Hello,
I have below problem statement -
A table contains incremental data as below -
Name | Date | Time(in min)
A | 2021-05-26 20:39:15.3366667 | 2
B | 2021-05-26 17:32:15.3366667 | 1
C | 2021-05-26 21:37:15.3366667 | 4
D | 2021-05-26 15:38:11.3366667 | 10
A | 2021-05-26 20:29:15.3366667 | 12
B | 2021-05-26 17:35:15.3366667 | 22
C | 2021-05-26 21:17:15.3366667 | 12
D | 2021-05-26 15:31:11.3366667 | 19
A | 2021-05-25 20:36:15.3366667 | 4
B | 2021-05-25 16:34:15.3366667 | 6
C | 2021-05-25 23:32:15.3366667 | 5
D | 2021-05-25 22:59:15.3366667 | 11
.
.
.
and so on.
I want this data in below format -
Name | 2021-05-26 | 2021-05-25 | 2021-05-24 | 2021-05-23 | 2021-05-22
A | 14 | 4 | ....
B | 23 | 6 | ....
C | 16 | 5 | ....
D | 29 | 11 | ....
The column names in the desired table will keep on changing and need to be that of rolling 5 days. In the value field, I need the sum of time(in min) for the given combination of date and Name. For eg. - For A and for 2021-05-26, it is 2 + 12 = 14...and so on.
We can use pivot here, but I am facing difficulty in the same. Also, I do not have permission to create any table. So options with CTE would be preferred.
Thank you.
Dipesh