question

21169824 avatar image
0 Votes"
21169824 asked 21169824 commented

Cumulative total for tasks version table (about 100 000 records)

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

sql-server-transact-sql
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

HI @21169824,

There are many columns with no data in your expected result.

Please also provide proper expected output with data and more details about your requirement by listing some examples of several dates.

Thank you for understanding!

Best regards
Melissa

0 Votes 0 ·

Hello Melissa.
(SET DATEFORMAT ymd;)
The output:
'2021-03-30',1,1,0,0,0,0,0, 0,0,0,0,0, 0,0,0,0,0, 0,0,0,0,0, 0,0,0,0,0, 2,2,0,0,2
'2021-03-31',1,1,0,0,0,0,0, 0,0,0,0,0, 2,2,0,0,2, 0,0,0,0,0, 0,0,0,0,0, 4,4,0,0,4
'2021-04-01',1,1,0,0,0,0,0, 0,0,0,0,0, 4,4,0,0,4, 0,0,0,0,0, 0,0,0,0,0, 6,6,0,0,6
'2021-04-02',1,1,0,0,0,0,0, 0,0,0,0,0, 6,6,0,0,6, 0,0,0,0,0, 0,0,0,0,0, 8,8,0,0,8
Column description:
CumulativeTasksTotalOpenTo9 - Cumulative Tasks Total to 'selected_date 09:00:00' with [StatusId]=1
CumulativeTasksTotalOpenAllExecutorGroupsTo9 - Cumulative Tasks Total to 'selected_date 09:00:00' with [StatusId]=1 and [ExecutorGroupId] in (1,2,3)
CumulativeTasksTotalOpenFirstExecutorGroupTo9 - Cumulative Tasks Total to 'selected_date 09:00:00' with [StatusId]=1 and [ExecutorGroupId]=1
...
CumulativeTasksTotalClosedThirdExecutorGroupTo18 - Cumulative Tasks Total to 'selected_date 18:00:00' with [StatusId]=3 and [ExecutorGroupId]=3

0 Votes 0 ·

Hello Melissa.
(SET DATEFORMAT ymd;)
The output:
'2021-03-30',1,1,0,0,0,0,0, 0,0,0,0,0, 0,0,0,0,0, 0,0,0,0,0, 0,0,0,0,0, 2,2,0,0,2
'2021-03-31',1,1,0,0,0,0,0, 0,0,0,0,0, 2,2,0,0,2, 0,0,0,0,0, 0,0,0,0,0, 4,4,0,0,4
'2021-04-01',1,1,0,0,0,0,0, 0,0,0,0,0, 4,4,0,0,4, 0,0,0,0,0, 0,0,0,0,0, 6,6,0,0,6
'2021-04-02',1,1,0,0,0,0,0, 0,0,0,0,0, 6,6,0,0,6, 0,0,0,0,0, 0,0,0,0,0, 8,8,0,0,8
Column description:
CumulativeTasksTotalOpenTo9 - Cumulative Tasks Total to 'selected_date 09:00:00' with [StatusId]=1
CumulativeTasksTotalOpenAllExecutorGroupsTo9 - Cumulative Tasks Total to 'selected_date 09:00:00' with [StatusId]=1 and [ExecutorGroupId] in (1,2,3)
CumulativeTasksTotalOpenFirstExecutorGroupTo9 - Cumulative Tasks Total to 'selected_date 09:00:00' with [StatusId]=1 and [ExecutorGroupId]=1
...
CumulativeTasksTotalClosedThirdExecutorGroupTo18 - Cumulative Tasks Total to 'selected_date 18:00:00' with [StatusId]=3 and [ExecutorGroupId]=3

0 Votes 0 ·

Hello Melissa.
(SET DATEFORMAT ymd;)
The output:
'2021-03-30',1,1,0,0,0,0,0, 0,0,0,0,0, 0,0,0,0,0, 0,0,0,0,0, 0,0,0,0,0, 2,2,0,0,2
'2021-03-31',1,1,0,0,0,0,0, 0,0,0,0,0, 2,2,0,0,2, 0,0,0,0,0, 0,0,0,0,0, 4,4,0,0,4
'2021-04-01',1,1,0,0,0,0,0, 0,0,0,0,0, 4,4,0,0,4, 0,0,0,0,0, 0,0,0,0,0, 6,6,0,0,6
'2021-04-02',1,1,0,0,0,0,0, 0,0,0,0,0, 6,6,0,0,6, 0,0,0,0,0, 0,0,0,0,0, 8,8,0,0,8
Column description:
CumulativeTasksTotalOpenTo9 - Cumulative Tasks Total to 'selected_date 09:00:00' with [StatusId]=1
CumulativeTasksTotalOpenAllExecutorGroupsTo9 - Cumulative Tasks Total to 'selected_date 09:00:00' with [StatusId]=1 and [ExecutorGroupId] in (1,2,3)
CumulativeTasksTotalOpenFirstExecutorGroupTo9 - Cumulative Tasks Total to 'selected_date 09:00:00' with [StatusId]=1 and [ExecutorGroupId]=1
...
CumulativeTasksTotalClosedThirdExecutorGroupTo18 - Cumulative Tasks Total to 'selected_date 18:00:00' with [StatusId]=3 and [ExecutorGroupId]=3

0 Votes 0 ·
MelissaMa-msft avatar image
1 Vote"
MelissaMa-msft answered 21169824 commented

Hi @21169824,

Please refer updated one from below:

 ;with cte as (
 select distinct TaskId,ServiceId,PriorityId,StatusId,ExecutorGroupId,version
 ,case when Changed>DATEADD(HOUR,18,cast(cast(Changed as date) as datetime)) then dateadd(day,1,cast(Changed as date)) else cast(Changed as date) end Changed
 from TaskHistory
 where  StatusId=3 and [ExecutorGroupId]=3)
 ,cte1 as (select *,ROW_NUMBER() over (partition by Changed,taskid order by version desc,Changed desc) rn
 from cte)
 ,cte2 as (
 select cast(Changed as date) Changed,ServiceId,PriorityId,count(TaskId) count from cte1 
 where rn=1
 group by cast(Changed as date),ServiceId,PriorityId
 ),cte3 as (
 select a.DATE,b.ServiceId,b.PriorityId
 ,sum(isnull(count,0)) over (order by changed ROWS UNBOUNDED PRECEDING) Total18 
 from #cal a left join cte2 b
 on a.DATE=b.changed)
 select date changed,ServiceId=MAX(ServiceId) OVER (PARTITION BY c),PriorityId=MAX(PriorityId) OVER (PARTITION BY c),
 CumulativeTasksTotalClosedThirdExecutorGroupTo18=MAX(Total18) OVER (PARTITION BY c) 
 from 
 (
 select a.date,ServiceId,PriorityId,Total18,c=COUNT(ServiceId) OVER (ORDER BY a.date)
  from cte3  a
 ) d
  order by Date

Output:

 changed ServiceId PriorityId CumulativeTasksTotalClosedThirdExecutorGroupTo18
 2017-05-25    1    1    1
 2017-05-26    1    1    1
 2017-05-27    1    1    1
 2017-05-28    1    1    1
 2017-05-29    1    1    1
 2017-05-30    1    1    1
 2017-05-31    1    1    1
 2017-06-01    1    1    1
 2017-06-02    1    1    5
 2017-06-03    1    1    5
 2017-06-04    1    1    5
 2017-06-05    1    1    5
 2017-06-06    1    1    6
 2017-06-07    1    1    7

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi, Melissa
You are The Best.
Thanks a lot!!!
Best regards
Vasily

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered 21169824 published

Hi @21169824,

Please refer below one example of CumulativeTasksTotalClosedThirdExecutorGroupTo18:

 ;with cte as (
 select *,ROW_NUMBER() over (partition by cast(Changed as date),taskid order by version desc,Changed desc) rn
 from TaskHistory
 where Changed<=DATEADD(HOUR,18,cast(cast(Changed as date) as datetime))
 and StatusId=3 and [ExecutorGroupId]=3
 ),cte1 as (
 select cast(Changed as date) Changed,ServiceId,PriorityId,count(TaskId) count from cte 
 where rn=1
 group by cast(Changed as date),ServiceId,PriorityId
 )
 select Changed,ServiceId,PriorityId,sum(count) over (order by changed ROWS UNBOUNDED PRECEDING) CumulativeTasksTotalClosedThirdExecutorGroupTo18 
  from cte1

Output:

 Changed ServiceId PriorityId CumulativeTasksTotalClosedThirdExecutorGroupTo18
 2021-03-30    1    1    2
 2021-03-31    1    1    4
 2021-04-01    1    1    6
 2021-04-02    1    1    8

If above is working, you could refer above and modify the conditions in first cte according to your requirement to build the whole query.

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello, Melissa
For this data your request doesn't work.
insert into TaskHistory ([TaskId],[ServiceId],[PriorityId],[StatusId],[ExecutorGroupId],[Changed],[Version]) values
(1,1,1,3,3,'2017-05-25 17:05:16.633',0),
(2,1,1,3,3,'2017-06-02 10:24:43.117',0),
(3,1,1,3,3,'2017-06-02 12:32:07.347',0),
(4,1,1,3,3,'2017-06-02 12:41:35.853',0),
(5,1,1,3,3,'2017-06-02 12:46:29.803',0),
(6,1,1,3,3,'2017-06-05 18:47:54.443',0),
(6,1,1,3,3,'2017-06-05 18:48:04.443',0),
(7,1,1,3,3,'2017-06-07 11:32:40.563',0)
Your result:
Changed ServiceId PriorityId CumulativeTasksTotalClosedThirdExecutorGroupTo18
2017-05-25 1 1 1
2017-06-02 1 1 5
2017-06-07 1 1 6
Right result:
2017-05-25 1 1 1
2017-05-26 1 1 1
2017-05-27 1 1 1
2017-05-28 1 1 1
2017-05-29 1 1 1
2017-05-30 1 1 1
2017-05-31 1 1 1
2017-06-01 1 1 1
2017-06-02 1 1 5
2017-06-03 1 1 5
2017-06-04 1 1 5
2017-06-05 1 1 5
2017-06-06 1 1 6
2017-06-07 1 1 7
BR, Vasily

0 Votes 0 ·
21169824 avatar image
0 Votes"
21169824 answered 21169824 published

Hi, Melissa
This isn't my mistake:
(6,1,1,3,3,'2017-06-05 18:47:54.443',0),
(6,1,1,3,3,'2017-06-05 18:48:04.443',0),
This meen that we have 2 changes for task number 6 on 2017-06-05.
Because this two datetimes later then 2017-06-05 18:00:00 on 2017-06-05 we have only 5 previous tasks => 2017-06-05 1 1 5
There is no data changes in 2017-06-06, but we need in output all dates of year and on 2017-06-06 18:00:00 we have 5+1(task with number 6) => 2017-06-06 1 1 6
And on 2017-06-07 18:00:00 we have 6+1(task with number 7) => 2017-06-07 1 1 7
Best regards
Vasily

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered 21169824 commented

Hi @21169824,

Please provide more detailed information together with sample data and expected output in the origial post so that we could proceed with building this query more efficiently.

I am concerned about below:

(6,1,1,3,3,'2017-06-05 18:47:54.443',0),
(6,1,1,3,3,'2017-06-05 18:48:04.443',0),

2017-06-05 1 1 5
2017-06-06 1 1 6
2017-06-07 1 1 7

There is no data in 2017-06-06 but the total is added in your right result. Besides, both changed in 2017-06-05 are later than 18:00:00 but you still include them in your right result. Please help double check this. Thanks.

Please refer below per my understanding:

 drop table if exists #cal
    
 DECLARE @StartDate  date 
 DECLARE @CutoffDate date 
    
 select @StartDate=min(Changed)
 from TaskHistory
    
 select @CutoffDate=max(Changed)
 from TaskHistory
    
 ;WITH seq(n) AS 
 (
   SELECT 0 UNION ALL SELECT n + 1 FROM seq
   WHERE n < DATEDIFF(DAY, @StartDate, @CutoffDate)
 ),
 d(d) AS 
 (
   SELECT DATEADD(DAY, n, @StartDate) FROM seq
 )
 SELECT DATE= CONVERT(date, d) 
 into #cal
 FROM D
   ORDER BY DATE
   OPTION (MAXRECURSION 0);
    
 ;with cte as (
 select *,ROW_NUMBER() over (partition by cast(Changed as date),taskid order by version desc,Changed desc) rn
 from TaskHistory
 where Changed<=DATEADD(HOUR,18,cast(cast(Changed as date) as datetime))
 and StatusId=3 and [ExecutorGroupId]=3
 ),cte1 as (
 select cast(Changed as date) Changed,ServiceId,PriorityId,count(TaskId) count from cte 
 where rn=1
 group by cast(Changed as date),ServiceId,PriorityId
 ),cte2 as (
 select a.DATE,b.ServiceId,b.PriorityId
 ,sum(isnull(count,0)) over (order by changed ROWS UNBOUNDED PRECEDING) Total18 
 from #cal a left join cte1 b
 on a.DATE=b.changed)
 select date changed,ServiceId=MAX(ServiceId) OVER (PARTITION BY c),PriorityId=MAX(PriorityId) OVER (PARTITION BY c),
 CumulativeTasksTotalClosedThirdExecutorGroupTo18=MAX(Total18) OVER (PARTITION BY c) 
 from 
 (
 select a.date,ServiceId,PriorityId,Total18,c=COUNT(ServiceId) OVER (ORDER BY a.date)
  from cte2  a
 ) d
  order by Date

Output:

 changed    ServiceId    PriorityId    CumulativeTasksTotalClosedThirdExecutorGroupTo18
 2017-05-25    1    1    1
 2017-05-26    1    1    1
 2017-05-27    1    1    1
 2017-05-28    1    1    1
 2017-05-29    1    1    1
 2017-05-30    1    1    1
 2017-05-31    1    1    1
 2017-06-01    1    1    1
 2017-06-02    1    1    5
 2017-06-03    1    1    5
 2017-06-04    1    1    5
 2017-06-05    1    1    5
 2017-06-06    1    1    5
 2017-06-07    1    1    6

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.