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 |
|
SQLGeno
Starting Member
13 Posts |
 Posted - 2011-01-26 : 18:40:58
|
| Hi guys\girls,Any help with this appreciated. Consider the follow setCaseId StatusId CreatedDate-------------- ----------- -----------------------13 1 2006-02-23 00:00:00.00024 1 2006-09-13 00:00:00.00024 2 2007-02-02 00:00:00.00024 1 2007-06-13 11:27:03.00024 1 2007-06-13 11:37:20.00024 3 2007-06-28 11:31:58.76324 3 2007-07-02 12:28:14.92024 1 2007-08-14 00:00:00.00024 3 2007-08-14 16:44:02.26324 3 2007-08-14 16:44:02.26324 3 2007-12-16 10:52:27.93736 1 2004-03-03 02:00:00.00036 2 2004-06-25 11:37:20.00036 2 2005-01-17 15:38:46.00036 2 2008-06-16 15:23:36.71736 2 2008-06-16 15:23:36.71736 3 2008-06-19 15:23:36.717501 1 2011-01-20 08:38:03.927501 1 2011-01-21 08:39:18.160501 1 2011-01-22 08:43:38.393501 3 2011-01-23 08:45:56.533501 1 2011-01-25 08:45:56.533For each CaseId, I need to look for a a given StatusId (in this case 3) and check to see if the status has subsequently been changed to a StatusId of 1. i.e. I need find all the CaseIds where the Status has been set to 3 at some stage and then changed 1. What is the best way of going about this?In the example shown CaseId 24 and 501 should be returned. |
|
|
singularity
Posting Yak Master
153 Posts |
Posted - 2011-01-26 : 19:01:50
|
| [code]select distinct CaseIDfrom yourtable awhere a.StatusID = 3 and exists (select * from yourtable b where StatusID = 1 and a.CaseID = b.CaseID and a.CreatedDate < b.CreatedDate)[/code] |
 |
|
|
SQLGeno
Starting Member
13 Posts |
Posted - 2011-01-26 : 23:10:33
|
| Thanks Yak Master Singularity. Exactly what I was after. |
|
|
|
|
|
|
Help with query t0 find subsequent changes