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 2008 Forums
 Transact-SQL (2008)
 How to turn the rest record for zero based on ID

Author  Topic 

micnie_2020
Posting Yak Master

232 Posts

Posted - 2012-11-29 : 18:47:03
Hi All,

I have a set of data eg:-

Before:

ID Desc Datt GAMt1 GAMt2 ...
1 xxx 01/12 1000 1000
1 xxx 02/12 1000 1000
2 bb 01/12 500 500
2 bb 02/12 500 500
2 bb 03/12 500 500


After( I need this output, mean 1 ID only have 1 GAmt1 & GMAt2):-

ID Desc Datt GAMt1 GAMt2 .....
1 xxx 01/12 1000 1000
1 xxx 02/12 0 0
2 bb 01/12 500 500
2 bb 02/12 0 0
2 bb 03/12 0 0

Please advise.

Thank you.

Regards,
Micheale



Elizabeth B. Darcy
Starting Member

39 Posts

Posted - 2012-11-29 : 19:29:52
This is one way. There are perhaps other ways that utilize the MIN function:
;WITH cte AS
(
SELECT GAMt1, GAMt2,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Datt) AS RN
FROM
YourTable
)
UPDATE cte SET
GAMt1 = 0,
GAMt2 = 0
WHERE
RN > 1;


________________________________________
-- Yes, I am indeed a fictional character.
Go to Top of Page
   

- Advertisement -