sql server filtering data

Question

0.00/5 (No votes)

See more:

productId    productname    enterdate     status
1                lux          1/1/2013      0
2               santoor       2/1/2013      0
3              colgate        2/1/2013      0
4                mouse        2/1/2013      0
null             null         3/1/2013      1
6               keyboard      6/1/2013      0
null             null         7/1/2013      1
8               speakers      8/1/2013      0
9                pen          9/1/2013      0
10               cellphone    10/1/2013     0

I have the output like this now i want update the
productid and productname when status =1

when null available in productid i have to get the
value before day enterdate value
same as productname also

The desired output is

productId    productname    enterdate     status
1                lux          1/1/2013      0
2               santoor       2/1/2013      0
3              colgate        2/1/2013      0
4                mouse        2/1/2013      0
4      santoor or colgate or mouse   3/1/2013      1
6               keyboard      6/1/2013      0
6           keyboard        7/1/2013      1
8               speakers      8/1/2013      0
9                pen          9/1/2013      0
10               cellphone    10/1/2013     0

Posted 4-Jun-13 7:52am

prakash.chakrala

Updated 4-Jun-13 7:56am

Maciej Los

v2

Add a Solution

Comments

Zoltán Zörgő 4-Jun-13 13:55pm

Horrible design.

Maciej Los 4-Jun-13 13:57pm

Agree!

damodara naidu betha 12-Jun-13 2:44am

What if the previous row also contains null value in productid column?

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-04T08:11:00

I would suggest you to redesign your table!
Use ALTER TABLE[^] command to update productid to not accept null values.

Here is one-usage solution:

SQL

DECLARE @tmp TABLE (productId INT NULL, productname VARCHAR(30) NULL, enterdate DATETIME, [status] INT)

SET DATEFORMAT dmy;

INSERT INTO @tmp (productId, productname, enterdate, [status])
VALUES(1, 'lux', '1/1/2013', 0)
INSERT INTO @tmp (productId, productname, enterdate, [status])
VALUES(2, 'santoor', '2/1/2013', 0)
INSERT INTO @tmp (productId, productname, enterdate, [status])
VALUES(3, 'colgate', '2/1/2013', 0)
INSERT INTO @tmp (productId, productname, enterdate, [status])
VALUES(4, 'mouse', '2/1/2013', 0)
INSERT INTO @tmp (productId, productname, enterdate, [status])
VALUES(null, null, '3/1/2013', 1)
INSERT INTO @tmp (productId, productname, enterdate, [status])
VALUES(6, 'keyboard', '6/1/2013', 0)
INSERT INTO @tmp (productId, productname, enterdate, [status])
VALUES(null, null, '7/1/2013', 1)
INSERT INTO @tmp (productId, productname, enterdate, [status])
VALUES(8, 'speakers', '8/1/2013', 0)
INSERT INTO @tmp (productId, productname, enterdate, [status])
VALUES(9, 'pen', '9/1/2013', 0)
INSERT INTO @tmp (productId, productname, enterdate, [status])
VALUES(10, 'cellphone', '10/1/2013', 0)

--insert products with <code>status=1</code> and <code>enterdate=previous date</code> 
INSERT INTO @tmp (productId, productname, enterdate, [status])
SELECT t1.productid, t1.productname, t2.enterdate, t2.status
FROM @tmp AS t1 INNER JOIN (
		SELECT enterdate, [status]
		FROM @tmp
		WHERE productId IS NULL AND productname IS NULL
	) AS t2 ON t1.enterdate = DATEADD(dd,-1,t2.enterdate)
WHERE t1.productId IS NOT NULL AND t1.productname IS NOT NULL

--delete nulls!!!
DELETE 
FROM @tmp
WHERE productId IS NULL AND productname IS NULL

--display "updated" values
SELECT *
FROM @tmp
ORDER BY enterdate, productid

Result:

1   lux          2013-01-01 00:00:00.000     0
2   santoor      2013-01-02 00:00:00.000     0
3   colgate      2013-01-02 00:00:00.000     0
4   mouse        2013-01-02 00:00:00.000     0
2   santoor      2013-01-03 00:00:00.000     1
3   colgate      2013-01-03 00:00:00.000     1
4   mouse        2013-01-03 00:00:00.000     1
6   keyboard     2013-01-06 00:00:00.000     0
6   keyboard     2013-01-07 00:00:00.000     1
8   speakers     2013-01-08 00:00:00.000     0
9   pen          2013-01-09 00:00:00.000     0
10  cellphone    2013-01-10 00:00:00.000     0