Get progressive sum of column by date



  • I've an Employee table with StartDate and EndDate as columns. How can I get a progressive sum of employees on per daily basis?

    ID StartDate EndDate
    1 2022-02-01 2022-02-28
    2 2022-02-01 2022-03-31
    3 2022-02-02 2022-03-31

    Output should be --

    Date Count
    2022-02-01 2
    2022-02-02 3


  • If I understand the question are you expecting an output as below.

    create table #table(id int,start_date datetime,end_date datetime)
    

    insert into #table
    select 1,'2022-02-01','2022-02-28'
    UNION ALL
    select 2,'2022-02-01','2022-03-31'
    UNION ALL
    select 3,'2022-02-02','2022-03-31'

    ;WITH Tally (N) AS
    (
    -- Tally table starting at 0
    SELECT 0 UNION ALL
    -- 8000 rows
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n)
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d(n)
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0)) e(n)
    )
    SELECT DATEADD(day,n, start_date) thedate,COUNT(id) AS total_emp
    FROM #table
    CROSS JOIN Tally
    WHERE N <=DATEDIFF(DAY,START_DATE,end_date)
    GROUP BY DATEADD(day,n, start_date)
    ORDER BY thedate

    Note the tally table code is being picked up from https://dwaincsql.com/2014/03/27/tally-tables-in-t-sql and it will calcluate for a maximum of 8000 rows




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2