Sql Server : How Many Days Each Item Was In Each State

Given a table that stores every revision for every item. For example: +--------+----------+---------------+--------+---------------------+ | ItemId | Revision | PreviousState | St

Solution 1:

This gives you the same results you're asking for, in a slightly different format (but you can easily find PIVOT solutions if you need the exact same result set):

declare@ttable (ItemId int,Revision int,State varchar(19),DateChanged datetime2)
insertinto@t(ItemId,Revision,State,DateChanged) values
(1,1,'New',   '2014-11-13T10:00:00'),
(1,2,'Active','2014-11-15T10:00:00'),
(1,3,'New',   '2014-11-17T10:00:00'),
(1,4,'Active','2014-11-19T10:00:00'),
(1,5,'Active','2014-11-20T10:00:00'),
(1,6,'Closed','2014-11-22T10:00:00'),
(2,1,'New',   '2014-11-13T10:00:00'),
(2,2,'Active','2014-11-16T10:00:00'),
(2,3,'Closed','2014-11-17T10:00:00'),
(2,4,'Active','2014-11-19T10:00:00'),
(2,5,'Closed','2014-11-21T10:00:00')

;With Joined as (
    select t1.ItemId,t1.State,DATEDIFF(day,t1.DateChanged,t2.DateChanged) as Days
    from@t t1
            innerjoin@t t2
            on
                t1.ItemId = t2.ItemId and
                t1.Revision = t2.Revision -1
    )
select ItemId,State,SUM(Days)
from Joined
where State <>'Closed'groupby ItemId,State

Result:

ItemId      State               
----------- ------------------- -----------1           Active              51New42           Active              32New3

Note that I'm ignoring the PreviousState column from your question and am instead constructing Joined because what really matters is when the next state came into effect.

---

Issues not dealt with because you've not described them in your question: 1) What to do if the current final state isn't Closed - i.e. do we ignore that, or count until today?, and 2) What to do if the time of day for each DateChanged isn't the same - do we have to handle partial days?

Solution 2:

Personally I like the CTE from [Damien_The_Unbeliever], I need to use them more often. Using inner joins I basically do the same thing the add a pivot wrapper around the results to get what you are looking for: (replace @t for your real table name)

SELECTItemId , [New],[Active]FROM
(
SELECT 
    ItemId , LASTSTATE,  DATEDIFF(D, LASTDATE, DateChanged) AS D 
FROM 
    @T AS T 
    INNER JOIN 
        (SELECT 
            ItemId as ItemLink, 
            Revision + 1 AS RevLink , 
            DateChanged AS LASTDATE , 
            State AS LASTSTATE from @t
        ) AS L ON T.ItemId = L.ItemLink AND T.Revision = L.RevLink
) ASPPIVOT ( SUM(D) FOR LASTSTATE IN ([New],[Active],[Closed])) ASDATA