June 14, 2016 at 9:13 am
DiabloSlayer (6/14/2016)
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;
You are running your query and mine, just don't run yours:
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 9:20 am
As for how my query works, start with the following:
http://www.sqlservercentral.com/articles/T-SQL/62867/
http://www.sqlservercentral.com/articles/Tally+Table/72993/
The rest, play with the date functions. Understand that the zero (0) in the datediff function represents 1900-01-01 that is also known as the zero (0) date. You will also find some basic date manipulation routines here:
http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/
June 14, 2016 at 10:41 am
Hi Lynn Pettis,
Got it :w00t:
This is it then, these are the results I was after so thank you once again !!
Have a blessed day 🙂
June 14, 2016 at 10:45 am
Hi Lynn Pettis,
This is going to take some time to digest as there is a lot to learn from these articles.
I thank you for directing me to these reads and I'm hoping to write my full queries in the future using what I learn from the experts 🙂
Thank you !!
June 14, 2016 at 11:34 am
If you want to make the performance even better you can create a table that stores the required start/end dates and write a function uses it. In the example below the table will only require a couple kb of space and the performance will be instantaneous.
USE tempdb -- a safe db for testing
GO
-- (1) CREATE THE TABLE TO STORE YOUR REQUIRED VALUES
IF OBJECT_ID('tempdb.dbo.Dates') IS NOT NULL DROP TABLE dbo.Dates;
CREATE TABLE dbo.Dates
(
STARTDATE date primary key,
ENDDATE date not null,
[WEEK] tinyint CHECK(WEEK < 7)
);
-- (2) MAKE SURE THAT YOUR DATE RANGE COVERS YOUR NEEDS
DECLARE @STARTDATE date = '20150101', @ENDDATE date = '20200101';
-- POPULATE THE TABLE
INSERT dbo.Dates
SELECT
STARTDATE = CalDate,
ENDDATE = CASE CalMoNbr WHEN MONTH(EndOfWk) THEN EndOfWk ELSE EndOfMo END,
WEEKNO = WeekOfMo
FROM
(
SELECT
CalDate,
EndOfMo = MAX(CalDate) OVER (PARTITION BY CalYrNbr, CalMoNbr),
EndOfWk = DATEADD(DAY,7-DATEPART(WEEKDAY,CalDate),CalDate),
CalMoNbr,
WeekOfMo = DENSE_RANK() OVER (PARTITION BY CalYrNbr, CalMoNbr ORDER BY CalYrNbr, CalMoNbr, WeekNbr)
FROM
(
SELECT
CalDate = DATEADD(DAY,n1,@STARTDATE),
CalYrNbr = DATEPART(YEAR,DATEADD(DAY,n1,@STARTDATE)),
CalMoNbr = DATEPART(MONTH,DATEADD(DAY,n1,@STARTDATE)),
WeekNbr = DATEPART(WEEK,DATEADD(DAY,n1,@STARTDATE))
FROM
(
SELECT TOP(DATEDIFF(DAY,@STARTDATE,@ENDDATE))
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1
FROM sys.all_columns a, sys.all_columns
) iTally(n1)
) dt
) dt
WHERE DATEPART(WEEKDAY,Caldate) = 1 OR DAY(CalDate) = 1 --ORDER BY CalDate;
GO
-- (3) CREATE THE FUNCTION
CREATE FUNCTION dbo.GetWeeks (@STARTDATE date, @ENDDATE date)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT STARTDATE, ENDDATE, [WEEK]
FROM dbo.Dates
WHERE STARTDATE >= @STARTDATE AND STARTDATE <= @ENDDATE;
GO
-- Example of how to use the function
DECLARE @STARTDATE date = '20160601', @ENDDATE date = '20160630';
SELECT * FROM dbo.GetWeeks(@STARTDATE, @ENDDATE);
The important thing to note from this thread is a recursive CTE (the original solution) will be the slowest. A tally table-based solution will be much faster (like what Lynn and I put together). A Calendar table approach will be even faster. The solution I just put together will be the fastest and will require less space than a calendar table (though I do recommend having a calendar table available).
-- Itzik Ben-Gan 2001
June 14, 2016 at 5:37 pm
DiabloSlayer (6/14/2016)
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.
Here is the simplest version of the table:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[BusinessCalendar](
[Date] [SMALLDATETIME] NOT NULL,
[isWeekDay] [BIT] NOT NULL DEFAULT (1),
[isWorkDay] [BIT] NOT NULL DEFAULT (1),
PRIMARY KEY CLUSTERED ([Date] ASC)
)
INSERT INTO dbo.BusinessCalendar ( Date )
SELECT DATEADD(dd, N, 0)
FROM dbo.Tally
where N between 0 and 256*256-1
UPDATE dbo.BusinessCalendar
SET [isWeekDay] = 0,
[isWorkDay] = 0
WHERE DATEPART(WEEKDAY,[Date])=7 OR DATEPART(WEEKDAY,[Date])=1
UPDATE dbo.BusinessCalendar
SET [isWorkDay] = 0
WHERE Date IN (/* put the list of your holidays in here*/ )
GO
dbo.Tally is a table, or a view, or an inline function containing sequential numbers from 0 to whatever.
I'm pretty sure Google is well aware of it.
Instead of a static object you may wish to use a CTE which was posted even within this thread.
It will do all right.
Later on you may wish to add more columns to this table, e.g. "DayOfYear", "MonthNumber", "FirstWeekDayOfMonth", "LastWeekDayOfMonth" (for querying "last Friday of the month", etc.
Since the table is absolutely static, no data would be changed ever, it's OK to put an index on every column of it.
You may wish to put it on a separate "DBA" database on every server in your environments(s), so it may be shared between all the application databases created on the server(s).
_____________
Code for TallyGenerator
June 15, 2016 at 12:34 am
Hi Alan.B,
This is great and works really well 🙂
Thank you for this solution !!
June 15, 2016 at 12:37 am
Hi Sergiy,
I really appreciate all of the help from you and the rest of the awesome group !!
I have three solutions now and I just have to pick the one that works best and is within my understanding reach as I will have to write up a document explaining the solution I choose to use.
Thank you again 🙂
June 26, 2016 at 12:38 am
Hi Alan Burstein,
I ended up using your solution so thank you VERY MUCH 🙂
I just have two questions for you, first, how can I change the start date to start from Saturday instead of Sunday and also the End Date would be Friday instead of the Saturday.
Secondly, can you point me where I can learn the contents in your script so I can decipher it and learn it for the future?
Thank you once again and God Bless !!
June 30, 2016 at 8:50 am
DiabloSlayer (6/26/2016)
Hi Alan Burstein,I ended up using your solution so thank you VERY MUCH 🙂
I just have two questions for you, first, how can I change the start date to start from Saturday instead of Sunday and also the End Date would be Friday instead of the Saturday.
Secondly, can you point me where I can learn the contents in your script so I can decipher it and learn it for the future?
Thank you once again and God Bless !!
Sorry for the delay - I forgot about this post until this morning... I will try to work this out today.
Quick update - here's an updated query with a the start and end date changes you asked for. This can be improved but, since you only need to run this once, it should be fine for now...
DECLARE @STARTDATE date = '20150101', @ENDDATE date = '20200101';
SELECT
STARTDATE,
ENDDATE =
CASE
WHEN STARTDATE > ENDDATE
THEN
CASE
WHEN MONTH(STARTDATE) <> MONTH(DATEADD(DAY,7,ENDDATE))
THEN EndOfMo
ELSE DATEADD(DAY,7,ENDDATE)
END
ELSE ENDDATE
END,
WEEKNO,
-- These are not required, including these for display
STARTDATENAME = DATENAME(WEEKDAY,STARTDATE),
ENDDATENAME = DATENAME
(
WEEKDAY,
CASE
WHEN STARTDATE > ENDDATE
THEN
CASE
WHEN MONTH(STARTDATE) <> MONTH(DATEADD(DAY,7,ENDDATE))
THEN EndOfMo
ELSE DATEADD(DAY,7,ENDDATE)
END
ELSE ENDDATE
END
)
FROM
(
SELECT
STARTDATE = CalDate,
ENDDATE = CASE CalMoNbr WHEN MONTH(EndOfWk) THEN EndOfWk ELSE EndOfMo END,
WEEKNO = WeekOfMo,
EndOfMo
FROM
(
SELECT
CalDate,
EndOfMo = MAX(CalDate) OVER (PARTITION BY CalYrNbr, CalMoNbr),
EndOfWk = DATEADD(DAY,6-DATEPART(WEEKDAY,CalDate),CalDate),
CalMoNbr,
WeekOfMo = DENSE_RANK() OVER (PARTITION BY CalYrNbr, CalMoNbr ORDER BY CalYrNbr, CalMoNbr, WeekNbr)
FROM
(
SELECT
CalDate = DATEADD(DAY,n1,@STARTDATE),
CalYrNbr = DATEPART(YEAR,DATEADD(DAY,n1,@STARTDATE)),
CalMoNbr = DATEPART(MONTH,DATEADD(DAY,n1,@STARTDATE)),
WeekNbr = DATEPART(WEEK,DATEADD(DAY,n1,@STARTDATE))
FROM
(
SELECT TOP(DATEDIFF(DAY,@STARTDATE,@ENDDATE))
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1
FROM sys.all_columns a, sys.all_columns
) iTally(n1)
) dt
) dt
WHERE DATEPART(WEEKDAY,Caldate) = 7 OR DAY(CalDate) = 1 --ORDER BY CalDate;
) dt
GO
I'll chime back in later about the rest of your post.
-- Itzik Ben-Gan 2001
June 30, 2016 at 12:55 pm
Hi Alan,
Thank you for the update and help !!
So I ran the update script for the period of 05/01/2016 to 05/31/2016 and I'm not sure why I'm getting two Week #1.
Here is what I'm getting.
STARTDATE ENDDATE WEEK STARTDATENAME ENDDATENAME
2016-05-01 2016-05-06 1 Sunday Friday
2016-05-07 2016-05-13 1 Saturday Friday
2016-05-14 2016-05-20 2 Saturday Friday
2016-05-21 2016-05-27 3 Saturday Friday
2016-05-28 2016-05-30 4 Saturday Monday
Thanks again for all your help !!
June 30, 2016 at 1:51 pm
DiabloSlayer (6/30/2016)
Hi Alan,Thank you for the update and help !!
So I ran the update script for the period of 05/01/2016 to 05/31/2016 and I'm not sure why I'm getting two Week #1.
Here is what I'm getting.
STARTDATE ENDDATE WEEK STARTDATENAME ENDDATENAME
2016-05-01 2016-05-06 1 Sunday Friday
2016-05-07 2016-05-13 1 Saturday Friday
2016-05-14 2016-05-20 2 Saturday Friday
2016-05-21 2016-05-27 3 Saturday Friday
2016-05-28 2016-05-30 4 Saturday Monday
Thanks again for all your help !!
This should do the trick:
DECLARE @STARTDATE date = '20150101', @ENDDATE date = '20200101';
SELECT
STARTDATE,
ENDDATE =
CASE
WHEN STARTDATE > ENDDATE
THEN
CASE
WHEN MONTH(STARTDATE) <> MONTH(DATEADD(DAY,7,ENDDATE))
THEN EndOfMo
ELSE DATEADD(DAY,7,ENDDATE)
END
ELSE ENDDATE
END,
WEEKNO = DENSE_RANK() OVER (PARTITION BY CalYrNbr, CalMoNbr ORDER BY CalYrNbr, CalMoNbr, STARTDATE),
-- These are not required, including these for display
STARTDATENAME = DATENAME(WEEKDAY,STARTDATE),
ENDDATENAME = DATENAME
(
WEEKDAY,
CASE
WHEN STARTDATE > ENDDATE
THEN
CASE
WHEN MONTH(STARTDATE) <> MONTH(DATEADD(DAY,7,ENDDATE))
THEN EndOfMo
ELSE DATEADD(DAY,7,ENDDATE)
END
ELSE ENDDATE
END
)
FROM
(
SELECT
STARTDATE = CalDate,
ENDDATE = CASE CalMoNbr WHEN MONTH(EndOfWk) THEN EndOfWk ELSE EndOfMo END,
EndOfMo,
CalMoNbr,
CalYrNbr
FROM
(
SELECT
CalDate,
EndOfMo = MAX(CalDate) OVER (PARTITION BY CalYrNbr, CalMoNbr),
EndOfWk = DATEADD(DAY,6-DATEPART(WEEKDAY,CalDate),CalDate),
CalMoNbr,
CalYrNbr
--WeekOfMo = DENSE_RANK() OVER (PARTITION BY CalYrNbr, CalMoNbr ORDER BY CalYrNbr, CalMoNbr, WeekNbr)
FROM
(
SELECT
CalDate = DATEADD(DAY,n1,@STARTDATE),
CalYrNbr = DATEPART(YEAR,DATEADD(DAY,n1,@STARTDATE)),
CalMoNbr = DATEPART(MONTH,DATEADD(DAY,n1,@STARTDATE)),
WeekNbr = DATEPART(WEEK,DATEADD(DAY,n1,@STARTDATE))
FROM
(
SELECT TOP(DATEDIFF(DAY,@STARTDATE,@ENDDATE))
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1
FROM sys.all_columns a, sys.all_columns
) iTally(n1)
) dt
) dt
WHERE DATEPART(WEEKDAY,Caldate) = 7 OR DAY(CalDate) = 1 --ORDER BY CalDate;
) dt
GO
This has an error in the logic... I'll have a better solution in a moment.
-- Itzik Ben-Gan 2001
June 30, 2016 at 6:43 pm
Wow!
That's a big bunch of code!
Compare to this (using the table I've posted above):
DECLARE @StartDate SMALLDATETIME, @EndDate DATE, @WeekStartsFrom VARCHAR(50)
SET @StartDate = '20160501'
SET @ENDDATE = '20160531'
SET @WeekStartsFrom = 'Saturday'
SELECTMIN(Date) STARTDATE,
MAX(Date) ENDDATE,
DATEDIFF(dd, BaseDate, Date) /7 +1 [Week],
DATENAME(dw, MIN(date)) STARTDATENAME,
DATENAME(dw, MAX(date)) EndDATENAME
FROM dbo.BusinessCalendar
CROSS JOIN (SELECT TOP 1 Date BaseDate
FROM dbo.BusinessCalendar
WHERE date <= @StartDate
AND DATENAME(dw, Date) = @WeekStartsFrom
ORDER BY Date DESC
) WD
WHERE Date >= @StartDate
AND Date <= @EndDate
GROUP BY DATEDIFF(dd, BaseDate, Date) /7
order BY [Week]
_____________
Code for TallyGenerator
July 1, 2016 at 8:58 am
Sergiy (6/30/2016)
Wow!That's a big bunch of code!
Compare to this (using the table I've posted above):
DECLARE @StartDate SMALLDATETIME, @EndDate DATE, @WeekStartsFrom VARCHAR(50)
SET @StartDate = '20160501'
SET @ENDDATE = '20160531'
SET @WeekStartsFrom = 'Saturday'
SELECTMIN(Date) STARTDATE,
MAX(Date) ENDDATE,
DATEDIFF(dd, BaseDate, Date) /7 +1 [Week],
DATENAME(dw, MIN(date)) STARTDATENAME,
DATENAME(dw, MAX(date)) EndDATENAME
FROM dbo.BusinessCalendar
CROSS JOIN (SELECT TOP 1 Date BaseDate
FROM dbo.BusinessCalendar
WHERE date <= @StartDate
AND DATENAME(dw, Date) = @WeekStartsFrom
ORDER BY Date DESC
) WD
WHERE Date >= @StartDate
AND Date <= @EndDate
GROUP BY DATEDIFF(dd, BaseDate, Date) /7
order BY [Week]
Yep, your solution is faster and requires less code. I skipped over it. What I'm proposing, however, only needs to be run once so it doesn't really matter.
-- Itzik Ben-Gan 2001
July 1, 2016 at 9:32 am
DiabloSlayer (6/26/2016)
Hi Alan Burstein,...Secondly, can you point me where I can learn the contents in your script so I can decipher it and learn it for the future?
Ok, my final solution along with some explanation is below.
First, the key concepts to learn and understand here are:
1. Tally Tables[/url]. This is the most important concept here and learning about them will change your career.
2. Most of the solutions in this thread use a tally table to create a calendar table[/url]. Another hugely important topic.
3. Window Functions (such as ROW_NUMBER, DENSE_RANK, MAX() OVER ...) are used here - they're a hugely important thing to know about. Itzik Ben Gan has some great books and articles about these.
4. Date functions (such as DATEADD, DATEDIFF, DATEPART, etc) are used here these are important concepts to understand. You can learn more about these with a basic google search.
My solution from earlier in this thread has been updated (see below). The idea behind my solution is that: given the same two parameters, the answer will always be the same. Although what Sergiy and Lynn posted were super efficient - the answer is calculated every time. My thinking is, why not put the results into a table? This way the answer is calculated only once and you can Index. Nothing is calculated, you are instead retrieving your solution using nothing more than an index seek.
Here's the final solution with some comments:
-- (1) CREATE THE TABLE TO STORE YOUR REQUIRED VALUES
IF OBJECT_ID('dbo.Dates') IS NOT NULL DROP TABLE dbo.Dates;
CREATE TABLE dbo.Dates
(
STARTDATE date primary key,
ENDDATE date not null,
[WEEK] tinyint CHECK(WEEK < 7)
);
-- (2) MAKE SURE THAT YOUR DATE RANGE COVERS YOUR NEEDS
DECLARE @STARTDATE date = '20150101', @ENDDATE date = '20200101';
-- POPULATE THE TABLE
INSERT dbo.Dates
SELECT
-- 3. Outermost query which produces the result set
--Uncomment these out to better understand the solution:
--CalDate, CurrentMonth, CurrentYear, EndofWeek1, EndofWeek2, EndOfMo,
STARTDATE = CalDate, -- a. get STARTDATE
ENDDATE =
CASE
WHEN EndOfWeek1 >= CalDate THEN EndofWeek1
WHEN MONTH(EndOfWeek2) = CurrentMonth THEN EndofWeek2
ELSE EndOfMo
END,
WEEKNO = DENSE_RANK() OVER
(
PARTITION BY CurrentYear, CurrentMonth
ORDER BY CurrentYear, CurrentMonth, CalDate
)
FROM
( -- 2. Gets the dates, months, years, and "end of week" columns
SELECT
CalDate,
CurrentMonth = MONTH(CalDate),
CurrentYear = YEAR(CalDate),
EndofWeek1 = DATEADD(DAY, 6 - DATEPART(WEEKDAY,CalDate), CalDate),
EndofWeek2 = DATEADD(DAY, 13 - DATEPART(WEEKDAY,CalDate), CalDate),
EndOfMo = MAX(CalDate) OVER (PARTITION BY YEAR(CalDate), MONTH(CalDate))
FROM
( -- 1. Innermost query, uses an on-the-fly tally table to create an on-the-fly calendar table
SELECT TOP(DATEDIFF(DAY,@STARTDATE,@ENDDATE))
CalDate = DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1, @STARTDATE)
FROM sys.all_columns a, sys.all_columns
) calendarTable
) DateCalculations
WHERE DATEPART(WEEKDAY,CalDate) = 7 OR DAY(CalDate) = 1;
GO
-- (3) CREATE THE FUNCTION
CREATE FUNCTION dbo.GetWeeks (@STARTDATE date, @ENDDATE date)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT STARTDATE, ENDDATE, [WEEK]
FROM dbo.Dates
WHERE STARTDATE >= @STARTDATE AND STARTDATE <= @ENDDATE;
GO
-- Example of how to use the function (2 months worth, )
DECLARE @STARTDATE date = '20150601', @ENDDATE date = '20150730';
SELECT STARTDATE, ENDDATE, [WEEK]
-- Uncomment to see the weekdays:
--,STARTDATE_DAY = DATENAME(WEEKDAY,STARTDATE), ENDDATE_DAY = DATENAME(WEEKDAY,ENDDATE)
FROM dbo.GetWeeks(@STARTDATE, @ENDDATE);
And here's a brief explanation about the SELECT statement that populates the table. Starting with the innermost subquery...
1. We start with a tally table that starts at 0 and has enough rows to do what we need
DECLARE @STARTDATE date = '20150101', @ENDDATE date = '20200101';
SELECT TOP(DATEDIFF(DAY,@STARTDATE,@ENDDATE))
N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1
FROM sys.all_columns a, sys.all_columns;
N
--------------------
0
1
2
3
4
5
...
2. Next, we create an "on-the-fly" calendar table using this formula: DATEADD(DAY,N,@STARTDATE) which looks like this:
DECLARE @STARTDATE date = '20150101', @ENDDATE date = '20200101';
SELECT TOP(DATEDIFF(DAY,@STARTDATE,@ENDDATE))
CalDate = DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1, @STARTDATE)
FROM sys.all_columns a, sys.all_columns;
CalDate
----------
2015-01-01
2015-01-02
2015-01-03
2015-01-04
2015-01-05
3. Now we use the calendar table to get the the dates, months, years, and "end of week" and end of year columns. These will be used later.
DECLARE @STARTDATE date = '20150101', @ENDDATE date = '20200101';
SELECT
CalDate,
CurrentMonth = MONTH(CalDate),
CurrentYear = YEAR(CalDate),
EndofWeek1 = DATEADD(DAY, 6 - DATEPART(WEEKDAY,CalDate), CalDate),
EndofWeek2 = DATEADD(DAY, 13 - DATEPART(WEEKDAY,CalDate), CalDate),
EndOfMo = MAX(CalDate) OVER (PARTITION BY YEAR(CalDate), MONTH(CalDate))
FROM
( -- Innermost query, uses an on-the-fly tally table to create an on-the-fly calendar table
SELECT TOP(DATEDIFF(DAY,@STARTDATE,@ENDDATE))
CalDate = DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1, @STARTDATE)
FROM sys.all_columns a, sys.all_columns
) calendarTable;
CalDate CurrentMonth CurrentYear EndofWeek1 EndofWeek2 EndOfMo
---------- ------------ ----------- ---------- ---------- ----------
2015-01-01 1 2015 2015-01-02 2015-01-09 2015-01-31
2015-01-02 1 2015 2015-01-02 2015-01-09 2015-01-31
2015-01-03 1 2015 2015-01-02 2015-01-09 2015-01-31
2015-01-04 1 2015 2015-01-09 2015-01-16 2015-01-31
2015-01-05 1 2015 2015-01-09 2015-01-16 2015-01-31
...
-- 4. Next, we turn the above query into a subquery and add this filter on the outside:
WHERE DATEPART(WEEKDAY,CalDate) = 7 OR DAY(CalDate) = 1;
This filters for Caldate values that fall on a Saturday or the first of the month; Caldate will be our STARTDATE
DECLARE @STARTDATE date = '20150101', @ENDDATE date = '20200101';
SELECT CalDate, DATENAME(WEEKDAY,CalDate)
FROM
( -- 2. Gets the dates, months, years, and "end of week" columns
SELECT
CalDate,
CurrentMonth = MONTH(CalDate),
CurrentYear = YEAR(CalDate),
EndofWeek1 = DATEADD(DAY, 6 - DATEPART(WEEKDAY,CalDate), CalDate),
EndofWeek2 = DATEADD(DAY, 13 - DATEPART(WEEKDAY,CalDate), CalDate),
EndOfMo = MAX(CalDate) OVER (PARTITION BY YEAR(CalDate), MONTH(CalDate))
FROM
( -- 1. Innermost query, uses an on-the-fly tally table to create an on-the-fly calendar table
SELECT TOP(DATEDIFF(DAY,@STARTDATE,@ENDDATE))
CalDate = DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1, @STARTDATE)
FROM sys.all_columns a, sys.all_columns
) calendarTable
) DateCalculations
WHERE DATEPART(WEEKDAY,CalDate) = 7 OR DAY(CalDate) = 1;
CalDate
---------- ------------------------------
2015-01-01 Thursday
2015-01-03 Saturday
2015-01-10 Saturday
2015-01-17 Saturday
2015-01-24 Saturday
2015-01-31 Saturday
2015-02-01 Sunday
2015-02-07 Saturday
2015-02-14 Saturday
...
5. Finally, we add calculations to get the ENDDATE and WEEKNO
For ENDDATE you'll just have to play around with it to understand how it works.
For the WEEKNO I'm using DENSE_RANK to rank the rows. In my DENSE_RANK calculation: PARTITION BY CurrentYear, CurrentMonth resets the rank for each Year/Month group the RANK is ordered by Year, Month, date.
DECLARE @STARTDATE date = '20150101', @ENDDATE date = '20200101';
SELECT
-- 3. Outermost query which produces the result set
--Uncomment these out to better understand the solution:
--CalDate, CurrentMonth, CurrentYear, EndofWeek1, EndofWeek2, EndOfMo,
STARTDATE = CalDate, -- a. get STARTDATE
ENDDATE =
CASE
WHEN EndOfWeek1 >= CalDate THEN EndofWeek1
WHEN MONTH(EndOfWeek2) = CurrentMonth THEN EndofWeek2
ELSE EndOfMo
END,
WEEKNO = DENSE_RANK() OVER
(
PARTITION BY CurrentYear, CurrentMonth
ORDER BY CurrentYear, CurrentMonth, CalDate
)
FROM
( -- 2. Gets the dates, months, years, and "end of week" columns
SELECT
CalDate,
CurrentMonth = MONTH(CalDate),
CurrentYear = YEAR(CalDate),
EndofWeek1 = DATEADD(DAY, 6 - DATEPART(WEEKDAY,CalDate), CalDate),
EndofWeek2 = DATEADD(DAY, 13 - DATEPART(WEEKDAY,CalDate), CalDate),
EndOfMo = MAX(CalDate) OVER (PARTITION BY YEAR(CalDate), MONTH(CalDate))
FROM
( -- 1. Innermost query, uses an on-the-fly tally table to create an on-the-fly calendar table
SELECT TOP(DATEDIFF(DAY,@STARTDATE,@ENDDATE))
CalDate = DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1, @STARTDATE)
FROM sys.all_columns a, sys.all_columns
) calendarTable
) DateCalculations
WHERE DATEPART(WEEKDAY,CalDate) = 7 OR DAY(CalDate) = 1;
-- Itzik Ben-Gan 2001
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply