|Subject:||SQL Stumper - the Status History table|
|Posted by:||bvy (b…@bigfoot.com)|
|Date:||Sun, 15 Aug 2010|
I'm using SQL Server 2005. I have a Status History table (tblStatHist)
that looks more or less like this:
intCaseID dtmStatusDate intStatusID
1 6/28/2010 10
1 4/15/2010 3
1 4/2/2010 1
2 7/13/2010 5
2 2/1/2010 1
3 7/15/2010 10
3 5/14/2010 5
3 5/2/2010 3
3 5/1/2010 1
The Status ID's might be 1=Open, 3=In Progress, 5=On Hold, 10=Closed.
The date shows when a Case ID went into a given status. So Case ID 2
went into Open status on Feb 1 2010 and went into On Hold status on
July 13 2010.
I need a query that return Case ID's that were In Progress or On Hold
during a given month.
So for June (June 1 - June 30), Case ID 1 was In Progress most of June
and then closed on June 28. Case ID 3 was in On Hold status the entire
month of June. Case ID 2 would not be in my list since it was in Open
status the entire month of June. My query should return Case ID's 1
Can you help? I've tried some things with rank(), and I can results
via very convoluted means. But I'm convinced there must be a very
straightforward and elegant way to do this. Thank you!