-----------------------------
-- Recursive
Query Example --
-----------------------------
---------------------------------------------------------------------------
--Write a query
to achieve each month's 3rd Tuesday date for any supplied year
---------------------------------------------------------------------------
DECLARE @YEAR VARCHAR(20)='2016' -- Year to supply
DECLARE @DT DATE
SET @DT = CAST('01-01-'+@YEAR AS DATE)
;WITH CTE
AS
(
SELECT @DT DT,MONTH(@DT) AS MNT,DATENAME(WEEKDAY,@DT) WEEKDAY
UNION ALL
SELECT DATEADD(D,1,DT),MONTH(DATEADD(D,1,DT)) AS MNT,DATENAME(WEEKDAY,DATEADD(D,1,DT)) WEEKDAY FROM CTE
WHERE YEAR(DATEADD(D,1,DT))='2016'
),
CTE1
AS
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY MNT ORDER BY MNT) AS RNK FROM CTE WHERE WEEKDAY='TUESDAY'
)
SELECT DT,WEEKDAY FROM CTE1 WHERE RNK=3 option (maxrecursion
0)
Output :
No comments:
Post a Comment