How to reset the cumulative sum based on condition?

Question

Sample data:

CREATE TABLE [dbo].[agent_sales]
(
    [date] [date] NULL,
    [agent] [nvarchar](50) NULL,
    [sale] [int] NULL
) ON [PRIMARY]
GO

INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-01-03' AS Date), N'Agent A', 10)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-02-05' AS Date), N'Agent A', 5)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-03-10' AS Date), N'Agent A', 20)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-04-10' AS Date), N'Agent A', 2)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-01-05' AS Date), N'Agent B', 5)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-02-06' AS Date), N'Agent B', 28)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-03-10' AS Date), N'Agent B', 5)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-04-10' AS Date), N'Agent B', 10)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-01-02' AS Date), N'Agent C', 35)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-02-04' AS Date), N'Agent C', 25)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-03-08' AS Date), N'Agent C', 15)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-04-10' AS Date), N'Agent C', 10)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-01-01' AS Date), N'Agent D', 5)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-02-02' AS Date), N'Agent D', 35)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-03-10' AS Date), N'Agent D', 31)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-04-10' AS Date), N'Agent D', 10)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-01-01' AS Date), N'Agent E', 32)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-02-01' AS Date), N'Agent E', 0)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-03-10' AS Date), N'Agent E', 20)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-04-10' AS Date), N'Agent E', 12)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-01-01' AS Date), N'Agent F', 32)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-02-02' AS Date), N'Agent F', 9)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-03-10' AS Date), N'Agent F', 11)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-04-10' AS Date), N'Agent F', 12)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-01-01' AS Date), N'Agent G', 32)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-02-02' AS Date), N'Agent G', 0)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-03-10' AS Date), N'Agent G', 20)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-04-10' AS Date), N'Agent G', 8)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-01-01' AS Date), N'Agent H', 32)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-03-10' AS Date), N'Agent H', 20)
GO
INSERT [dbo].[agent_sales] ([date], [agent], [sale]) VALUES (CAST(N'2021-04-10' AS Date), N'Agent H', 8)

SELECT statement output:

select date, agent, sale
from agent_sales

date                    agent   sales
--------------------------------------
2021-01-03 00:00:00.000 Agent A 10
2021-02-05 00:00:00.000 Agent A 5
2021-03-10 00:00:00.000 Agent A 20
2021-04-10 00:00:00.000 Agent A 2
2021-01-05 00:00:00.000 Agent B 5
2021-02-06 00:00:00.000 Agent B 28
2021-03-10 00:00:00.000 Agent B 5
2021-04-10 00:00:00.000 Agent B 10
2021-01-02 00:00:00.000 Agent C 35
2021-02-04 00:00:00.000 Agent C 25
2021-03-08 00:00:00.000 Agent C 15
2021-04-10 00:00:00.000 Agent C 10
2021-01-01 00:00:00.000 Agent D 5
2021-02-02 00:00:00.000 Agent D 35
2021-03-10 00:00:00.000 Agent D 31
2021-04-10 00:00:00.000 Agent D 10
2021-01-01 00:00:00.000 Agent E 32
2021-02-02 00:00:00.000 Agent E 0
2021-03-10 00:00:00.000 Agent E 20
2021-04-10 00:00:00.000 Agent E 12
2021-01-01 00:00:00.000 Agent F 32
2021-02-02 00:00:00.000 Agent F 9
2021-03-10 00:00:00.000 Agent F 11
2021-04-10 00:00:00.000 Agent F 12
2021-01-01 00:00:00.000 Agent G 32
2021-02-02 00:00:00.000 Agent G 0
2021-03-10 00:00:00.000 Agent G 20
2021-04-10 00:00:00.000 Agent G 8
2021-01-01 00:00:00.000 Agent H 32
2021-03-10 00:00:00.000 Agent H 20
2021-04-10 00:00:00.000 Agent H 8

I want to get the counts of agents who have crossed 30 sales cumulatively summed, but the counter (cumulative sum logic) should get reset if an agent has not made 30 sales in last 45 days.

Expected output:

YrMon	Count_Agent_more_than_30_sales
Jan21	5
Feb21	7
Mar21	5
Apr21	6

Logic:

Jan21 - 5 since C, E, F, G, H cross 30.
Feb21 - 7 since B, C, D, E, F, G, H cumulatively cross 30.
Mar21 - 5 since A, B, C, D, F cumulatively cross 30. Where as E, G, H are excluded because it has been 45 days since the last entry cumulatively crossing 30 sales.
Apr21 - 6 since A, B, C, D, E, F cumulatively cross 30. Where as G, H are excluded because it has been 45 days since the last entry cumulatively crossing 30 sales.

My query to calculate sum over period:

;WITH CTE AS 
(
    SELECT 
        CAST(YEAR([DATE]) AS VARCHAR) + ' ' + CAST(MONTH([DATE]) AS VARCHAR) YRMON, 
        [DATE], AGENT, SUM(SALE) SALES
    FROM 
        agent_sales
    GROUP BY  
        CAST(YEAR([DATE]) AS VARCHAR) + ' ' + CAST(MONTH([DATE]) AS VARCHAR), 
        AGENT, [DATE]
)
SELECT 
    *, 
    SUM(SALES) OVER(PARTITION BY AGENT ORDER BY YRMON) SUMOVERPERIOD 
FROM CTE
ORDER BY 3,2

Now I am trying to apply the logic on the calculated sum:

;WITH CTE AS (SELECT CAST(YEAR([DATE]) AS VARCHAR)+' '+CAST(MONTH([DATE]) AS VARCHAR) YRMON, [DATE], AGENT, SUM(SALE) SALES
      FROM agent_sales
      GROUP BY  CAST(YEAR([DATE]) AS VARCHAR)+' '+CAST(MONTH([DATE]) AS VARCHAR), [DATE], AGENT
      )
      SELECT *, SUM(SALES) OVER(PARTITION BY AGENT ORDER BY YRMON) SUMOVERPERIOD,
      CASE WHEN SUM(SALES) OVER(PARTITION BY AGENT ORDER BY YRMON)>30 THEN 1 ELSE 0 END AS CALC
      FROM CTE
      ORDER BY 3,2

This is giving me the cumulative sum.

To check cumulative sum based on 45 day:

 ;WITH CTE AS (SELECT CAST(YEAR([DATE]) AS VARCHAR)+' '+CAST(MONTH([DATE]) AS VARCHAR) YRMON, [DATE], AGENT, SUM(SALE) SALES
      FROM agent_sales
      GROUP BY  CAST(YEAR([DATE]) AS VARCHAR)+' '+CAST(MONTH([DATE]) AS VARCHAR), [DATE], AGENT
      )
      SELECT *, SUM(SALES) OVER(PARTITION BY AGENT ORDER BY YRMON) SUMOVERPERIOD,
      CASE WHEN SUM(SALES) OVER(PARTITION BY AGENT ORDER BY YRMON)>30 THEN 1 ELSE 0 END AS CUMULATIVE_ABOVE_30, 
      [DATE],LAG([DATE],1,[DATE]) OVER(PARTITION BY AGENT ORDER BY [DATE]) [LAG],
      DATEDIFF(DAY,LAG([DATE],1,[DATE]) OVER(PARTITION BY AGENT ORDER BY [DATE]),[DATE]) [DDIFF],
      CASE WHEN SUM(SALES) OVER(PARTITION BY AGENT ORDER BY YRMON)>30 AND DATEDIFF(DAY,LAG([DATE],1,[DATE]) OVER(PARTITION BY AGENT ORDER BY [DATE]),[DATE])<46 THEN 1 ELSE 0 END AS CUMULATIVE_ABOVE_30_AND_LAST_SALE_IN_45_DAYS
      FROM CTE
      ORDER BY 3,2

How do I get the above query to reset the cumulative sum counter with 45 day logic? For example - Agent G should not show up in Mar and Apr.

Same SQL as above, but with Month-wise Agent Names:

   ;WITH CTE AS (SELECT CAST(YEAR([DATE]) AS VARCHAR)+' '+CAST(MONTH([DATE]) AS VARCHAR) YRMON, [DATE], AGENT, SUM(SALE) SALES
  FROM agent_sales
  GROUP BY  CAST(YEAR([DATE]) AS VARCHAR)+' '+CAST(MONTH([DATE]) AS VARCHAR), [DATE], AGENT
  ),
  CTE1 as (SELECT *, SUM(SALES) OVER(PARTITION BY AGENT ORDER BY YRMON) SUMOVERPERIOD,
  CASE WHEN SUM(SALES) OVER(PARTITION BY AGENT ORDER BY YRMON)>30 THEN 1 ELSE 0 END AS CUMULATIVE_ABOVE_30, 
  [DATE] AS [DT],LAG([DATE],1,[DATE]) OVER(PARTITION BY AGENT ORDER BY [DATE]) [LAG],
  DATEDIFF(DAY,LAG([DATE],1,[DATE]) OVER(PARTITION BY AGENT ORDER BY [DATE]),[DATE]) [DDIFF],
  CASE WHEN SUM(SALES) OVER(PARTITION BY AGENT ORDER BY YRMON)>30 AND DATEDIFF(DAY,LAG([DATE],1,[DATE]) OVER(PARTITION BY AGENT ORDER BY [DATE]),[DATE])<46 THEN 1 ELSE 0 END AS CUMULATIVE_ABOVE_30_AND_LAST_SALE_IN_45_DAYS
  FROM CTE)
  select YRMON,AGENT FROM CTE1 WHERE CUMULATIVE_ABOVE_30_AND_LAST_SALE_IN_45_DAYS=1

Same as above, but with month-wise counts:

   ;WITH CTE AS (SELECT CAST(YEAR([DATE]) AS VARCHAR)+' '+CAST(MONTH([DATE]) AS VARCHAR) YRMON, [DATE], AGENT, SUM(SALE) SALES
  FROM agent_sales
  GROUP BY  CAST(YEAR([DATE]) AS VARCHAR)+' '+CAST(MONTH([DATE]) AS VARCHAR), [DATE], AGENT
  ),
  CTE1 as (SELECT *, SUM(SALES) OVER(PARTITION BY AGENT ORDER BY YRMON) SUMOVERPERIOD,
  CASE WHEN SUM(SALES) OVER(PARTITION BY AGENT ORDER BY YRMON)>30 THEN 1 ELSE 0 END AS CUMULATIVE_ABOVE_30, 
  [DATE] AS [DT],LAG([DATE],1,[DATE]) OVER(PARTITION BY AGENT ORDER BY [DATE]) [LAG],
  DATEDIFF(DAY,LAG([DATE],1,[DATE]) OVER(PARTITION BY AGENT ORDER BY [DATE]),[DATE]) [DDIFF],
  CASE WHEN SUM(SALES) OVER(PARTITION BY AGENT ORDER BY YRMON)>30 AND DATEDIFF(DAY,LAG([DATE],1,[DATE]) OVER(PARTITION BY AGENT ORDER BY [DATE]),[DATE])<46 THEN 1 ELSE 0 END AS CUMULATIVE_ABOVE_30_AND_LAST_SALE_IN_45_DAYS
  FROM CTE)
  select YRMON,count(*) FROM CTE1 WHERE CUMULATIVE_ABOVE_30_AND_LAST_SALE_IN_45_DAYS=1
  group by YRMON

I am unable to get query to reset the cumulative sum counter with 45 day logic.

`select date, agent, sales from agentsales` ? Your table name is `agent_sales` and column is `sale` Please ensure that any query that you posted is correspond to the schema — Squirrel, Oct 20 '21 at 07:28
i find desired result different , can you check it again ?: -2021 April 2-| -2021 February 7-| -2021 March 3-| -2021 January 5- — Ali Fidanli, Oct 20 '21 at 08:49
If you want to reset the cumulative counter every time there is not a sale in the last 45 days, perhaps you could just count the agents that have more than 30 sales in the last 45 days? Logically this should be equivalent. The other consideration is what date you want to look back from. Should it be the last 45 days prior to the start of the month, or the end of the month, or something else? — Brenton, Oct 20 '21 at 09:08
@AliFidanli - your values for March and April are different, can you tell me what Agent's do you get for those months? — variable, Oct 20 '21 at 09:09
@Brenton - I want to get the count of such Agent's in each month. I have given the expected output in the question. I have given the SQL query at the end of the question, any feedback will be helpful. I can't figure out a way to reset the counter. — variable, Oct 20 '21 at 09:30
Could you explain why A is considered to have crossed a cumulative sum of 30 in March and April? I would have expected the counter to have reset since it has less than 30 sales in the last 45 days. — Brenton, Oct 20 '21 at 09:52
@variable -- output for april Agent A 2021 April 22 Agent B 2021 April 15 Agent C 2021 April 25 Agent D 2021 April 41 Agent E 2021 April 32 Agent F 2021 April 23 Agent G 2021 April 28 Agent H 2021 April 28 — Ali Fidanli, Oct 20 '21 at 10:05
@Brenton - the counter should reset only when there are no sales in last 45 days. Subsequently, to qualify it should have 30+ sales. — variable, Oct 20 '21 at 11:12
@variable It seems you are searching for pattern "capping a running total". It could be handled by recursive CTE, loops/cursor or "quirky update"(do not use this approach). Related [Conditional SUM on Oracle](https://stackoverflow.com/questions/52936012/conditional-sum-on-oracle) and [Running Total until specific condition is true](https://stackoverflow.com/questions/33069463/running-total-until-specific-condition-is-true) — Lukasz Szozda, Oct 24 '21 at 10:13

score 0 · Answer 1 · answered Oct 20 '21 at 15:49

Note: I get different results, perhaps misinterpreting one of your rules, but you will get the idea.

Try this:

DECLARE @ClosingDay     int =   21
    ,   @CicleDays      int =   -45
    ,   @TargetSales    int =   30
;

WITH AgentSaleCicle     AS
    (
        SELECT  
                *
            ,   CicleBegin  =   DATEADD(DAY, @CicleDays,    CONVERT(date, LTRIM(YEAR([date])*10000+MONTH([date])*100+@ClosingDay)))
            ,   CicleEnd    =                               CONVERT(date, LTRIM(YEAR([date])*10000+MONTH([date])*100+@ClosingDay))
        FROM    [dbo].[agent_sales]
    )
,   AgentSaleCicleSum   AS
    (
        SELECT  
                *
        --  ,   CicleDays   =   DATEDIFF(DAY, CicleBegin, CicleEnd) 
            ,   CicleSales  =   (
                                SELECT  SUM(Sale) 
                                FROM    [dbo].[agent_sales] IA
                                WHERE   1=1
                                    AND IA.Agent    =   OA.Agent
                                    AND IA.[Date]   BETWEEN CicleBegin  AND CicleEnd
                            )
        FROM    AgentSaleCicle  OA
    )

SELECT 
        CicleEnd
    ,   CicleAgentCount =   SUM(1)
FROM    AgentSaleCicleSum
WHERE   CicleSales  >= @TargetSales
GROUP BY CicleEnd
ORDER BY CicleEnd

How to reset the cumulative sum based on condition?

1 Answers1