Yes, I know this is old post. Thought I'd provide a different slant on things despite its age. Heh... and my apologies. I just realized that I've nearly duplicate what @jyao posted above.
Based on the current edit of the OP's original question, I couldn't figure out why people where posting the answers they did.
Looking through the edits, I found the original question and have posted it below...
I have a time series ranging from 1.1.1996 -
30.8.2014 in an SQL database e.g. with table "db.dbo.datestable".
I need to determine the dates which are the "3rd Friday of each month"
for this date range in SQL.
I expect I should use a combination of "DENSE_RANK()" and "PARTITION
BY()" to set "rank = 3". However, I am new to SQL and unable to find
the correct code.
Can you solve this problem?
The part of the original question that I've emphasized by bolding it seems to be the key. I could certainly be incorrect but it seems to me that the OP was stating that he has a "Calendar" table called "dbo.datestable" and, to me, that makes a huge amount of difference and I now understand why many of the answers are what they are including the one that generated the dates because it was posted on Nov 10th... one day after the final edit on the question, which removed the final vestiges of the reference to the "dbo.datestable".
Like I said, I could be wrong but here's my interpretation of the original question.
I have a "Calendar" table called "dbo.datestable". Given any range of
dates covered by that table, how can I return just the dates that are the 3rd Friday of each
month within that given date range?
Since the conventional methods for doing this have already been covered, I'll add an alternative that could be helpful for some.
Let's simulate a couple of the columns that I think the OP will already have in his table. Of course, I'm guessing at the column names. Please sub whatever the equivalent columns are for your "Calendar" table. Also, I'm doing this all in TempDB so I don't take a chance of interfering with someone's real "Calendar" table.
-- Simulate just a couple of the necessary columns of the OPs "Calendar" table.
-- This is not a part of the solution. We're just trying to simulate what the OP has.
--===== Variables to control the dates that will appear in the "Calendar" table.
DECLARE @StartDT DATETIME
SELECT @StartDT = '1900' --Will be inclusive start of this year in calculations.
,@EndDT = '2100' --Will be exclusive start of this year in calculations.
--===== Create the "Calendar" table with just enough columns to simulate the OP's.
CREATE TABLE #datestable
TheDate DATETIME NOT NULL
,DW TINYINT NOT NULL --SQL standard abbreviate of "Day of Week"
--===== Populate the "Calendar" table (uses "Minimal Logging" in 2008+ this case).
WITH cteGenDates AS
SELECT TOP (DATEDIFF(dd,@StartDT,@EndDT)) --You can use "DAY" instead of "dd" if you prefer. I don't like it, though.
TheDate = DATEADD(dd, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1, @StartDT)
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
INSERT INTO #datestable WITH (TABLOCK)
,DW = DATEDIFF(dd,0,TheDate)%7+1 --Monday is 1, Friday is 5, Sunday is 7 etc.
OPTION (RECOMPILE) -- Help keep "Minimal Logging" in the presence of variables.
--===== Add the expected named PK for this example.
ALTER TABLE #datestable
ADD CONSTRAINT PK_datestable PRIMARY KEY CLUSTERED (TheDate)
It's also a given that I don't know if the OP can make changes to his "Calendar" table so this might not help him but it may help others. With that in mind, let's add a DWoM (Day of Week for the Month) column. If you don't like the name, please feel free to change it to whatever you need on your own box.
--===== Add the new column.
ALTER TABLE #datestable
ADD DWOM TINYINT NOT NULL DEFAULT (0)
Next, we need to populate the new column. The OP had a sense of this in his original unadulterated post.
--===== Populate the new column using the CTE trick for updates so that
-- we can use a Windowing Function in an UPDATE.
WITH cteGenDWOM AS
SELECT DW# = ROW_NUMBER() OVER (PARTITION BY DATEDIFF(mm,0,TheDate), DW
ORDER BY TheDate)
SET DWOM = DW#
Now, because that's a fixed length column, that just created a bunch of page splits so we need to rebuild the Clustered Index to "repack" the table to have as many rows per page as possible for the sake of performance.
--===== "Repack" the Clustered Index to get rid of the page splits we
-- caused by adding the new column.
ALTER INDEX PK_datestable
REBUILD WITH (FILLFACTOR = 100, SORT_IN_TEMPDB = ON)
Once that's done, queries that do things like returning the 3rd Friday of every month in a given date range become trivial and fairly obvious to read.
--===== Return the 3rd Friday of every month included in the given date range.
WHERE TheDate >= '1996-01-01' --I never use "BETWEEN" for dates out of habit for end date offsets.
AND TheDate <= '2014-08-30'
AND DW = 5 --Friday
AND DWOM = 3 --The 3rd one for every month
ORDER BY TheDate