SQL Query with Date, Child and Sweets eaten-CodePudding

I have a question like this

I'm not sure how to solve this

CodePudding user response：

DROP TABLE IF EXISTS newtable;
CREATE TABLE newtable
(
  DateD DATE NOT NULL,
  Child VARCHAR(10)NOT NULL,
  SweetsEaten TINYINT NOT NULL
)
INSERT INTO newtable ( DateD, Child, SweetsEaten) 
VALUES ('20220101', 'Tom', 3), 
       ('20220102', 'Jane', 2), 
       ('20220111', 'Leslie', 1), 
       ('20220214', 'Tom', 0), 
       ('20220218', 'Jane', 5), 
       ('20220201', 'Leslie', 1), 
       ('20220307', 'Leslie', 3 ), 
       ( '20220309', 'Tom', 2), 
       ('20220318', 'Jane', 0);
   SELECT TOP 1 WITH TIES * FROM
   (
     SELECT N.DateD,N.Child,N.SweetsEaten,
      SUM(N.SweetsEaten)OVER(PARTITION BY N.Child ORDER BY N.DateD ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)XCOL  
     FROM newtable AS N
   ) X WHERE X.XCOL>=5
    ORDER BY ROW_NUMBER()OVER(PARTITION BY X.Child ORDER BY XCOL ASC)

Hope the above is suitable for you (SQL Server 2017)

CodePudding user response：

The inner from is the cumulative sum of sweets, so you only need to select the first occurrence, MIN(Date), when the cumulative sum is greater then 5.

SELECT

    Child,
    MIN(Date)

FROM    (
        SELECT  
            Child,
            DATE,
            SUM(SweetsEaten) OVER(PARTITION BY Child ORDER BY Date) AS SweetsEatenToDate
        FROM table1
        )

WHERE SweetsEatenToDate >= 5
GROUP BY Child

The cumulative sum reads like this: Your SUMing all the sweets PARTIONing them BY Child ORDERed BY Date