Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a table that holds status changes per item. The item can go in and out of a status multiple times.

ItemTable (ItemId int, Status varchar(50), DateOfChange date)

I need to pull the date of each change. However, since the statuses can be repeated for each Item, I can't just do the min(DateOfChange) and find the occurrences.

This is in an OLAP environment, and the subquery that's been used to pull this data is KILLING performance.

Is there a way to pull this stuff via partitioning/rownumber functions? Or something else that would behave better in OLAP? (This is in SQL 2008.)

share|improve this question
To clarify, you need to pull the first date of each status for an item? – Kevin Jan 11 '14 at 14:01
Sorry, power knocked out the office for about 10 hours. :P No, I need each date for each status change. So if it goes into status A 5 times, I need 5 dates. Be nice if it were just the first instance of A, but no such luck. – Valkyrie Jan 12 '14 at 1:21
I had an answer lined up but Martin's is fleshed out a little better if that's what you were looking for. What doesn't quite make sense to me is you have a date per status per item in your table, what are you looking for? – Kevin Jan 12 '14 at 1:28
'Cause it's not just those columns; it's a metric ton of data per day, but this one column is all I care about for this crappy subquery. Martin saved my bacon! – Valkyrie Jan 12 '14 at 2:28
up vote 9 down vote accepted

This type of requirement comes under the banner of "gaps and islands". A popular approach is

     AS (SELECT *,
                DENSE_RANK() OVER (PARTITION BY ItemId ORDER BY DateOfChange) - 
                DENSE_RANK() OVER (PARTITION BY ItemId, Status ORDER BY DateOfChange) AS Grp
         FROM   ItemTable)
       MIN(DateOfChange) AS Start,
       MAX(DateOfChange) AS Finish
ORDER  BY Start 
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.