calc/group data by week for date range ?

  • jbalbo

    SSCertifiable

    Points: 7456

    Hi

    Not sure if I will explain this correctly....

    I need to calc/ get data  within the "week" for a given range.

    So lets say the parameter start and end date is 4/1/20 thru 4/30/20 each week is Sunday thru Saturday

    So my weeks for this would be

    3/29/20 - 4/4/20

    4/5/20 - 4/11/20

    4/12/20 - 4/18/20

    4/19/20 - 4/25/20

    4/26/20 - 5/2/20

    so in this case I would get/calc 5 values

    first would be select data from table where date between 3/29/20 - 4/4/20

    second  would be select data from table where date between  4/5/20 - 4/11/20

    third etc...

    the date range would not always be one full month could be two weeks, could be 12 each week would be Sunday thru Saturday

    any ideas on getting started, or just processing the data by weeks would be great.

     

    Thanks and hoping it's not too confusing

     

     

     

  • Mr. Brian Gale

    SSC-Insane

    Points: 23059

    Just to get a bit more info with this - you want to give a date range, and you want SQL to figure out how to break that up into 5 groups, right?

    So I think first you are going  to need to calculate the start date back to Sunday with something like:

    SELECT DATEADD(wk, DATEDIFF(wk,0,@startDate), -1)

    NOTE - that assumes SQL is configured to have MONDAY as the start date.  If it is configured to be SUNDAY, you don't need that -1 and can change that to 0.  If you run that for today's date (May 19th) it should you the 17th.  That is your REAL start date.

    Do a similar calculation for end date but change the -1 to +5 (or 5).

    So at this point, you have the real start and real end dates.  Next, you need to figure out the number of weeks between the 2.  Since each row is a week, I'd probably use a loop of some sort (cursor, while, etc... may be better options but I think this is the easiest to work with and will maintain readablility).  Have your loop start on "RealStart", store that date in a WeekStart variable and do a dateadd of 7 to get the WeekEnd.  Then loop on the WeekStart until WeekStart > RealEnd at which point you no longer need the date.  Dump the results into a temp table and select from the temp table at the very end.

    Remember to close and deallocate your cursor when you are done (if you use a cursor) and to make the cursor LOCAL and FAST_FORWARD (again, if you use a cursor).

     

    Does the above  help?

  • jbalbo

    SSCertifiable

    Points: 7456

    It definitely gives me a starting point to start working

    I have actually never use cursor or a while(in sql anyway) yet. But will look into it

    It for the most part with be the full weeks for the prior month, but have the option for anytime period.

    Thank you and I'l be sure to be back with questions 🙂

     

     

     

     

  • Steve Collins

    SSC Eights!

    Points: 884

    No looping necessary.  Check out this script:

    https://www.sqlservercentral.com/scripts/a-daterange-table-valued-function

    It generates any range of days (or seconds or years...)

    declare
    @start_day_num int=5, /* 0 = Monday, 1 = Tuesday, etc. */
    @start_dt date='2020-05-10',
    @end_dt date='2020-06-26';

    select distinct
    dateadd(dd, @start_day_num-(@@datefirst+5+datepart(dw, d.[value]))%7, cast(d.[value] as date)) start_dt,
    dateadd(dd, (6+@start_day_num)-(@@datefirst+5+datepart(dw, d.[value]))%7, cast(d.[value] as date)) end_dt
    from
    dbo.daterange(@start_dt, @end_dt, 'dd', 1) d;

     

  • Mr. Brian Gale

    SSC-Insane

    Points: 23059

    Thanks scdecate!  That is a bit more elegant of a solution than what I was proposing.

    Seems to have a bug though - it returns the week of the 27th through 3rd even though the end date is set to the 26th.  If the end date is between the 22nd and 26th it seems to return an extra row.  Also, if the start date is the 8th, that doesn't seem to get pulled in that row.

    Looking at it, I think it is because

    Revised version of scdecade's code (not trying to steal thunder, just fixing some logic):

    DECLARE
    @start_day_num int=-1, /* 0 = Monday, 1 = Tuesday, etc. Negative numbers mean go to previous so -1 would be previous Sunday */
    @start_dt date='2020-05-08',
    @end_dt date='2020-06-22';

    select distinct
    CAST(DATEADD(wk, DATEDIFF(wk,0,d.value), @start_day_num) AS DATE) AS start_dt,
    CAST(DATEADD(wk,DATEDIFF(wk,0,d.value), @start_day_num + 6) AS DATE) AS end_dt
    from
    dbo.daterange(@start_dt, @end_dt, 'dd', 1) d;

    Changed the start_dt and end_dt calculations.  End date is start date + 6.  So if start date is previous Sunday (ie -1), then end date should be following Saturday (ie +5).

  • Steve Collins

    SSC Eights!

    Points: 884

    Nicely done!  It looks cleaned up now

  • Jeffrey Williams

    SSC Guru

    Points: 88595

    You can do this without the function:

    Declare @startDate date = '2020-05-08'
    , @endDate date = '2020-06-22';

    Set @startDate = dateadd(day, -datediff(day, -1, @startDate) % 7, @startDate);

    With t(n)
    As (
    Select t.n
    From (
    Values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) As t(n)
    )
    , iTally (n)
    As (
    Select Top (datediff(week, @startDate, @endDate) + 1)
    checksum(row_number() over(Order By @@spid) - 1)
    From t t1, t t2
    )
    Select *
    From iTally it
    Cross Apply (Values (dateadd(day, it.n * 7, @startDate))) sd(StartDate)
    Cross Apply (Values (dateadd(day, 6, sd.StartDate))) ed(EndDate);

    This generates the start and end dates for each week from Sunday through Saturday.  To get Monday through Sunday - change this:

     Set @startDate = dateadd(day, -datediff(day, -1, @startDate) % 7, @startDate);

    To this:

     Set @startDate = dateadd(day, -datediff(day, 0, @startDate) % 7, @startDate);

    This will work for up to 100 weeks - if you need more it can be increased to return more weeks.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 7 posts - 1 through 7 (of 7 total)

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