December 18, 2009 at 6:58 am
I need to update a table I am going to export, of store records where the table has 4 fields which contain the most recent roberies dates in order (most recent in rdate1). The robbery data is in a detail table that has one record per store per robbery. So my export table has one record per store with fields storeno, rdate1, rdate2, rdate3, rdate4 and my detail table has storeno and robbery date. I want to accomplish this with 4 updates, one per date in my export table, without needing to fetch records in a loop.
December 18, 2009 at 7:27 am
Sounds simple but based on what you have given, not exactly sure what the query would look like. Please read the first article I reference below in my signature block. Please follow the instructions in the article in posting the table structures, sample data. Also, be sure to post the expected results should be based on the provided sample data.
Do that, and you will get much better responses to your questions, plus you'll get tested code in return.
December 18, 2009 at 7:51 am
Something like this ???
with cteRobberies
as(
Select StoreId,RobberyDate ,Row_number() over (partition by StoreID order by RobberyDate desc) as Rown
from YourTable
)
Select StoreId,min(case when Rown = 1 then RobberyDate else null end),
min(case when Rown = 2 then RobberyDate else null end),
min(case when Rown = 3 then RobberyDate else null end),
min(case when Rown = 4 then RobberyDate else null end)
from cteRobberies
group by storeId
December 18, 2009 at 8:48 am
Good job thanks Dave
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply