Take the 2-minute tour ×
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. It's 100% free, no registration required.

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 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 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 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 at 2:28

1 Answer 1

up vote 6 down vote accepted

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

WITH T
     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)
SELECT ItemId,
       Status,
       MIN(DateOfChange) AS Start,
       MAX(DateOfChange) AS Finish
FROM   T
GROUP  BY ItemId,
          Status,
          Grp
ORDER  BY Start 
share|improve this answer
    
Perfect! And now I am more edumacated. –  Valkyrie Jan 12 at 2:27

Your Answer

 
discard

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.