update value - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

update value

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-12-12 : 16:28:07

I have a data like below.. I want to get rid of Y_N value except the last lab value of the privious month where lab vlaue is > 39 in the same ID.
For example, Id 111 has 40 as the last lab value of the privous month , so i want to keep Y_N value of that row and get rid of rest of Y_N values in ID 111,
Id 112 has 24 as the last lab value of the privous month ,so I want to get rid of all Y_N vlaues in ID 112.


ID                     TEST_DATE                 LAB                    EPO                    TX                     Y_N 
---------------------- ------------------------- ---------------------- ---------------------- ---------------------- --- 
111                    01-OCT-06                                        1100                                              
111                    03-OCT-06                                        1120                   1                          
111                    04-OCT-06                                        1500                   1                          
111                    15-OCT-06                 28                     1500                   1                          
111                    20-OCT-06                                        1400                   1                          
111                    27-OCT-06                 45                     1500                   1                      Y   
111                    28-OCT-06                                        1600                   1                          
111                    29-OCT-06                 40                     1700                   1                      Y   
111                    30-OCT-06                                        1100                                              
111                    01-NOV-06                                        1200                   1                          
111                    03-NOV-06                                        1100                   1                          
111                    06-NOV-06                 25                     1300                   1                          
111                    07-NOV-06                                        1700                   1                          
111                    08-NOV-06                 39.5                   1200                   1                      Y   
111                    09-NOV-06                                                               1                          
111                    10-NOV-06                                        1800                                              
112                    02-OCT-06                                                               1                          
112                    05-OCT-06                 40                                                                   N   
112                    06-OCT-06                                        1700                   1                          
112                    11-OCT-06                                                               1                          
112                    14-OCT-06                                        2000                                              
112                    24-OCT-06                 24                                            1                          
112                    29-OCT-06                                        2200                                              
112                    04-NOV-06                                                                                          
112                    06-NOV-06                 42                     3000                   1                      Y   
112                    08-NOV-06                                        2400                                              
112                    11-NOV-06                                        2400                   1                          
112                    20-NOV-06                                        2300                                              
112                    21-NOV-06                                        1000                   1                          
112                    22-NOV-06                 35                     1200                                              
112                    24-NOV-06                                        1500                   1                          
112                    26-NOV-06                 40                     1700                                          N   
112                    27-NOV-06                                        1100                   1                          
112                    28-NOV-06                                        1300                                              
112                    29-NOV-06                                        1400                   1                          
112                    30-NOV-06                                        1100                   1

The desired output should look like.


ID                     TEST_DATE                 LAB                    EPO                    TX                     Y_N 
---------------------- ------------------------- ---------------------- ---------------------- ---------------------- --- 
111                    01-OCT-06                                        1100                                              
111                    03-OCT-06                                        1120                   1                          
111                    04-OCT-06                                        1500                   1                          
111                    15-OCT-06                 28                     1500                   1                          
111                    20-OCT-06                                        1400                   1                          
111                    27-OCT-06                 45                     1500                   1                     
111                    28-OCT-06                                        1600                   1                          
111                    29-OCT-06                 40                     1700                   1                      Y   
111                    30-OCT-06                                        1100                                              
111                    01-NOV-06                                        1200                   1                          
111                    03-NOV-06                                        1100                   1                          
111                    06-NOV-06                 25                     1300                   1                          
111                    07-NOV-06                                        1700                   1                          
111                    08-NOV-06                 39.5                   1200                   1                         
111                    09-NOV-06                                                               1                          
111                    10-NOV-06                                        1800                                              
112                    02-OCT-06                                                               1                          
112                    05-OCT-06                 40                                                                   
112                    06-OCT-06                                        1700                   1                          
112                    11-OCT-06                                                               1                          
112                    14-OCT-06                                        2000                                              
112                    24-OCT-06                 24                                            1                          
112                    29-OCT-06                                        2200                                              
112                    04-NOV-06                                                                                          
112                    06-NOV-06                 42                     3000                   1                        
112                    08-NOV-06                                        2400                                              
112                    11-NOV-06                                        2400                   1                          
112                    20-NOV-06                                        2300                                              
112                    21-NOV-06                                        1000                   1                          
112                    22-NOV-06                 35                     1200                                              
112                    24-NOV-06                                        1500                   1                          
112                    26-NOV-06                 40                     1700                                          
112                    27-NOV-06                                        1100                   1                          
112                    28-NOV-06                                        1300                                              
112                    29-NOV-06                                        1400                   1                          
112                    30-NOV-06                                        1100                   1

How can I do this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-12 : 17:00:56

something like

update x
set x.[y_n] = case when q.id is null then '' else 'y' end
from <yourtablenamehere> as x
left join (select id, max(test_date) as mdt from <yourtablenamehere> where y_n = 'y' group by id) q 
on q.id = x.id and q.mdt = x.test_date

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources