Solution to TSQL Beginners Challenge 2

DECLARE @tbl TABLE (Yr INT,Mon VARCHAR(50),Dy VARCHAR(50),Dyno INT)
INSERT INTO @tbl(Yr, Mon, Dy, Dyno)
    SELECT 2010,'Jan','Sun',2 UNION ALL
	SELECT 2005,'Jan','Mon',3 UNION ALL
	SELECT 1995,'Feb','Sun',1 UNION ALL
	SELECT 2000,'Feb','Wed',4 UNION ALL
	SELECT 1982,'Mar','Tue',2 UNION ALL
	SELECT 2010,'Mar','Tue',8

--SELECT * FROM @tbl


-- Solution with Date Calender
-- TSql Beginners Challenge #2
-- Author: Niladri Biswas


--Validate Year and Month
;WITH ValidDates AS(
	SELECT 
			Yr
			,Mon
			,Dy
			,Dyno FROM @tbl
	WHERE Yr LIKE REPLACE('XXXX','X','[0-9]') AND 
		  Mon IN('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'))
-- Generate the date ranges for a particular month of a particular year
,YrMonthDateRange AS
(
	SELECT
			Yr
			,Mon
			,StartDate=CONVERT(DATETIME, CONVERT(VARCHAR, Yr)+'-'+CONVERT(VARCHAR, Mon) + '-1',120) 
			,Enddate=DATEADD(DAY,-1,DATEADD(MONTH, 1, CONVERT(DATETIME, CONVERT(VARCHAR, Yr)+'-'+CONVERT(VARCHAR, Mon) + '-1',120)))
  			
	FROM ValidDates
)
-- Date range for Calender Generation
,DateRangeForCalenderGeneration AS
(
	SELECT			
			StartDate=MIN(CONVERT(DATETIME, CONVERT(VARCHAR, Yr)+'-'+CONVERT(VARCHAR, Mon) + '-1',120)) 
			,Enddate=MAX(DATEADD(DAY,-1,DATEADD(MONTH, 1, CONVERT(DATETIME, CONVERT(VARCHAR, Yr)+'-'+CONVERT(VARCHAR, Mon) + '-1',120))))  			
	FROM ValidDates
)
-- Building a DateCalender
,DateCalender AS
(
         SELECT StartDate [date] FROM DateRangeForCalenderGeneration
         UNION ALL
         SELECT [date] + 1
         FROM    DateCalender   
         WHERE   [date] + 1 < (SELECT Enddate FROM DateRangeForCalenderGeneration)
)
,GenerateAllDates AS
(
	SELECT DISTINCT 
					Yr
					,Mon
					,Dy =SUBSTRING(DATENAME(dw,d.date),0,4)
					,d.date
  FROM DateCalender d 
  JOIN YrMonthDateRange dr 
  ON d.date BETWEEN dr.startdate AND dr.enddate
)
--Find the needed dates
SELECT 
	 t.Yr
	,t.Mon
	,t.Dy
	,t.Dyno
	,[Date]=COALESCE(x.[Date],'Invalid Date')
 FROM @tbl t
 LEFT JOIN(
 SELECT t.Yr,t.Mon,t.Dy,t.Dyno,dc.[Date] 
 FROM @tbl t
 JOIN ( SELECT 
			Yr,
			Mon,
			Dy,
			[Date]=CONVERT(VARCHAR(10),[Date],20),	
			DENSE_RANK() OVER(PARTITION BY Yr,Mon,Dy ORDER BY [Date]) Dyno
 FROM GenerateAllDates)dc
 ON dc.Yr = t.Yr AND dc.Mon = t.Mon AND dc.Dy = t.Dy AND dc.Dyno = t.Dyno)x
 ON x.Yr = t.Yr AND x.Mon = t.Mon AND x.Dy = t.Dy AND x.Dyno = t.Dyno
 ORDER BY t.Yr,t.Mon,t.Dy
 OPTION (MAXRECURSION 0)

Tags: