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.
| Author |
Topic |
|
amsqlguy
Yak Posting Veteran
89 Posts |
 Posted - 2009-06-12 : 09:44:44
|
| Guys,I have following data set for which I need to set flag based on the timestamp when the last document has been created.EMPLOYEEID DATETIMECREATED RESIDENCECITY CURRENTRESIDENCEFLG100 2009-06-12 CITYA Y100 2008-06-12 CITYB N100 2009-01-19 CITYC NIn the above example for the same employeeID 100 based on datetimecreated each night I should run a job to update the current flag, for employee ID 100the current residence flag based on date time created is updated to Y for CITYA. Is there any way to do this since I cannot use group by with max(id) function?thanks |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-12 : 09:52:27
|
UPDATE fSET CURRENTRESIDENCEFLG = CASE recID WHEN 1 THEN 'Y' ELSE 'N' ENDFROM (SELECT CURRENTRESIDENCEFLG,ROW_NUMBER() OVER (PARTITION BY EMPLOYEEID ORDER BY DATETIMECREATED DESC) AS recIDFROM Table1) AS f
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
|
|
|
Update flag