Cumulative total for tasks version table

Question

I have table with task fields versions. Every task field change create record in this table (TaskHistory).
TaskHistory fields:
[Id], [TaskId] ,[PriorityId] ,[Changed], [Version].
I need T-SQL request which get Cumulative total for tasks version table by 365 dates in year and group by Priority.
If we have several versions of task with [Changed]<[Selected Date], we need count to SUM only oldest version with same Priority.
For example for table TaskHistory:
1,123,3,'2021-04-01 12:39:53',1
2,123,2,'2021-04-01 13:39:53',2
3,123,1,'2021-04-01 14:39:53',3
4,124,3,'2021-04-01 15:39:53',1
5,124,2,'2021-04-01 16:39:53',2
6,124,1,'2021-04-01 17:39:53',3
7,125,3,'2021-04-02 12:39:53',1
8,125,2,'2021-04-02 13:39:53',2
9,125,1,'2021-04-02 14:39:53',3
10,126,3,'2021-04-02 15:39:53',1
11,126,2,'2021-04-02 16:39:53',2
12,126,1,'2021-04-02 17:39:53',3
Result:
Date, Priority, Cumulative tasks total
'2021-04-01',1, 2
'2021-04-01',2, 0
'2021-04-01',3, 0
'2021-04-02',1, 4
'2021-04-02',2, 0
'2021-04-02',3, 0

Answer 1

Hi @Vasily Zaytsev ,

Welcome to Microsoft Q&A!

Please refer below:

drop table if exists TaskHistory  
  
create table TaskHistory  
(  
[Id] int identity(1,1),  
 [TaskId] int ,  
 [PriorityId] int ,  
 [Changed] datetime,  
  [Version] int  
)  
  
insert into TaskHistory ([TaskId],[PriorityId],[Changed],[Version]) values  
(123,3,'2021-04-01 12:39:53',1),  
(123,2,'2021-04-01 13:39:53',2),  
(123,1,'2021-04-01 14:39:53',3),  
(124,3,'2021-04-01 15:39:53',1),  
(124,2,'2021-04-01 16:39:53',2),  
(124,1,'2021-04-01 17:39:53',3),  
(125,3,'2021-04-02 12:39:53',1),  
(125,2,'2021-04-02 13:39:53',2),  
(125,1,'2021-04-02 14:39:53',3),  
(126,3,'2021-04-02 15:39:53',1),  
(126,2,'2021-04-02 16:39:53',2),  
(126,1,'2021-04-02 17:39:53',3)  
  
;with cte as(  
select cast(Changed as date) Changed ,PriorityId,count(TaskId) count  
from TaskHistory  
group by cast(Changed as date),PriorityId)  
,cte1 as (select *,ROW_NUMBER() over (partition by changed order by changed,PriorityId) rn  
from cte)  
,cte2 as (  
select Changed,PriorityId,IIF(rn=1,count,0) total   
from cte1)  
select Changed,PriorityId,sum(total) over (partition by PriorityId order by changed ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) [Cumulative tasks total]  
from cte2  
order by Changed,PriorityId  

Output:

Changed	PriorityId	Cumulative tasks total  
2021-04-01	1	2  
2021-04-01	2	0  
2021-04-01	3	0  
2021-04-02	1	4  
2021-04-02	2	0  
2021-04-02	3	0  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.