Second Friday of every month
-
I have this query that gets every Friday of the year, I am trying to get the second Friday of every month.
ANY SUGGESTIONS???
WITH cteFridays AS
(
select CONVERT(datetime,GETDATE()) as dt, datename(dw,CONVERT(date,GETDATE())) as DAY,datename(mm,CONVERT(date,GETDATE())) as MONTH, YEAR(CONVERT(date,GETDATE())) AS YEAR
union all
select s.dt+7 as dts,datename(dw,dt-4) as DAY ,datename(mm,dt+7) as MONTH, YEAR(dt+7) AS YEAR
from cteFridays s
where s.dt<CONVERT(nvarchar,'12/31/' + CONVERT(nvarchar,DATEPART(YEAR, GETDATE())))
)
select dt,DAY,MONTH,YEAR from cteFridays
order by dt
OPTION (MaxRecursion 0)
-
You're using a recursive CTE that counts which can cause a disaster. Read the following link to know the implications: http://www.sqlservercentral.com/articles/T-SQL/74118/
I came up with this formula in 2 versions.
SELECT MonthStart,
DATEADD(DD, DATEDIFF(DD,4, MonthStart)/7*7, 4) --Friday between beginning of month and previous 6 days
+ CASE WHEN DATEADD(DD, DATEDIFF(DD,4, MonthStart)/7*7, 4) = MonthStart
THEN 7 --Conditionally add 1 or 2 weeks
ELSE 14
END SecondFriday,
CASE WHEN DATEADD(DD, DATEDIFF(DD,4, MonthStart)/7*7, 4) = MonthStart
THEN DATEADD(DD, DATEDIFF(DD,4, MonthStart)/7*7, 11) --Conditionally add 1 or 2 weeks
ELSE DATEADD(DD, DATEDIFF(DD,4, MonthStart)/7*7, 18)
END SecondFriday
FROM (
SELECT DATEADD( MM, ROW_NUMBER() OVER(ORDER BY(SELECT NULL))-1, 0) MonthStart
FROM sys.all_columns --Generate a months table with over 3000 months
)m
WHERE DATEADD(DD, DATEDIFF(DD,4, MonthStart)/7*7, 4) > GETDATE()
-
How about this group of ctes? The first cte creates a table of Dates for the current year, the second cte expands these to add the Year, Month and DayOfWeek, whilst the third cte adds a sequence number to each DayOfWeek to determine where it is in the month. The final select just picks "fridays" and "second in the month". Possibly not the quickest solution but generic and (IMHO) easy to read.
WITHcte1AS (SELECT TOP 366 DATEADD(dd,(ROW_NUMBER() OVER (ORDER BY name) -1),CONVERT(DATE,CONVERT(CHAR(4),DATEPART(yyyy,GETDATE()))+'-01-01')) AS [Date] FROM sys.columns),
cte2AS (SELECT [Date],DATEPART(yy,[Date]) AS [Year],DATEPART(mm,[Date]) AS [Month],DATEPART(dw,[Date]) AS [DayOfWeek] FROM cte1),
cte3AS (SELECT *,ROW_NUMBER() OVER (PARTITION BY [Year],[Month],[DayOfWeek] ORDER BY [Date]) AS [SequencedDayOfWeek] FROM cte2)
SELECT * FROM cte3
WHERE [DayOfWeek]=5 -- Friday
AND [SequencedDayOfWeek]=2 -- Second in the month
ORDER BY [Date]
-
andyscott you actually had it returning Thursdays, but that's cool simple change to the DayofWeek=6, both of the helpful examples run very quick, I like the formatting of your example Andy, thanks to both of you..
Now I have a question regarding this???
Can I use the date to execute a SSRS subscription?
I am using 2012 SSRS Standard Edition.
-
cbrammer1219 (3/11/2015)
andyscott you actually had it returning Thursdays..Depends on your SET DATEFIRST settings: Whilst the default (English,US) is 7, meaning weeks start on Sunday, in the UK it is quite normal to have the week starting on a Monday. i.e. with SET DATEFIRST 1. So - for me - day 5 is a Friday! 🙂
-
andyscott (3/11/2015)
cbrammer1219 (3/11/2015)
andyscott you actually had it returning Thursdays..Depends on your SET DATEFIRST settings: Whilst the default (English,US) is 7, meaning weeks start on Sunday, in the UK it is quite normal to have the week starting on a Monday. i.e. with SET DATEFIRST 1. So - for me - day 5 is a Friday! 🙂
That's why my code is language/settings independent. 🙂
-
andyscott (3/11/2015)
How about this group of ctes? The first cte creates a table of Dates for the current year, the second cte expands these to add the Year, Month and DayOfWeek, whilst the third cte adds a sequence number to each DayOfWeek to determine where it is in the month. The final select just picks "fridays" and "second in the month". Possibly not the quickest solution but generic and (IMHO) easy to read.WITHcte1AS (SELECT TOP 366 DATEADD(dd,(ROW_NUMBER() OVER (ORDER BY name) -1),CONVERT(DATE,CONVERT(CHAR(4),DATEPART(yyyy,GETDATE()))+'-01-01')) AS [Date] FROM sys.columns),
cte2AS (SELECT [Date],DATEPART(yy,[Date]) AS [Year],DATEPART(mm,[Date]) AS [Month],DATEPART(dw,[Date]) AS [DayOfWeek] FROM cte1),
cte3AS (SELECT *,ROW_NUMBER() OVER (PARTITION BY [Year],[Month],[DayOfWeek] ORDER BY [Date]) AS [SequencedDayOfWeek] FROM cte2)
SELECT * FROM cte3
WHERE [DayOfWeek]=5 -- Friday
AND [SequencedDayOfWeek]=2 -- Second in the month
ORDER BY [Date]
DATEFIRST issue aside, this is a great solution; very good work sir! I hope you don't mind me making a couple suggestions.
#1 Change the ORDER BY clause in your ROW_NUMBER() functions to (ORDER BY (SELECT NULL)).
They are not necessary and add an additional sort to your query plan
Observe the change:
-- BeforeWITH
cte1AS (SELECT TOP 366 DATEADD(dd,(ROW_NUMBER() OVER (ORDER BY name) -1),CONVERT(DATE,CONVERT(CHAR(4),DATEPART(yyyy,GETDATE()))+'-01-01')) AS [Date] FROM sys.columns),
cte2AS (SELECT [Date],DATEPART(yy,[Date]) AS [Year],DATEPART(mm,[Date]) AS [Month],DATEPART(dw,[Date]) AS [DayOfWeek] FROM cte1),
cte3AS (SELECT *,ROW_NUMBER() OVER (PARTITION BY [Year],[Month],[DayOfWeek] ORDER BY [Date]) AS [SequencedDayOfWeek] FROM cte2)
SELECT * FROM cte3
WHERE [DayOfWeek]=5 -- Friday
AND [SequencedDayOfWeek]=2 -- Second in the month
ORDER BY [Date];
-- After
WITH
cte1AS (SELECT TOP 366 DATEADD(dd,(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1),CONVERT(DATE,CONVERT(CHAR(4),DATEPART(yyyy,GETDATE()))+'-01-01')) AS [Date] FROM sys.columns),
cte2AS (SELECT [Date],DATEPART(yy,[Date]) AS [Year],DATEPART(mm,[Date]) AS [Month],DATEPART(dw,[Date]) AS [DayOfWeek] FROM cte1),
cte3AS (SELECT *,ROW_NUMBER() OVER (PARTITION BY [Year],[Month],[DayOfWeek] ORDER BY (SELECT NULL)) AS [SequencedDayOfWeek] FROM cte2)
SELECT * FROM cte3
WHERE [DayOfWeek]=5 -- Friday
AND [SequencedDayOfWeek]=2 -- Second in the month
ORDER BY [Date];
Now check out the query plans:
Nasty Sort Removed!
#2 Change the ORDER BY from [Date] to [Year],[Month],[SequencedDayOfWeek]
You will get the same result but you are removing another sort in the query plan. Observe the change:
-- BeforeWITH
cte1AS (SELECT TOP 366 DATEADD(dd,(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1),CONVERT(DATE,CONVERT(CHAR(4),DATEPART(yyyy,GETDATE()))+'-01-01')) AS [Date] FROM sys.columns),
cte2AS (SELECT [Date],DATEPART(yy,[Date]) AS [Year],DATEPART(mm,[Date]) AS [Month],DATEPART(dw,[Date]) AS [DayOfWeek] FROM cte1),
cte3AS (SELECT *,ROW_NUMBER() OVER (PARTITION BY [Year],[Month],[DayOfWeek] ORDER BY (SELECT NULL)) AS [SequencedDayOfWeek] FROM cte2)
SELECT * FROM cte3
WHERE [DayOfWeek]=5 -- Friday
AND [SequencedDayOfWeek]=2 -- Second in the month
ORDER BY [Date];
-- After
WITH
cte1AS (SELECT TOP 366 DATEADD(dd,(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1),CONVERT(DATE,CONVERT(CHAR(4),DATEPART(yyyy,GETDATE()))+'-01-01')) AS [Date] FROM sys.columns),
cte2AS (SELECT [Date],DATEPART(yy,[Date]) AS [Year],DATEPART(mm,[Date]) AS [Month],DATEPART(dw,[Date]) AS [DayOfWeek] FROM cte1),
cte3AS (SELECT *,ROW_NUMBER() OVER (PARTITION BY [Year],[Month],[DayOfWeek] ORDER BY (SELECT NULL)) AS [SequencedDayOfWeek] FROM cte2)
SELECT * FROM cte3
WHERE [DayOfWeek]=5 -- Friday
AND [SequencedDayOfWeek]=2 -- Second in the mo
ORDER BY [Year],[Month],[SequencedDayOfWeek];
... and the improved query plan:
Two sorts combined into one!
If you check the query plan on your system you will see that these two changes reduce the est. subtree cost from 0.0160 to 0.0184. :w00t:
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
-
This is quite easy to do if you already have a calendar table that stores that information:
Calendar Tables in T-SQL[/url]
The GenerateCalendar FUNCTION included in that article shows how it can be calculated directly.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
dwain.c (3/11/2015)
This is quite easy to do if you already have a calendar table that stores that information:Calendar Tables in T-SQL[/url]
The GenerateCalendar FUNCTION included in that article shows how it can be calculated directly.
Dang it Dwain!!! You stole my thunder. :crazy:
I was putting together a solution with a calendar table when you posted your comment.
P.S. I had not yet seen your series on Calendar Tables until you posted the link. Very good stuff sir.
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
-
Alan.B (3/11/2015)
dwain.c (3/11/2015)
This is quite easy to do if you already have a calendar table that stores that information:Calendar Tables in T-SQL[/url]
The GenerateCalendar FUNCTION included in that article shows how it can be calculated directly.
Dang it Dwain!!! You stole my thunder. :crazy:
I was putting together a solution with a calendar table when you posted your comment.
P.S. I had not yet seen your series on Calendar Tables until you posted the link. Very good stuff sir.
You have to give Jeff Moden a lot of credit for his version of my GenerateCalendar FUNCTION!
I'm a great believer in: Give a man a fish and he can feed his family for a day. Teach a man to fish and he can feed his family for a lifetime.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
First, Louis' solution is brilliant; I tried for a couple hours to come up with something faster (that does not utilize a calendar table) and failed.
So here's my calendar table solution:
-- the calendar table
CREATE TABLE dbo.datedim
(
DateKey int not null primary key,
DateBase date not null,
YearNum smallint not null,
MonthNum tinyint not null
check (MonthNum < 13),
WeekDayNum tinyint not null
check (WeekDayNum < 8),
WeekDayTxt char(10) not null,
MonthDaySeq tinyint not null
check (MonthDaySeq < 32) ,
MonthWeekdaySeq tinyint not null
check (MonthWeekdaySeq < 6)
);
-- a useful nonclustered index
CREATE INDEX xxx ON dbo.datedim(WeekDayNum, MonthWeekdaySeq)
INCLUDE (YearNum, MonthNum, DateBase);
GO
WITH x AS
(
SELECT
DateBase = cast(dateadd(day,rn-1,'1/1/2015') AS date),
YearNum = datepart(year,dateadd(day,rn-1,'1/1/2015')),
MonthNum = datepart(month,dateadd(day,rn-1,'1/1/2015')),
WeekDayNum = datepart(weekday,dateadd(day,rn-1,'1/1/2015')),
WeekDayTxt = datename(weekday,dateadd(day,rn-1,'1/1/2015')),
MoSeq = row_number() over (partition by datepart(year,dateadd(day,rn-1,'1/1/2015')), datepart(month,dateadd(day,rn-1,'1/1/2015')) order by rn),
WeekdayMoSeq = row_number() over (partition by datepart(year,dateadd(day,rn-1,'1/1/2015')), datepart(month,dateadd(day,rn-1,'1/1/2015')) order by rn) / 7 + 1
FROM dbo.getnumsAB(1,25000,1)
)
INSERT dbo.datedim
SELECT DateKey = REPLACE(CAST(DateBase AS char(10)),'-',''), *
FROM x;
GO
-- Solution
SELECT YearNum, MonthNum, DateBase
FROM dbo.datedim
WHERE WeekDayNum = 6 AND MonthWeekdaySeq = 2
ORDER BY DateKey; -- nc index handles the sort :)
This solution get's me what I need with just a non-clustered index seek. :hehe:
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
-
First, let's note that we need an actual date computation only for the first Friday. For the subsequent Fridays, we can simply add 7 more days.
Note: I had to remove the in-line CTE tally from my code because the SSC site was giving me "sql injection" errors when I tried to post it, but the code is "standard" CROSS JOINs that produce seq nums from 0 to 99; any tally table equivalent will do.
SELECT DATEADD(DAY, weeks.tally * 7, Second_Friday) AS Second_Friday
FROM (
SELECT DATEADD(DAY, 13, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)) AS Jan_14
) AS max_possible_second_friday_of_year
CROSS APPLY (
SELECT DATEADD(DAY, -DATEDIFF(DAY, 4, Jan_14) % 7, Jan_14) AS Second_Friday
) AS actual_second_friday_of_year
INNER JOIN cteTally100 weeks ON
weeks.tally BETWEEN 0 AND 53 AND
DATEADD(DAY, weeks.tally * 7, Second_Friday) < DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0)
Edit: Changed misnomer of "First_Friday" to "Second_Friday".
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
-
Here's the code with the tally table built in as a derived table rather than a CTE.
Note that we need an actual date computation to compute the first desired Friday date. For the subsequent Fridays, we can simply add 7 more days.
SELECT DATEADD(DAY, weeks.tally * 7, Second_Friday) AS Second_Friday
FROM (
SELECT DATEADD(DAY, 13, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)) AS Jan_14
) AS max_possible_second_friday_of_year
CROSS APPLY (
SELECT DATEADD(DAY, -DATEDIFF(DAY, 4, Jan_14) % 7, Jan_14) AS Second_Friday
) AS actual_second_friday_of_year
INNER JOIN (
SELECT ROW_NUMBER() OVER(ORDER BY [10_rows1].dummy) - 1 AS tally
FROM (
SELECT TOP (10) 0 AS dummy
FROM sys.all_columns
) AS [10_rows1]
CROSS JOIN (
SELECT TOP (10) 0 AS dummy
FROM sys.all_columns
) AS [10_rows2]
CROSS JOIN (
SELECT TOP (10) 0 AS dummy
FROM sys.all_columns
) AS [10_rows3]
) AS weeks ON
weeks.tally BETWEEN 0 AND 53 AND
DATEADD(DAY, weeks.tally * 7, Second_Friday) < DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0)
Edit: Changed misnomer of "First_Friday" to "Second_Friday".
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
-
solved!
1st friday : (SELECT DATEADD(DAY, DECODE(EXTRACT(DOW FROM START_DATE), 0,5, 1,4, 2,3, 3,2 ,4,1, 6,6, 0), START_DATE) third_friday FROM (SELECT date_trunc('month',current_date) START_DATE))
2nd friday : (SELECT DATEADD(DAY, 7 + DECODE(EXTRACT(DOW FROM START_DATE), 0,5, 1,4, 2,3, 3,2 ,4,1, 6,6, 0), START_DATE) third_friday FROM (SELECT date_trunc('month',current_date) START_DATE))
3rd friday : (SELECT DATEADD(DAY, 14 + DECODE(EXTRACT(DOW FROM START_DATE), 0,5, 1,4, 2,3, 3,2 ,4,1, 6,6, 0), START_DATE) third_friday FROM (SELECT date_trunc('month',current_date) START_DATE))
Ignore following!
5th Business Day SQL : (SELECT DATEADD(DAY, 6 - DECODE(EXTRACT(DOW FROM START_DATE), 1, 2,0, 1, 0), START_DATE) FROM (SELECT date_trunc('month',current_date) START_DATE))
- lokesh97singh wrote:
solved!
1st friday : (SELECT DATEADD(DAY, DECODE(EXTRACT(DOW FROM START_DATE), 0,5, 1,4, 2,3, 3,2 ,4,1, 6,6, 0), START_DATE) third_friday FROM (SELECT date_trunc('month',current_date) START_DATE))
2nd friday : (SELECT DATEADD(DAY, 7 + DECODE(EXTRACT(DOW FROM START_DATE), 0,5, 1,4, 2,3, 3,2 ,4,1, 6,6, 0), START_DATE) third_friday FROM (SELECT date_trunc('month',current_date) START_DATE))
3rd friday : (SELECT DATEADD(DAY, 14 + DECODE(EXTRACT(DOW FROM START_DATE), 0,5, 1,4, 2,3, 3,2 ,4,1, 6,6, 0), START_DATE) third_friday FROM (SELECT date_trunc('month',current_date) START_DATE))
Ignore following!
5th Business Day SQL : (SELECT DATEADD(DAY, 6 - DECODE(EXTRACT(DOW FROM START_DATE), 1, 2,0, 1, 0), START_DATE) FROM (SELECT date_trunc('month',current_date) START_DATE))
Heh... Wecome aboard but... "Must Look Eye!"
You've posted what looks like Oracle code in an SQL Server forum and it's not going to work in SQL Server.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply