I have a table with Case records with CaseId, UserId, Opened date, Closed date etc value.
For reporting I need to have a result table with all users by continuous months with number of case still opened with amount and number of closed case with amount.
CaseID | UserID | Opened On | Closed Date| Value
1 U1 04/01/2020 05/02/2020 500
2 U1 08/01/2020 null 120
3 U3 16/01/2020 null 350
4 U2 21/01/2020 25/01/2020 100
5 U1 25/01/2020 08/04/2020 150
6 U2 05/02/2020 null 790
7 U4 14/02/2020 18/03/2020 190
8 U4 19/02/2020 null 490
9 U1 21/02/2020 27/02/2020 140
10 U2 07/03/2020 17/03/2020 640
11 U1 17/03/2020 12/05/2020 420
12 U3 22/03/2020 null 810
13 U2 05/04/2020 19/05/2020 320
14 U1 18/04/2020 null 180
15 U4 16/05/2020 22/05/2020 210
16 U3 19/05/2020 null 230
Output desired (*values in parenthesis are only for helping)
UserID | Month | Nb case still opened | Total Open | Nb closed case | Total Value closed U1 01 3 (1 ;2 ;5)* 770 0 0 U1 02 2 (2 ;5) 270 2 (1;9) 640 U1 03 3 (2;5;11) 690 0 0 U1 04 3 (2;11;14) 720 1 (5) 150 U1 05 2 (2 ;14) 300 1 (11) 420 U2 01 0 0 1 100 U2 02 1 (6) 790 0 0 U2 03 1 (6) 790 1 640 U2 04 2 (6 ;13) 1100 0 0 U2 05 1 (6) 790 1 (13) 320 U3 01 1 (3) 350 0 0 U3 02 1 (3) 350 0 0 U3 03 2 (6 ;12) 1160 0 0 U3 04 2 (6 ;12) 1160 0 0 U3 05 3 (6 ;12 ;16) 1390 0 0 U4 01 0 0 0 0 U4 02 1 (7 ;8) 680 0 0 U4 03 2 (8) 490 1 190 U4 04 1 (8) 490 0 0 U4 05 1 (8) 490 1 210
I tried to play with CROSS JOIN, OVER (PARTITION) but I didn’t find the solution. Thanks for you help.
Advertisement
Answer
I would first cross join the available year/month and users available to generate the rows. Then, union all in a subquery can be used to unpivot the opening and closing information (one could possibly use cross apply too).
The final step is aggregation and window computation:
select
u.userID,
d.dt year_month,
sum(coalesce(sum(no_open - no_closed), 0))
over(partition by u.userID order by d.dt) no_cases_still_open,
sum(coalesce(sum(value_open - value_closed), 0))
over(partition by u.userID order by d.dt) value_open,
coalesce(sum(no_closed), 0) no_closed,
coalesce(sum(value_closed), 0) value_closed
from
(select distinct datefromparts(year(openedOn), month(openedOn), 1) dt from mytable) d
cross join (select distinct userID from mytable) u
left join (
select userID, openedOn dt, 1 no_open, value value_open, 0 no_closed, 0 value_closed from mytable
union all
select userID, closedDate dt, 0, 0, 1, value from mytable
) t
on t.userID = u.userID
and t.dt >= d.dt and t.dt < dateadd(month, 1, d.dt)
group by u.userID, d.dt
userID | year_month | no_cases_still_open | value_open | no_closed | value_closed :----- | :--------- | ------------------: | ---------: | --------: | -----------: U1 | 2020-01-01 | 3 | 770 | 0 | 0 U1 | 2020-02-01 | 2 | 270 | 2 | 640 U1 | 2020-03-01 | 3 | 690 | 0 | 0 U1 | 2020-04-01 | 3 | 720 | 1 | 150 U1 | 2020-05-01 | 2 | 300 | 1 | 420 U2 | 2020-01-01 | 0 | 0 | 1 | 100 U2 | 2020-02-01 | 1 | 790 | 0 | 0 U2 | 2020-03-01 | 1 | 790 | 1 | 640 U2 | 2020-04-01 | 2 | 1110 | 0 | 0 U2 | 2020-05-01 | 1 | 790 | 1 | 320 U3 | 2020-01-01 | 1 | 350 | 0 | 0 U3 | 2020-02-01 | 1 | 350 | 0 | 0 U3 | 2020-03-01 | 2 | 1160 | 0 | 0 U3 | 2020-04-01 | 2 | 1160 | 0 | 0 U3 | 2020-05-01 | 3 | 1390 | 0 | 0 U4 | 2020-01-01 | 0 | 0 | 0 | 0 U4 | 2020-02-01 | 2 | 680 | 0 | 0 U4 | 2020-03-01 | 1 | 490 | 1 | 190 U4 | 2020-04-01 | 2 | 700 | 0 | 0 U4 | 2020-05-01 | 1 | 490 | 1 | 210