June 13, 2016 at 1:09 am
Hello Everyone,
I need some help in setting up a Calendar Table or an Staging Table that I can use for running some reports.
I'm given a Start Date and an End Date and I'm trying to return a table that has weeks with Start Date and End Date within the two given dates, the weeks starts on Sundays and ends on Mondays.
After doing research on the web I found the following SQL query that gives me the starting point by separating the weeks out with Start Dates (Sundays) however I also need the end dates (Mondays).
Please help me get the desired results.
Thank you,
Below is the code I'm using:
DECLARE @STARTDATE DATETIME,
@ENDDATE DATETIME
SET @STARTDATE ='06/05/2016'
SET @ENDDATE = '07/02/2016'
;
WITH CTE_DatesTable
AS
(
SELECT CAST(@STARTDATE as datetime) AS [date]
UNION ALL
SELECT DATEADD(WEEK, 1, [date])
FROM CTE_DatesTable
WHERE DATEADD(WEEK, 1, [date]) <= @ENDDATE
)
SELECT [date] FROM CTE_DatesTable
OPTION (MAXRECURSION 0);
Current Results
STARTDATE
2016-06-05 00:00:00.000
2016-06-12 00:00:00.000
2016-06-19 00:00:00.000
2016-06-26 00:00:00.000
Desired Results
STARTDATE ENDDATE WEEK
2016-06-05 00:00:00.000 2016-06-11 00:00:00.000 1
2016-06-12 00:00:00.000 2016-06-18 00:00:00.000 2
2016-06-19 00:00:00.000 2016-06-25 00:00:00.000 3
2016-06-26 00:00:00.000 2016-07-02 00:00:00.000 4
June 13, 2016 at 3:53 am
Quick suggestion: you can add 6 days and you will get the end date as well.
SELECT [date] as StartDate, DATEADD(dd,6,[date]) as EndDate
FROM CTE_DatesTable
OPTION (MAXRECURSION 0);
June 13, 2016 at 6:58 am
twin.devil (6/13/2016)
Quick suggestion: you can add 6 days and you will get the end date as well.
SELECT [date] as StartDate, DATEADD(dd,6,[date]) as EndDate
FROM CTE_DatesTable
OPTION (MAXRECURSION 0);
twin.devel nailed it, and you can simply add a row_number function to generate your week.
SELECT
[date] as StartDate,
DATEADD(dd,6,[date]) as EndDate,
row_number() over(ORDER BY [date]) AS [Week]
FROM CTE_DatesTable
OPTION (MAXRECURSION 0);
Lowell
June 13, 2016 at 10:27 am
For a work-table free, sort-free, purely set-based way of handling this you could use getnumsAB[/url] and do this:
DECLARE @STARTDATE DATETIME = '06/05/2016',
@ENDDATE DATETIME = '07/02/2016';
SELECT STARTDATE = DATEADD(DAY,n1,@STARTDATE),
ENDDATE = DATEADD(DAY,n2,@STARTDATE) - 1,
[WEEK] = rn
FROM GetNumsAB(0, DATEDIFF(DAY,@STARTDATE,@ENDDATE), 7, 1);
Edit: further simplified the solution.
-- Itzik Ben-Gan 2001
June 13, 2016 at 10:53 am
DiabloSlayer (6/13/2016)
Hello Everyone,I need some help in setting up a Calendar Table or an Staging Table that I can use for running some reports.
I'm given a Start Date and an End Date and I'm trying to return a table that has weeks with Start Date and End Date within the two given dates, the weeks starts on Sundays and ends on Mondays.
After doing research on the web I found the following SQL query that gives me the starting point by separating the weeks out with Start Dates (Sundays) however I also need the end dates (Mondays).
Please help me get the desired results.
Thank you,
Below is the code I'm using:
DECLARE @STARTDATE DATETIME,
@ENDDATE DATETIME
SET @STARTDATE ='06/05/2016'
SET @ENDDATE = '07/02/2016'
;
WITH CTE_DatesTable
AS
(
SELECT CAST(@STARTDATE as datetime) AS [date]
UNION ALL
SELECT DATEADD(WEEK, 1, [date])
FROM CTE_DatesTable
WHERE DATEADD(WEEK, 1, [date]) <= @ENDDATE
)
SELECT [date] FROM CTE_DatesTable
OPTION (MAXRECURSION 0);
Current Results
STARTDATE
2016-06-05 00:00:00.000
2016-06-12 00:00:00.000
2016-06-19 00:00:00.000
2016-06-26 00:00:00.000
Desired Results
STARTDATE ENDDATE WEEK
2016-06-05 00:00:00.000 2016-06-11 00:00:00.000 1
2016-06-12 00:00:00.000 2016-06-18 00:00:00.000 2
2016-06-19 00:00:00.000 2016-06-25 00:00:00.000 3
2016-06-26 00:00:00.000 2016-07-02 00:00:00.000 4
Last query is my take. eliminates the recursive counting which will not scale well over larger values.
D E C L A R E -- spaced to permit posting from current location
@STARTDATE DATETIME,
@ENDDATE DATETIME;
SET @STARTDATE ='06/05/2016';
SET @ENDDATE = '07/02/2016';
WITH CTE_DatesTable
AS
(
SELECT CAST(@STARTDATE as datetime) AS [date]
UNION ALL
SELECT DATEADD(WEEK, 1, [date])
FROM CTE_DatesTable
WHERE DATEADD(WEEK, 1, [date]) <= @ENDDATE
)
SELECT [date] FROM CTE_DatesTable
OPTION (MAXRECURSION 0);
with
e1(n) as (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
e2(n) as (select 1 from e1 a cross join e1 b),
e4(n) as (select 1 from e2 a cross join e2 b),
eTally(n) as (select top(datediff(week,@STARTDATE,@ENDDATE) + 1) rn = row_number() over (order by (select null)) - 1 from e4)
select
dateadd(week,n,@STARTDATE) STARTDATE,
dateadd(day,-1,dateadd(week,n + 1,@STARTDATE)) ENDDATE,
n + 1 [WEEK]
from
eTally;
June 13, 2016 at 11:13 am
Thanks to both twin.devil and Lowell for the quick solution, that's exactly the output I was looking for and presented to the client however ..
The client now added a twist to the requirements this morning by saying that the Start Date should be from beginning of the month till the end of the month, for instance for June it would be from 6/1 thru 6/30.
In this case the SQL query that I have will not work to get the right dates because:
If we chose 6/1 as the Start Date which is Wednesday then it goes from 6/1 to 6/7 etc. whereas it should go from Wednesday till Saturday (6/1 to 6/4) and then next row should be from Sunday to Saturday again which would be from 6/5 to 6/11, 6/12 to 6/18 etc., and the last row should be 6/26 to 6/30 because the End Date is at 6/30.
Thank you again for all the help !
DECLARE @STARTDATE DATETIME,
@ENDDATE DATETIME
SET @STARTDATE ='06/01/2016'
SET @ENDDATE = '06/30/2016'
;
WITH CTE_DatesTable
AS
(
SELECT CAST(@STARTDATE as datetime) AS [date]
UNION ALL
SELECT DATEADD(WEEK, 1, [date])
FROM CTE_DatesTable
WHERE DATEADD(WEEK, 1, [date]) <= @ENDDATE
)
SELECT
[date] as StartDate,
DATEADD(dd,6,[date]) as EndDate,
row_number() over(ORDER BY [date]) AS [Week]
FROM CTE_DatesTable
OPTION (MAXRECURSION 0);
STARTDATE ENDDATE WEEK
2016-06-01 00:00:00.000 2016-06-07 00:00:00.000 1
2016-06-08 00:00:00.000 2016-06-14 00:00:00.000 2
2016-06-15 00:00:00.000 2016-06-21 00:00:00.000 3
2016-06-22 00:00:00.000 2016-06-28 00:00:00.000 4
2016-06-29 00:00:00.000 2016-07-05 00:00:00.000 5
STARTDATE ENDDATE WEEK
2016-06-01 00:00:00.000 2016-06-04 00:00:00.000 1
2016-06-05 00:00:00.000 2016-06-11 00:00:00.000 2
2016-06-12 00:00:00.000 2016-06-18 00:00:00.000 3
2016-06-19 00:00:00.000 2016-06-25 00:00:00.000 4
2016-06-26 00:00:00.000 2016-06-30 00:00:00.000 5
June 13, 2016 at 11:35 am
DiabloSlayer (6/13/2016)
Thanks to both twin.devil and Lowell for the quick solution, that's exactly the output I was looking for and presented to the client however ..The client now added a twist to the requirements this morning by saying that the Start Date should be from beginning of the month till the end of the month, for instance for June it would be from 6/1 thru 6/30.
In this case the SQL query that I have will not work to get the right dates because:
If we chose 6/1 as the Start Date which is Wednesday then it goes from 6/1 to 6/7 etc. whereas it should go from Wednesday till Saturday (6/1 to 6/4) and then next row should be from Sunday to Saturday again which would be from 6/5 to 6/11, 6/12 to 6/18 etc., and the last row should be 6/26 to 6/30 because the End Date is at 6/30.
Thank you again for all the help !
DECLARE @STARTDATE DATETIME,
@ENDDATE DATETIME
SET @STARTDATE ='06/01/2016'
SET @ENDDATE = '06/30/2016'
;
WITH CTE_DatesTable
AS
(
SELECT CAST(@STARTDATE as datetime) AS [date]
UNION ALL
SELECT DATEADD(WEEK, 1, [date])
FROM CTE_DatesTable
WHERE DATEADD(WEEK, 1, [date]) <= @ENDDATE
)
SELECT
[date] as StartDate,
DATEADD(dd,6,[date]) as EndDate,
row_number() over(ORDER BY [date]) AS [Week]
FROM CTE_DatesTable
OPTION (MAXRECURSION 0);
STARTDATE ENDDATE WEEK
2016-06-01 00:00:00.000 2016-06-07 00:00:00.000 1
2016-06-08 00:00:00.000 2016-06-14 00:00:00.000 2
2016-06-15 00:00:00.000 2016-06-21 00:00:00.000 3
2016-06-22 00:00:00.000 2016-06-28 00:00:00.000 4
2016-06-29 00:00:00.000 2016-07-05 00:00:00.000 5
STARTDATE ENDDATE WEEK
2016-06-01 00:00:00.000 2016-06-04 00:00:00.000 1
2016-06-05 00:00:00.000 2016-06-11 00:00:00.000 2
2016-06-12 00:00:00.000 2016-06-18 00:00:00.000 3
2016-06-19 00:00:00.000 2016-06-25 00:00:00.000 4
2016-06-26 00:00:00.000 2016-06-30 00:00:00.000 5
With changes to my code:
D E C L A R E -- spaced to permit posting from current location
@STARTDATE DATETIME,
@ENDDATE DATETIME;
SET @STARTDATE ='06/01/2016';
SET @ENDDATE = '06/30/2016';
WITH CTE_DatesTable
AS
(
SELECT CAST(@STARTDATE as datetime) AS [date]
UNION ALL
SELECT DATEADD(WEEK, 1, [date])
FROM CTE_DatesTable
WHERE DATEADD(WEEK, 1, [date]) <= @ENDDATE
)
SELECT [date] FROM CTE_DatesTable
OPTION (MAXRECURSION 0);
with
e1(n) as (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
e2(n) as (select 1 from e1 a cross join e1 b),
e4(n) as (select 1 from e2 a cross join e2 b),
eTally(n) as (select top(datediff(week,@STARTDATE,@ENDDATE) + 1) rn = row_number() over (order by (select null)) - 1 from e4)
select
--dateadd(week,n,@STARTDATE) STARTDATE,
--dateadd(day,-1,dateadd(week,n + 1,@STARTDATE)) ENDDATE,
case when dateadd(day,-1,dateadd(week,datediff(week,0,dateadd(week,n,@STARTDATE)),0)) >= @STARTDATE then dateadd(day,-1,dateadd(week,datediff(week,0,dateadd(week,n,@STARTDATE)),0)) else @STARTDATE end StartDate,
case when dateadd(day,-2,dateadd(week,datediff(week,0,dateadd(week,n + 1,@STARTDATE)),0)) <= @ENDDATE then dateadd(day,-2,dateadd(week,datediff(week,0,dateadd(week,n + 1,@STARTDATE)),0)) else @ENDDATE end EndDate,
n + 1 [WEEK]
from eTally;
June 13, 2016 at 5:14 pm
DiabloSlayer (6/13/2016)
If we chose 6/1 as the Start Date which is Wednesday then it goes from 6/1 to 6/7 etc. whereas it should go from Wednesday till Saturday (6/1 to 6/4) and then next row should be from Sunday to Saturday again which would be from 6/5 to 6/11, 6/12 to 6/18 etc., and the last row should be 6/26 to 6/30 because the End Date is at 6/30.
How should this logic work for StartDate = '01/01/2017'?
Hint - it's Sunday. And it's 1st of the month.
_____________
Code for TallyGenerator
June 13, 2016 at 5:35 pm
Hi Sergiy,
In this case, it would be like the following:
STARTDATE ENDDATE WEEK
2017-01-01 00:00:00.000 2017-01-07 00:00:00.000 1
2017-01-08 00:00:00.000 2017-01-14 00:00:00.000 2
2017-01-15 00:00:00.000 2017-01-21 00:00:00.000 3
2017-01-22 00:00:00.000 2017-01-28 00:00:00.000 4
2017-01-29 00:00:00.000 2017-01-31 00:00:00.000 5
June 13, 2016 at 6:51 pm
DiabloSlayer (6/13/2016)
Hi Sergiy,In this case, it would be like the following:
STARTDATE ENDDATE WEEK
2017-01-01 00:00:00.000 2017-01-07 00:00:00.000 1
2017-01-08 00:00:00.000 2017-01-14 00:00:00.000 2
2017-01-15 00:00:00.000 2017-01-21 00:00:00.000 3
2017-01-22 00:00:00.000 2017-01-28 00:00:00.000 4
2017-01-29 00:00:00.000 2017-01-31 00:00:00.000 5
Ah, I see.
Sorry, misread the requirements at the first time.
I believe everything will be much simpler if you use static Calendar table - populated with all days you will possibly deal with.
Then the query which you actually need for reports will look like this:
DECLARE @StartDate DATETIME
SET @StartDate = '20160601'
SELECT bc.Date, DATENAME(dw, bc.Date),
DATEDIFF(dd, 6, bc.Date)/7 - DATEDIFF(dd, 6, @StartDate)/7 +1 WeekNo
FROM dbo.BusinessCalendar bc
WHERE bc.Date >= @StartDate
AND bc.Date < DATEADD(mm, 1, @StartDate)
If you want to see STARTDATE and ENDDATE for every week just run the aggregation on top of this query:
SELECT @StartDate [@StartDate],
WeekNo, MIN(Date) FromDate, MAX(Date) ToDate
FROM (
SELECT bc.Date, DATENAME(dw, bc.Date) WeekDay,
DATEDIFF(dd, 6, bc.Date)/7 - DATEDIFF(dd, 6, @StartDate)/7 +1 WeekNo
FROM dbo.BusinessCalendar bc
WHERE bc.Date >= @StartDate
AND bc.Date < DATEADD(mm, 1, @StartDate)
) DT
GROUP BY WeekNo
_____________
Code for TallyGenerator
June 13, 2016 at 7:16 pm
You can even have a query for all the dates ever:
SELECT DATENAME(MONTH, MonthStart) + ' ' + DATENAME(YY, DT.MonthStart) ReportMonth,
WeekNo, MIN(Date) FromDate, MAX(Date) ToDate
FROM (
SELECT DATEADD(mm, DATEDIFF(mm, 0, bc.Date), 0) MonthStart,
bc.Date,
DATENAME(dw, bc.Date) WeekDay,
DATEDIFF(dd, 6, bc.Date)/7 - DATEDIFF(dd, 6, DATEADD(mm, DATEDIFF(mm, 0, bc.Date), 0))/7 +1 WeekNo
FROM dbo.BusinessCalendar bc
) DT
GROUP BY MonthStart, WeekNo
ORDER BY MonthStart, WeekNo
Not @StartDate required.
It will show correct Month and WeekInMonth for all the dates included in your report.
Another advantage of static Calendar table - it may have all the holidays accounted, so you don't need to figure out the Easters schedule every time you run a report.
It's especially valuable when you have different holidays for different locations.
_____________
Code for TallyGenerator
June 13, 2016 at 7:28 pm
Hi Sergiy,
Do I need to create the BusinessCalendar table first?
I'm getting an unknown invalid object name error.
Thank you.
June 13, 2016 at 7:50 pm
Yes, of course.
It's pretty much Numbers table, but instead of numbers it contains dates:
Select DATEADD (dd, Number, 0)
From Tally
Where Number < 256*256
It will cover full range of SMALLDATETIME dates
_____________
Code for TallyGenerator
June 14, 2016 at 1:22 am
Hi Lynn Pettis,
That's exactly the results I need so THANK YOU very much !!
But... I don't know two things, so if you could please help me 🙂
1. How do I remove the first result from your query as shown below?
I mean, when I run the following query I get 'dates' result and the StartDate, EndDate and Week result. I just need the second three column result so I can insert it into my staging table.
2. Where can I learn more about what exactly your query is doing?
Thank you very much..
DECLARE
@STARTDATE DATETIME,
@ENDDATE DATETIME;
SET @STARTDATE ='06/01/2016';
SET @ENDDATE = '06/30/2016';
WITH CTE_DatesTable
AS
(
SELECT CAST(@STARTDATE as datetime) AS [date]
UNION ALL
SELECT DATEADD(WEEK, 1, [date])
FROM CTE_DatesTable
WHERE DATEADD(WEEK, 1, [date]) <= @ENDDATE
)
SELECT [date] FROM CTE_DatesTable
OPTION (MAXRECURSION 0);
with
e1(n) as (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
e2(n) as (select 1 from e1 a cross join e1 b),
e4(n) as (select 1 from e2 a cross join e2 b),
eTally(n) as (select top(datediff(week,@STARTDATE,@ENDDATE) + 1) rn = row_number() over (order by (select null)) - 1 from e4)
select
--dateadd(week,n,@STARTDATE) STARTDATE,
--dateadd(day,-1,dateadd(week,n + 1,@STARTDATE)) ENDDATE,
case when dateadd(day,-1,dateadd(week,datediff(week,0,dateadd(week,n,@STARTDATE)),0)) >= @STARTDATE then dateadd(day,-1,dateadd(week,datediff(week,0,dateadd(week,n,@STARTDATE)),0)) else @STARTDATE end StartDate,
case when dateadd(day,-2,dateadd(week,datediff(week,0,dateadd(week,n + 1,@STARTDATE)),0)) <= @ENDDATE then dateadd(day,-2,dateadd(week,datediff(week,0,dateadd(week,n + 1,@STARTDATE)),0)) else @ENDDATE end EndDate,
n + 1 [WEEK]
from eTally;
June 14, 2016 at 1:24 am
Hi Sergiy,
I'm sorry I'm lost 😉
Can you kindly add the initial piece (where we create the Calendar table) into your query?
Sorry and thanks again for your help.
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply