question

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

Cumulative total for tasks version table

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

sql-server-transact-sql
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.

1 Answer

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

Hi @21169824,

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.

· 3
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.
I can't see [Version] field in your T-SQL request, but in my question «we need count to SUM only oldest TASK version with same Priority»
So if I
SELECT [Id],[TaskId],[PriorityId],[Changed],[Version]
FROM [TaskHistory]
where [PriorityId]=9 and cast([Changed] as date)='2016-05-16'
order by [TaskId],[Id]
I see 14 records:
Id TaskId PriorityId Changed Version
914 163 9 2016-05-16 14:41:01.340 0
915 164 9 2016-05-16 15:33:39.307 0
925 164 9 2016-05-16 16:00:12.283 0
927 164 9 2016-05-16 16:03:41.100 0
916 165 9 2016-05-16 15:34:25.090 0
924 165 9 2016-05-16 15:59:54.770 0
928 165 9 2016-05-16 16:04:19.647 0
918 167 9 2016-05-16 15:42:00.393 0
919 167 9 2016-05-16 15:45:18.380 0
920 167 9 2016-05-16 15:45:35.070 0
923 167 9 2016-05-16 15:49:00.853 0
921 168 9 2016-05-16 15:46:47.740 0
922 168 9 2016-05-16 15:47:10.540 0
926 169 9 2016-05-16 16:01:45.240 0
But for date='2016-05-16' I need only count 6 tasks. Version in (0,1,2,3,4,...)

0 Votes 0 ·

The standard recommendation for this type of query, the recommendation is that you post CREATE TABLE statements for your table and INSERT statements with sample data, enough to illustrate all angles of the problem.

Melissa produced a query that gives the correct result for the data you posted initially, but apparently that was not enough. Melissa was kind to compose INSERT statements the first time, but she may not do it a second time. And I am not going to do it. Least of all when I am not sure that I see the full story.

0 Votes 0 ·

Hi @21169824,

Thanks for your update.

It is recommended for you to post a new question and provide CREATE TABLE statements for your table and INSERT statements with sample data as I posted, more details about your latest requirement and the expected output.

Then we could proceed to develop this query as you expected.

Thank you for understanding!

Best regards
Melissa

0 Votes 0 ·