SUM Value Based On Day Near Each Other By A Day

Question

So, I wanted to get the SUM of New Ink (ml) and Ink Used (ml) grouped by Date, Paper Code, and Ink Code but I also wanted the Date group the difference of the Date only by a day

Main Table

Date	Paper Code	Ink Code	New Ink (ml)	Ink Used (ml)
10-1-2022	911	C21	10	8
10-1-2022	911	C29	9	3
10-2-2022	911	C21	8	3
10-5-2022	911	C21	25	15
10-12-2022	911	C21	10	8
10-13-2022	911	C21	8	6
10-15-2022	911	C21	6	6
10-15-2022	911	C29	9	9

Expected result should be :

For Date 10-1-2022 and 10-2-2022

Paper Code	Ink Code	New Ink (ml)	Ink Used (ml)
911	C21	18	11
911	C29	9	3

For Date 10-5-2022

Paper Code	Ink Code	New Ink (ml)	Ink Used (ml)
911	C21	25	15

For Date 10-12-2022 and 10-13-2022

Paper Code	Ink Code	New Ink (ml)	Ink Used (ml)
911	C21	18	14

For Date 10-15-2022

Paper Code	Ink Code	New Ink (ml)	Ink Used (ml)
911	C21	6	6
911	C29	9	9

and all combined would be

Paper Code	Ink Code	New Ink (ml)	Ink Used (ml)
911	C21	18	11
911	C29	9	3
911	C21	25	15
911	C21	18	14
911	C21	6	6
911	C29	9	9

I've tried

SELECT 
    [Paper Code],
    [Ink Code],
    SUM([New Ink (ml)]) AS [New Ink (ml)],
    SUM([Ink Used (ml)]) AS [Ink Used (ml)]
FROM [Table Ink]
GROUP BY [Paper Code], [Ink Code]

I trying to get it. You want to sum up two days by combining (1,2), (3,4), (5,6) ... right ? What I don't understand in your example is the aggregation for 10-12-2022 and 10-13-2022 where it should result in 18(new Ink) and 14 (ink used). Could you please check ? — Andi Kleinbichler, Nov 01 '22 at 06:11
@AndiKleinbichler Oops, fixed! I just copy pasted that and forgot to change the value, thank you! To be more specific with the combined two days is that, the days only has to be next to each other, like 11-12, 8-9, 13-14. And in this database there won't be days like 3-4-5 but more like 3-4 and then 6-7. — Rohad Bokhar, Nov 01 '22 at 06:15
You could use some window functions Lag/Leap for that purpose (https://www.sqlshack.com/sql-lag-function-overview-and-examples/). It can combine fields from previous and current records values from from a partition. In the select part of the statement you can than build proper sums. But please take care of performance. For large tables this would not scale well. — Andi Kleinbichler, Nov 01 '22 at 06:35

score 2 · Accepted Answer · answered Nov 01 '22 at 08:28

As suggested in comments, you can use LAG() or LEAD() to identify the group of rows. After that just use GROUP BY as per normal

with cte as
(
  select *,
         g = case when datediff(day, lag([Date]) over (order by [Date]),
                                     [Date]) > 1
                  then 1
                  else 0
                  end
  from   [Table Ink]
),
cte2 as
(
  select *, grp = sum(g) over (order by [Date])
  from   cte
)
select [Paper Code],  [Ink Code],
       sum([New Ink (ml)])  as [New Ink (ml)],
       sum([Ink Used (ml)]) as [Ink Used (ml)]
from   cte2
group by grp, [Paper Code],  [Ink Code]

SUM Value Based On Day Near Each Other By A Day

1 Answers1