sql server getting data

Question

0.00/5 (No votes)

See more:

Hi all
I have result set like this

productid	proudctname	enterdate	nameoftheday	status
1		colgate		2013/05/01       wednesday	 0
2		santoor		2013/05/01       wednesday	 0
3		rin		2013/05/01       wednesday	 0
4		lux		2013/05/02       Thursday	 0
4		mouse		2013/05/03       friday		 0
null		null	        2013/05/04       satruday	 0
null		null  	        2013/05/05       sunday	         0
5		cellphone	2013/05/06	 monday	 	 0
6		bottle 		2013/05/06	 monday	 	 0
7		books		2013/05/06	 monday	 	 0
null		null		2013/05/07	 tuesday	 1
8		chair		2013/05/08	 wednuesday      0
null		null    	2013/05/09	 Thursday	 1

now i want to update above result set
that is
if status =1
i want to update previous date values
in product id and productname
To get clarification i will give sample output

productid	proudctname	enterdate	nameoftheday	status
1		colgate		2013/05/01       wednesday	 0
2		santoor		2013/05/01       wednesday	 0
3		rin		2013/05/01       wednesday	 0
4		lux		2013/05/02       Thursday	 0
4		mouse		2013/05/03       friday		 0
null		null	        2013/05/04       satruday	 0
null		null  	        2013/05/05       sunday	         0
5		cellphone	2013/05/06	 monday	 	 0
6		bottle 		2013/05/06	 monday	 	 0
7		books		2013/05/06	 monday	 	 0
5or6or7	  cellphone/bottle/books 2013/05/07	 tuesday	 1
8		chair		2013/05/08	 wednuesday      0
8		chair	    	2013/05/09	 Thursday	 1

Could anyone help me out

Regards,
Prakash.ch

Posted 5-Jun-13 21:10pm

prakash.chakrala

Updated 5-Jun-13 21:23pm

_Amy

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-06-05T21:52:00

Solution 2

Have a look here: sql server filtering data[^]. Exactly the same question and my anser!

Posted 5-Jun-13 21:52pm

Maciej Los

Comments

prakash.chakrala 6-Jun-13 3:54am

In that solution wherever nulls are available its replacing

Maciej Los 6-Jun-13 4:25am

Yes, NULL's in productid column are replaced with values based on data from previous date ;)
It's not possible to store 5or6or7 in the "productid" column, because its stores integer values.

prakash.chakrala 6-Jun-13 7:33am

I need to replace if status=1 only
and 5 or 6 or 7 means not in one column
either 5 or 6 or 7

Maciej Los 6-Jun-13 7:41am

So... What do you need more? Have you seen my past solution? Have you tried to change it to your needs? What is not understandable?

prakash.chakrala 6-Jun-13 8:05am

tried but.......

Maciej Los 6-Jun-13 8:10am

Try harder...
;)

prakash.chakrala 6-Jun-13 8:13am

could u give any hint

Maciej Los 6-Jun-13 8:19am

What kind of hint?
Have a look at my example (in past answer) and start analyze it step by step, comment by comment...
In first part i declare and fulfil temporary table to work on data. Next, you have comments:
--insert products with status=1 and enterdate=previous date
--delete nulls!!!
Isn't enough clear?

As i wrote before, you can't add '5 or 6 or 7' into productid column, because it stores integer values. '5 or 6 or 7' is not integer value! It's a string!