How can I Create Multiple DATEDIFF Columns?

I would like to calculate the DATEDIFF between each of the different ranges represented by the NewValue column below.

    Id  CreatedDate         NewValue    Count    
0   ABC 2018-11-28 09:16:15 Created     1
1   ABC 2019-01-17 14:09:02 Approved    2
2   ABC 2019-03-01 13:41:16 Req Def     3
3   ABC 2019-03-26 10:31:00 Dev&Config  4
4   ABC 2019-03-26 10:31:19 Testing     5
5   ABC 2019-04-26 10:03:09 Complete    6
6   EAI 2018-11-28 16:08:55 Created     1
7   EAI 2018-12-03 10:06:42 Approved    2
8   EAI 2019-01-18 17:15:29 Req Def     3
9   EAI 2019-03-21 23:48:08 Testing     4
10  EAI 2019-05-06 16:50:03 Complete    5
11  BAC 2018-11-30 12:11:26 Created     1
12  BAC 2018-12-03 14:22:53 Approved    2
13  BAC 2018-12-19 14:00:03 Req Def     3
14  BAC 2019-09-18 11:52:16 Complete    4

I would like the output to be something like this:

    Id  Created_Approved  Approved_ReqDef  ReqDef_DevConfig  DevConfig_Testing  Testing_Complete 
0   ABC 1234              1234             1234              1234               1234
1   EAI 1234              1234             NULL              NULL               1234
2   BAC 1234              1234             NULL              NULL               NULL

I’ve tried the following code to create one of these columns, but I get all 0s. How can I fix this code and make it work for any number of columns? I’m thinking CASE WHEN, but not sure how to integrate. Thank you!!!

Select #Temp.Id, DATEDIFF(MINUTE, MAX(#Temp.CreatedDate), MIN(#Temp.CreatedDate)) as Created_Approved
from #Temp
where #Temp.NewValue in ('Created','Approved')
group by #Temp.Id```

Answer

Use conditional aggregation:

Select t.Id
       datediff(minute,
                max(case when t.NewValue = 'Created' then CreatedDate end),
                max(case when t.NewValue = 'Approved' then t.CreatedDate end)
             ) as Created_Approved,
       datediff(minute,
                max(case when t.NewValue = 'Approved' then CreatedDate end),
                max(case when t.NewValue = 'Req Def then t.CreatedDate end)
             ) as approved_ReqDef,
        . . .
from #Temp t
group by t.Id ;

Advertisement

Answer