I have table with task fields versions. Every task field change ([ServiceId], [PriorityId], [StatusId], [ExecutorGroupId]) create record in this table (TaskHistory).
TaskHistory fields:
[Id], [TaskId] , [ServiceId], [PriorityId], [StatusId], [ExecutorGroupId], [Changed], [Version].
I need T-SQL request which get Cumulative total for tasks version table by 365*2 datetimes in year (9:00 AM, 6:00 PM) and group by task service, task priority, task status, all groups and by each group.
If we have several versions of task with [Changed]<=[Selected DateTime], we need count to SUM only newest task version with same service, priority, status, groups and group (last version to selected date).
Task service can change periodically 3->2->1 (2-1-3, 1-2-3, ... , any variants) 1=Service 1, 2=Service 2, 3=Service 3.
Task priority can change periodically 3->2->1 (2-1-3, 1-2-3, ... , any variants).
Task status can change periodically 1->2->3 (2-1-3, 1-2-3, ... , any variants) 1=Open, 2=In progress, 3=Closed.
Task executor group can change periodically 1->2->3 (2-1-3, 1-2-3, ... , any variants) 1=First line, 2=Second line, 3=Third line.
drop table if exists TaskHistory
create table TaskHistory
(
[Id] int identity(1,1),
[TaskId] int ,
[ServiceId] int,
[PriorityId] int,
[StatusId] int,
[ExecutorGroupId] int,
[Changed] datetime,
[Version] int
)
insert into TaskHistory ([TaskId],[ServiceId],[PriorityId],[StatusId],[ExecutorGroupId],[Changed],[Version]) values
(119,1,3,1,1,'2021-03-30 12:39:53',0),
(119,1,2,2,2,'2021-03-30 13:39:53',0),
(119,1,1,3,3,'2021-03-30 14:39:53',0),
(120,1,3,1,1,'2021-03-30 15:39:53',0),
(120,1,2,2,2,'2021-03-30 16:39:53',0),
(120,1,1,3,3,'2021-03-30 17:39:53',0),
(121,1,3,1,1,'2021-03-31 12:39:53',0),
(121,1,2,2,2,'2021-03-31 13:39:53',0),
(121,1,1,3,3,'2021-03-31 14:39:53',0),
(122,1,3,1,1,'2021-03-31 15:39:53',0),
(122,1,2,2,2,'2021-03-31 16:39:53',0),
(122,1,1,3,3,'2021-03-31 17:39:53',0),
(123,1,3,1,1,'2021-04-01 12:39:53',1),
(123,1,2,2,2,'2021-04-01 13:39:53',2),
(123,1,1,3,3,'2021-04-01 14:39:53',3),
(124,1,3,1,1,'2021-04-01 15:39:53',1),
(124,1,2,2,2,'2021-04-01 16:39:53',2),
(124,1,1,3,3,'2021-04-01 17:39:53',3),
(125,1,3,1,1,'2021-04-02 12:39:53',1),
(125,1,2,2,2,'2021-04-02 13:39:53',2),
(125,1,1,3,3,'2021-04-02 14:39:53',3),
(126,1,3,1,1,'2021-04-02 15:39:53',1),
(126,1,2,2,2,'2021-04-02 16:39:53',2),
(126,1,1,3,3,'2021-04-02 17:39:53',3)
Result:
Date, ServiceId, PriorityId, CumulativeTasksTotalOpenTo9, CumulativeTasksTotalOpenAllExecutorGroupsTo9, CumulativeTasksTotalOpenFirstExecutorGroupTo9, CumulativeTasksTotalOpenSecondExecutorGroupTo9, CumulativeTasksTotalOpenThirdExecutorGroupTo9, CumulativeTasksTotalInProgressTo9, CumulativeTasksTotalInProgressAllExecutorGroupsTo9, CumulativeTasksTotalInProgressFirstExecutorGroupTo9, CumulativeTasksTotalInProgressSecondExecutorGroupTo9,
CumulativeTasksTotalInProgressThirdExecutorGroupTo9, CumulativeTasksTotalClosedTo9, CumulativeTasksTotalClosedAllExecutorGroupsTo9,CumulativeTasksTotalClosedFirstExecutorGroupTo9,CumulativeTasksTotalClosedSecondExecutorGroupTo9,CumulativeTasksTotalClosedThirdExecutorGroupTo9,CumulativeTasksTotalOpenTo18, CumulativeTasksTotalOpenAllExecutorGroupsTo18, CumulativeTasksTotalOpenFirstExecutorGroupTo18, CumulativeTasksTotalOpenSecondExecutorGroupTo18, CumulativeTasksTotalOpenThirdExecutorGroupTo18, CumulativeTasksTotalInProgressTo18, CumulativeTasksTotalInProgressAllExecutorGroupsTo18, CumulativeTasksTotalInProgressFirstExecutorGroupTo18, CumulativeTasksTotalInProgressSecondExecutorGroupTo18,
CumulativeTasksTotalInProgressThirdExecutorGroupTo18, CumulativeTasksTotalClosedTo18, CumulativeTasksTotalClosedAllExecutorGroupsTo18,CumulativeTasksTotalClosedFirstExecutorGroupTo18,CumulativeTasksTotalClosedSecondExecutorGroupTo18,CumulativeTasksTotalClosedThirdExecutorGroupTo18