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