Setting Duplicate Records to Zero (0)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Setting Duplicate Records to Zero (0)

Author

Topic

ucal
Yak Posting Veteran

72 Posts

Posted - 2008-01-30 : 21:42:41

Hi Guys,
I have the a table with the folowing structure:

Seq ID1 Qty1 ID2 Qty2
1 1 107 1 11
2 2 121 1 11
3 3 130 2 16
4 3 130 3 12
5 5 102 4 22
6 4 123 5 34
7 4 123 6 13
8 4 123 7 18
9 6 100 8 19
I will like to set the values of Qty1 and Qty2 to zero (0)
except the first record for records with duplicate
values of ID1 and ID2

EXPECTED RESULS:
Seq ID1 Qty1 ID2 Qty2
1 1 107 1 11
2 2 121 1 0
3 3 130 2 16
4 3 0 3 12
5 5 102 4 22
6 4 123 5 34
7 4 0 6 13
8 4 0 7 18
9 6 100 8 19

Any help will be welcomed

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-30 : 21:56:28

[code]DECLARE @sample TABLE
(
Seq int,
ID1 int,
Qty1 int,
ID2 int,
Qty2 int
)
INSERT INTO @sample
SELECT 1, 1, 107, 1, 11 UNION ALL
SELECT 2, 2, 121, 1, 11 UNION ALL
SELECT 3, 3, 130, 2, 16 UNION ALL
SELECT 4, 3, 130, 3, 12 UNION ALL
SELECT 5, 5, 102, 4, 22 UNION ALL
SELECT 6, 4, 123, 5, 34 UNION ALL
SELECT 7, 4, 123, 6, 13 UNION ALL
SELECT 8, 4, 123, 7, 18 UNION ALL
SELECT 9, 6, 100, 8, 19

SELECT Seq, ID1, Qty1, ID2,
Qty2 = CASE WHEN row_no = 1 THEN Qty2 ELSE 0 END
FROM
(
SELECT Seq, ID1, Qty1, ID2, Qty2,
row_no = row_number() OVER (PARTITION BY ID2 ORDER BY ID2, ID1)
FROM @sample
) s
/*
Seq ID1 Qty1 ID2 Qty2
----------- ----------- ----------- ----------- -----------
1 1 107 1 11
2 2 121 1 0
3 3 130 2 16
4 3 130 3 12
5 5 102 4 22
6 4 123 5 34
7 4 123 6 13
8 4 123 7 18
9 6 100 8 19

(9 row(s) affected)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

ucal
Yak Posting Veteran

72 Posts

Posted - 2008-01-30 : 22:07:22

Thanks for your response,
Qty1 for ID1=3 and 4 should also be set to 0
exept the first case.
How wilImodify you script to address this ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-30 : 22:13:16

Same technique

DECLARE @sample TABLE
(
	Seq 	int,
	ID1  	int,
	Qty1  	int,
	ID2  	int,
	Qty2 	int
)
INSERT INTO @sample
SELECT	1, 1, 107, 1, 11 UNION ALL
SELECT	2, 2, 121, 1, 11 UNION ALL
SELECT	3, 3, 130, 2, 16 UNION ALL
SELECT	4, 3, 130, 3, 12 UNION ALL
SELECT	5, 5, 102, 4, 22 UNION ALL
SELECT	6, 4, 123, 5, 34 UNION ALL
SELECT	7, 4, 123, 6, 13 UNION ALL
SELECT	8, 4, 123, 7, 18 UNION ALL
SELECT	9, 6, 100, 8, 19

SELECT	Seq, 
	ID1, 
	Qty1 = CASE WHEN row_no1 = 1 THEN Qty1 ELSE 0 END, 
	ID2,
	Qty2 = CASE WHEN row_no2 = 1 THEN Qty2 ELSE 0 END
FROM
(
	SELECT	Seq, ID1, Qty1, ID2, Qty2,
		row_no1 = row_number() OVER (PARTITION BY ID1 ORDER BY ID1, ID2),
		row_no2 = row_number() OVER (PARTITION BY ID2 ORDER BY ID2, ID1)
	FROM	@sample
) s

/*
Seq         ID1         Qty1        ID2         Qty2        
----------- ----------- ----------- ----------- ----------- 
          1           1         107           1          11 
          2           2         121           1           0 
          3           3         130           2          16 
          4           3           0           3          12 
          5           5         102           4          22 
          6           4         123           5          34 
          7           4           0           6          13 
          8           4           0           7          18 
          9           6         100           8          19 

(9 row(s) affected)
*/

KH
[spoiler]Time is always against us[/spoiler]

ucal
Yak Posting Veteran

72 Posts

Posted - 2008-01-31 : 06:30:37

Sorry khtan,
What will be an altenate solution in
SQL Server 2000. Sorry for the inconvinience.
Thanks for your elegant solution.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-31 : 06:37:23

quote:
Originally posted by ucal

Sorry khtan,
What will be an altenate solution in
SQL Server 2000. Sorry for the inconvinience.
Thanks for your elegant solution.

Here's a sql 2000 compatible solution

SELECT	Seq, 
	ID1, 
	Qty1 = CASE WHEN row_no1 = 1 THEN Qty1 ELSE 0 END, 
	ID2,
	Qty2 = CASE WHEN row_no2 = 1 THEN Qty2 ELSE 0 END
FROM
(
	SELECT	Seq, ID1, Qty1, ID2, Qty2,
		(SELECT COUNT(*) + 1 FROM @sample WHERE ID1=t.ID1 AND Seq<t.Seq) AS row_no1,
		(SELECT COUNT(*) + 1 FROM @sample WHERE ID2=t.ID2 AND Seq<t.Seq) AS row_no2
	FROM	@sample t
) s

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources