May 19, 2020 at 2:56 pm
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
May 19, 2020 at 4:21 pm
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?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 19, 2020 at 4:31 pm
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 🙂
May 19, 2020 at 6:31 pm
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;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
May 19, 2020 at 7:25 pm
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).
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 19, 2020 at 8:14 pm
Nicely done! It looks cleaned up now
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
May 19, 2020 at 9:18 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply