CODEWITHSUNDEEP
sqlsql-server-2012
bpfalcon
bpfalcon

Reputation: 11

How many Days each item was in each State, the full value of the period

This post is really similar to my question: SQL Server : how many days each item was in each state

but I dont have the column Revision to see wich is the previous state, and also I want to get the full time of a status, I b

....

I'm want to get how long one item has been in one status in general, my table look like this:

    ID                                      DATE                     STATUS
    3D56B7B1-FCB3-4897-BAEB-004796E0DC8D    2016-04-05 11:30:00.000  1
    3D56B7B1-FCB3-4897-BAEB-004796E0DC8D    2016-04-08 11:30:00.000  13
    274C5DA9-9C38-4A54-A697-009933BB7B7F    2016-04-29 08:00:00.000  5
    274C5DA9-9C38-4A54-A697-009933BB7B7F    2016-05-04 08:00:00.000  4
    A70A66DC-9D9E-49BE-93CF-00F9E3E06CE2    2016-04-14 07:50:00.000  1
    A70A66DC-9D9E-49BE-93CF-00F9E3E06CE2    2016-04-21 14:00:00.000  2
    A70A66DC-9D9E-49BE-93CF-00F9E3E06CE2    2016-04-23 12:15:00.000  3
    A70A66DC-9D9E-49BE-93CF-00F9E3E06CE2    2016-04-23 16:15:00.000  1
    BF122AE1-CB39-4967-8F37-012DC55E92A7    2016-04-05 10:30:00.000  1
    BF122AE1-CB39-4967-8F37-012DC55E92A7    2016-04-20 17:00:00.000  5

I want to get this

Column 1 : ID Column 2 : Status Column 3 : Time with the status

Column 3 : Time with the status = NextDate - PreviosDate + 1

I should look like this:

ID                                      STATUS      TIME
3D56B7B1-FCB3-4897-BAEB-004796E0DC8D    1           3
3D56B7B1-FCB3-4897-BAEB-004796E0DC8D    13          1
274C5DA9-9C38-4A54-A697-009933BB7B7F    5           5
274C5DA9-9C38-4A54-A697-009933BB7B7F    4           1
A70A66DC-9D9E-49BE-93CF-00F9E3E06CE2    1           8
A70A66DC-9D9E-49BE-93CF-00F9E3E06CE2    2           2   
BF122AE1-CB39-4967-8F37-012DC55E92A7    1           15
BF122AE1-CB39-4967-8F37-012DC55E92A     5           1

Upvotes: 0

Views: 31

Answers (1)

bpfalcon
bpfalcon

Reputation: 11

Thanks to @ConradFrix comments, this is how works ..

WITH CTE
    AS
    (
        SELECT
        ID,
        STATUS, 
        DATE, 
        LEAD(DATE, 1) over (partition by ID order by DATE) LEAD,
        ISNULL(DATEDIFF(DAYOFYEAR,  DATE,
        LEAD(DATE, 1) over (partition by ID order by DATE)), 1)  DIF_BY_LEAD
        FROM TABLE_NAME
    )
    SELECT ID, STATUS, SUM(DIF_BY_LEAD) AS TIME_STATUS 
    FROM CTE GROUP BY ID, STATUS
    ORDER BY ID, STATUS

Upvotes: 1

Related Questions