May 14, 2009 at 4:52 pm
Hi all,
I'm just returning to work after being on maternity leave for a year, so the SQL part of my brain has rusted. 🙂
We have a table that has, amongst other things, a Status field that has six possible values. Every time a row in the table is changed, a copy of the row is placed into a table I'll call ChangeLog.
Now, what I need to do is find all records where the status became "MR" or stopped being "MR". (Not all records will ever be in MR status.)
My first instinct was to use a cursor, and then I remembered that people generally consider cursors to be evil... so I'm not sure of another way to do it. Can someone point me in the right direction?
Sample data in the table might be something like:
ID,Status,ChangeLogDate
1, Act, May 5
1, Init, May 7
1, MR, May 14
1, Out, May 15
2, Act, May 1
2, Out, May 7
So ID #1 moved into and then back out of MR status. ID #2 never did, so I don't care about it at all.
I'd like output to be something along the lines of:
ID #1 changed from Init to MR status on May 14
ID #1 changed from MR to Out status on May 15 after 1 day
I also don't care that #1 was originally in Act status, only Init-->MR and MR-->Out.
Like I said, I can immediately think of how to do it using a cursor, but I'm sure there's got to be a better way. I haven't even written a line of code yet, just thinking about it!
May 14, 2009 at 5:26 pm
the secret is to join the table against a copy of itself.
this example is most of the way there...the only problem is that it returns more than one row.
that can be fixed easily by using row_number() function and an outer select over the results to get the max() row number;
if you KNOW the status always goes Init to MR and MR to Out, then you can hange the code 'MR' to the right value, and you would not have to use row_number() at all.
see if this helps get you started
Create Table #MyAudit(ID int,Status varchar(20),ChangeLogDate datetime)
insert into #MyAudit
SELECT 1, 'Act', 'May 5 2009' UNION ALL
SELECT 1, 'Init', 'May 7 2009' UNION ALL
SELECT 1, 'MR', 'May 14 2009' UNION ALL
SELECT 1, 'Out', 'May 15 2009' UNION ALL
SELECT 2, 'Act', 'May 1 2009' UNION ALL
SELECT 2, 'Out', 'May 7 2009'
--status was anyything, later changed to MR
SELECT * FROM #MyAudit PRE_MR
LEFT OUTER JOIN #MyAudit CHANGED_TO_MR ON PRE_MR.ID = CHANGED_TO_MR.ID
WHERE PRE_MR.ChangeLogDate < CHANGED_TO_MR.ChangeLogDate
AND PRE_MR.[Status] 'MR' --or ='Init'?
AND CHANGED_TO_MR.[Status]='MR'
--status was MR, changed to anything
SELECT * FROM #MyAudit CHANGED_TO_MR
LEFT OUTER JOIN #MyAudit PRE_MR ON CHANGED_TO_MR.ID = PRE_MR.ID
WHERE PRE_MR.ChangeLogDate > CHANGED_TO_MR.ChangeLogDate
AND PRE_MR.[Status] 'MR' --or ='Out'?
AND CHANGED_TO_MR.[Status]='MR'
--results
ID Status ChangeLogDate ID Status ChangeLogDate
----------- -------------------- ----------------------- ----------- -------------------- -----------------------
1 Act 2009-05-05 00:00:00.000 1 MR 2009-05-14 00:00:00.000
1 Init 2009-05-07 00:00:00.000 1 MR 2009-05-14 00:00:00.000
(2 row(s) affected)
ID Status ChangeLogDate ID Status ChangeLogDate
----------- -------------------- ----------------------- ----------- -------------------- -----------------------
1 MR 2009-05-14 00:00:00.000 1 Out 2009-05-15 00:00:00.000
(1 row(s) affected)
Lowell
May 14, 2009 at 7:03 pm
Wow, thanks that looks pretty easy! I'll try this first thing tomorrow!
(And no, it could be any status before and after MR status so your generic method is great)
Thank you very much!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply