count by week by start/end date

  • I'm trying to show a count by week but I am unsure of how to find dates between years? How do I show the Year, Week, and count for the example below? This ID_NUM should show 1 for every week for the entire year. I've shown what I've tried so far below. Thanks.

     

    q

    I'd want it to look like below. I've taken out some weeks to make it smaller for posting but it should have the entire year.

     

    r

    with test as
    (
    SELECT DISTINCT ID_NUM
    ,EFFDAT
    ,EXPDAT


    ,DATEPART(MONTH,EFFDAT) AS effmonth
    ,DATEPART(WEEK,EFFDAT) AS EFFWEEK
    ,DATEPART(year,EFFDAT) AS effyear

    ,DATEPART(MONTH,EXPDAT) AS expmonth
    ,DATEPART(WEEK,EXPDAT) AS EXPWEEK
    ,DATEPART(year,EXPDAT) as expyear

    FROM AUTHS
    WHERE EXPDAT >= '2019-01-01'
    AND SVCCLS IN ('IDR','IDD','IPD','BED','IPA','IPC','IPP','IPW')

    )
    ,cte as
    (
    select effyear
    ,min(effweek) week
    ,max(expweek) max_week
    from test
    group by effyear


    union all

    select effyear
    ,week + 1
    ,max_week
    from cte
    where week < max_week --and effyear = effyear
    )
    --select * from cte

    select c.effyear, c.week, count(distinct t.id_num) cnt
    from cte c
    left join test t on c.week between t.effweek and t.expweek and c.effyear = t.effyear
    group by c.effyear,c.week
    order by c.effyear,c.week?

     

    • This topic was modified 3 years, 12 months ago by  smattiko83.
    • This topic was modified 3 years, 12 months ago by  smattiko83.
  • I'm not quite clear what you mean. The ID is 1 for every row? Or do you mean there should be a row for every week of the year, regardless of if there is other data?

    This gets you weeks for a few years.

    WITH myTally(n)
    AS
    (SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT null))
    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a(n)
    CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) b(n)
    CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) c(n)
    )
    , cteDates (DateValue) as
    ( SELECT DATEADD(WEEK, n, '1-1-2019') AS Datevalue
    FROM Mytally
    )
    SELECT YEAR(cteDates.DateValue) AS DateYear
    , DATEPART(WEEK, cteDates.DateValue) AS Dateweek
    , cteDates.DateValue
    FROM cteDates
    ORDER BY cteDates.DateValue
  • Re-reading this... are you trying to do a running total, or just a normal total? If you want a running total, I'd use a windowing function.

    SUM([numeric column]) OVER (PARTITION BY date ROWS BETWEEN UNBOUNDED AND CURRENT ROW)

    or similar.

  • You could add a date dimension table to your model.  Week count for the year/month/quarter and many others are already in the table, you would have to join to it with your date.  Its a two step process, create table then populate.

    https://www.codeproject.com/Articles/647950/Create-and-Populate-Date-Dimension-for-Data-Wareho

  • I have a date calendar table. I'm just not sure how to use it to find the weeks between a date range.

  • Post the definition of your Calendar table.  Are you familiar with dimension tables in data warehouse designs? Normally a Calendar dimension will be something like this:

    CREATE TABLE Calendar (
       CalendarDate DATE PRIMARY KEY,
       Year INT,
       MonthNumber TINYINT,
       DayOfMonth TINYINT,
    WeekNumberOfYear TINYINT,
       MonthName VARCHAR(8),
    …);

    Then you can join that to your data/fact table and summarize any way you want.

    ---- new stuff ---

    Here's the data I started with:

    ---- new stuff ---
    Here's the data I started with:
    CREATE TABLE MyData (
     ID_NUM INT PRIMARY KEY,
     EFF_DATE DATE NOT NULL,
     EXP_DATE DATE NOT NULL
    );
    GO
    INSERT INTO MyData VALUES
     (526,'5/21/2019','5/21/2020')
     ,(527,'6/1/2019','1/1/2020')
     ,(528,'5/1/2019','6/15/2019');

    Needed something to do, so I sorted this out (I think)… I used someone's dynamic Calendar function from here. (Can't remember whose), but here's the solution I came up with... First is the Calendar function I borrowed:

     CREATE FUNCTION [dbo].[GenerateCalendar] 
            (
            @FromDate   DATETIME
            ,@NoDays    INT   
            )
    -- Generates a calendar table with sequential day numbering (@FromDate = SeqNo 1).
    -- See RETURNS table (comments) for meaning of each column.
    -- Notes:       1) Max for NoDays is 65536, which runs in just over 2 seconds.
    --
    -- Example calls to generate the calendar:
    -- 1) Forward for 365 days starting today:
    --             DECLARE @Date DATETIME
    --             SELECT @Date = GETDATE()
    --             SELECT *
    --             FROM dbo.GenerateCalendar(@Date, 365)
    --             ORDER BY SeqNo;
    -- 2) Backwards for 365 days back starting today:
    --             DECLARE @Date DATETIME
    --             SELECT @Date = GETDATE()
    --             SELECT *
    --             FROM dbo.GenerateCalendar(@Date, -365)
    --             ORDER BY SeqNo;
    -- 3) For only the FromDate:
    --             DECLARE @Date DATETIME
    --             SELECT @Date = GETDATE()
    --             SELECT *
    --             FROM dbo.GenerateCalendar(@Date, 1);
    -- 4) Including only the last week days of each month:
    --             Note: Seq no in this case are as if all dates were generated
    --             DECLARE @Date DATETIME
    --             SELECT @Date = GETDATE()
    --             SELECT *
    --             FROM dbo.GenerateCalendar(@Date, 365)
    --             WHERE Last = 1 ORDER BY SeqNo;
    RETURNS TABLE WITH SCHEMABINDING AS
     RETURN
    --===== High speed code provided courtesy of SQL MVP Jeff Moden (idea by Dwain Camps)
    --===== Generate sequence numbers from 1 to 65536 (credit to SQL MVP Itzik Ben-Gen)
       WITH  E1(N) AS (SELECT 1 UNION ALL SELECT 1), --2 rows
             E2(N) AS (SELECT 1 FROM E1 a, E1 b),    --4 rows
             E4(N) AS (SELECT 1 FROM E2 a, E2 b),    --16 rows
             E8(N) AS (SELECT 1 FROM E4 a, E4 b),    --256 rows
            E16(N) AS (SELECT 1 FROM E8 a, E8 b),    --65536 rows
       cteTally(N) AS (
    SELECT TOP (ABS(@NoDays)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E16)
            -- [SeqNo]=Sequential day number (@FromDate always=1) forward or backwards
     SELECT [SeqNo]     = t.N,
            -- [Date]=Date (with 00:00:00.000 for the time component)                             
            [Date]      = dt.DT, 
            -- [Year]=Four digit year                                 
            [Year]      = dp.YY,
            -- [YrNN]=Two digit year                                   
            [YrNN]      = dp.YY % 100,
            -- [YYYYMM]=Integer YYYYMM (year * 100 + month)                             
            [YYYYMM]    = dp.YY * 100 + dp.MM,
            -- [BuddhaYr]=Year in Buddhist calendar                     
            [BuddhaYr]  = dp.YY + 543,
            -- [Month]=Month (as an INT)                            
            [Month]     = dp.MM,
            -- [Day]=Day (as an INT)                                  
            [Day]       = dp.DD,
            -- [WkDNo]=Week day number (based on @@DATEFIRST)                                   
            [WkDNo]     = DATEPART(dw,dt.DT),
            -- Next 3 columns dependent on language setting so may not work for non-English 
            -- [WkDName]=Full name of the week day, e.g., Monday, Tuesday, etc.                    
            [WkDName]   = CONVERT(NCHAR(9),dp.DW),
            -- [WkDName2]=Two characters for the week day, e.g., Mo, Tu, etc.                
            [WkDName2]  = CONVERT(NCHAR(2),dp.DW), 
            -- [WkDName3]=Three characters for the week day, e.g., Mon, Tue, etc.               
            [WkDName3]  = CONVERT(NCHAR(3),dp.DW), 
            -- [JulDay]=Julian day (day number of the year)               
            [JulDay]    = dp.DY,
            -- [JulWk]=Week number of the year                                   
            [JulWk]     = dp.DY/7+1,
            -- [WkNo]=Week number                               
            [WkNo]      = dp.DD/7+1,
            -- [Qtr]=Quarter number (of the year)                               
            [Qtr]       = DATEPART(qq,dt.Dt),                      
            -- [Last]=Number the weeks for the month in reverse     
            [Last]      = (DATEPART(dd,dp.LDtOfMo)-dp.DD)/7+1,
            -- [LdOfMo]=Last day of the month                 
            [LdOfMo]    = DATEPART(dd,dp.LDtOfMo),
            -- [LDtOfMo]=Last day of the month as a DATETIME
            [LDtOfMo]   = dp.LDtOfMo                               
       FROM cteTally t
      CROSS APPLY
      ( --=== Create the date
            SELECT DT = DATEADD(dd,(t.N-1)*SIGN(@NoDays),@FromDate)
      ) dt
      CROSS APPLY
      ( --=== Create the other parts from the date above using a "cCA"
        -- (Cascading CROSS APPLY (cCA), courtesy of Chris Morris)
            SELECT YY        = DATEPART(yy,dt.DT),
                    MM        = DATEPART(mm,dt.DT),
                    DD        = DATEPART(dd,dt.DT),
                    DW        = DATENAME(dw,dt.DT),
                    Dy        = DATEPART(dy,dt.DT),
                    LDtOfMo   = DATEADD(mm,DATEDIFF(mm,-1,dt.DT),-1)
      ) dp;
    GO

    Now I can use the Calendar function to generate just the dates I need, and summarize:

    DECLARE @StartDate DATE, @EndDate DATE, @NumDays INT;
    SELECT @StartDate = MIN(Eff_Date)
      ,@EndDate = MAX(Exp_Date)
      ,@NumDays = DATEDIFF(day,@StartDate,@EndDate) + 1
    FROM MyData;
    SELECT wks.[Year]
     , wks.JulWk
     , COUNT(*) AS CountPerWk
    FROM
    (SELECT 
      gc.[Year]
     -- ,gc.Day
      ,gc.JulWk
     -- ,gc.Month
      ,md.ID_NUM
     -- ,md.EFF_DATE
     -- ,md.EXP_DATE
    FROM MyData md
    OUTER APPLY SCRIDB.dbo.GenerateCalendar(@StartDate,@NumDays) gc
    WHERE gc.Date BETWEEN md.EFF_DATE AND md.EXP_DATE) wks
    GROUP BY wks.[Year]
      ,wks.JulWk
    ORDER BY wks.[Year]
      ,wks.JulWk;

     

    -- this section just gets the start/end points of the date range we need
    DECLARE @StartDate DATE, @EndDate DATE, @NumDays INT;
    SELECT @StartDate = MIN(Eff_Date)
      ,@EndDate = MAX(Exp_Date)
      ,@NumDays = DATEDIFF(day,@StartDate,@EndDate) + 1
    FROM MyData;

    -- this returns the solution, using the calendar dates from above
    SELECT wks.[Year]
     , wks.JulWk
     , COUNT(*) AS CountPerWk
    FROM
    (SELECT 
      gc.[Year]
     -- ,gc.Day
      ,gc.JulWk
     -- ,gc.Month
      ,md.ID_NUM
     -- ,md.EFF_DATE
     -- ,md.EXP_DATE
    FROM MyData md
    OUTER APPLY SCRIDB.dbo.GenerateCalendar(@StartDate,@NumDays) gc
    WHERE gc.Date BETWEEN md.EFF_DATE AND md.EXP_DATE) wks
    GROUP BY wks.[Year]
      ,wks.JulWk
    ORDER BY wks.[Year]
      ,wks.JulWk;

    Result:

    2019 18 5

    2019 19 7

    2019 20 7

    2019 21 13

    2019 22 16

    2019 23 21

    2019 24 20

    2019 25 14

    2019 26 14

    2019 27 14

    2019 28 14

    2019 29 14

    2019 30 14

    2019 31 14

    2019 32 14

    2019 33 14

    2019 34 14

    2019 35 14

    2019 36 14

    2019 37 14

    2019 38 14

    2019 39 14

    2019 40 14

    2019 41 14

    2019 42 14

    2019 43 14

    2019 44 14

    2019 45 14

    2019 46 14

    2019 47 14

    2019 48 14

    2019 49 14

    2019 50 14

    2019 51 14

    2019 52 14

    2019 53 4

    2020 1 7

    2020 2 7

    2020 3 7

    2020 4 7

    2020 5 7

    2020 6 7

    2020 7 7

    2020 8 7

    2020 9 7

    2020 10 7

    2020 11 7

    2020 12 7

    2020 13 7

    2020 14 7

    2020 15 7

    2020 16 7

    2020 17 7

    2020 18 7

    2020 19 7

    2020 20 7

    2020 21 3

    • This reply was modified 3 years, 12 months ago by  pietlinden.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply