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_dtfrom  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_dtfrom  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.

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