Sum up column between two dates, group by 3 columns

Can someone please suggest a way to sum up the "count" column between two dates and group it according to the "mcc", "partner" and "result" columns.

"Stats" table:

id	date	count	mcc	partner	result
1	2022-07-13	4	293	TynTec	SUCCESS
2	2022-07-13	5	293	TynTec	FATAL
3	2022-07-13	4	294	NTH	SUCCESS
4	2022-07-14	9	294	NTH	SUCCESS
5	2022-07-14	4	294	TynTec	SUCCESS
6	2022-07-14	10	294	NTH	FATAL

Desired output:

id	date	count	mcc	partner	result
1	2022-07-13 - 2022-07-14	8	293	TynTec	SUCCESS
2	2022-07-13 - 2022-07-14	5	293	TynTec	FATAL
3	2022-07-13 - 2022-07-14	13	294	NTH	SUCCESS
4	2022-07-13 - 2022-07-14	10	294	NTH	FATAL

Thank you.

CodePudding user response：

Hope this will helps you.

   select concat(mindv , ' - ' , maxdv) as dates, 
          sum(cnt) as counts,
          min(mcc) as mcc,
          partner,
          res as result 
   from tmp_1,
   (select min(dt) as mindv ,partner as pmin from tmp_1 group by 2) mind,
   (select max(dt) as maxdv , partner as pmax  from tmp_1 group by 2) maxd
   where 
      mind.pmin=maxd.pmax
      and partner=mind.pmin
      group by 1,4,5;

CodePudding user response：

This piece of code returns the result that you want; It didn't optimize but gives you the idea of what you have to do:

SELECT  
    CONVERT(char(10), T.[date],126)  
    ' - '
      CONVERT(char(10), DATEADD(DAY,IIF(DATEDIFF(DAY, T.[date], NextDate) 
IS NOT NULL, DATEDIFF(DAY, T.[date], NextDate),1),T.[date]),126),
    T.ItemCount
    T.mcc,
    T.[partner],
    T.[result]
FROM    (SELECT T1.*,(SELECT  MIN([date]) FROM (SELECT 
            SUM([count]) AS ItemCount,
            [mcc], 
            [partner], 
            [result],
            DATEADD(DAY,0, datediff(day,0, [date])) AS [date]
        FROM [TABLENAME] 
        WHERE [date] between '[FromDate]' and '[ToDate]'
        GROUP BY [mcc], [partner], [result], dateadd(DAY,0, DATEDIFF(DAY,0, 
  [date]))) AS T2
                    WHERE   T2.mcc = T1.mcc AND T2.[partner]=T1.[partner] 
AND T2.[result]=T1.[result]
                    AND     T2.[date] > T1.[date]
                ) AS NextDate
FROM (SELECT 
            SUM([count]) AS ItemCount,
            [mcc], 
            [partner], 
            [result],
            DATEADD(DAY,0, DATEDIFF(day,0, [date])) AS [date]
        FROM [TABLENAME] 
        WHERE [date] between '[FromDate]' and '[ToDate]'
        GROUP BY [mcc], [partner], [result] , DATEADD(DAY,0, 
DATEDIFF(DAY,0, [date]))) AS T1 )AS T

To describe a quick and short senario about the code :

I generate a query that GROUP BY columns you said and the date.
Then I get NextDate of SELECT Statement to generate Time Period
In final step I select the columns that you want from table and generate Time Period related to Date and NextDate

note: If NextDate column be null generated date automatically add one day to Time Period.

If you have any questions write them below in the comments.