SQL Stumper - the Status History table

Giganews Newsgroups
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
and 3.

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!

Replies