Monday, February 29, 2016

Write a Recursive query to achieve each month's 3rd Tuesday date for any supplied year



-----------------------------
-- 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