SUM Value Based On Day Near Each Other By A Day-CodePudding

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]

CodePudding user response：

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]