Solution to TSQL Beginners Challenge 2

; with t as (
    select 'Jan' as Monname, '01' as Monno
    union all select 'Feb', '02'
    union all select 'Mar', '03'
    union all select 'Apr', '04'
	union all select 'May', '05'
	union all select 'Jun', '06'
	union all select 'Jul', '07'
	union all select 'Aug', '08'
	union all select 'Sep', '09'
	union all select 'Oct', '10'
	union all select 'Nov', '11'
	union all select 'Dec', '12'
), i as (
	select 'Sun' as Dyname, 1 as Dynum
	union all select 'Mon', 2
	union all select 'Tue', 3
	union all select 'Wed', 4
	union all select 'Thu', 5
	union all select 'Fri', 6
	union all select 'Sat', 7 
)
select Yr, Mon, Dy, Dyno, case
	when month(NewDate) > month(yyyymmdd) then 'Invalid Date'
	else left(convert(nvarchar, NewDate, 120), 10)
end as [Date]
from (
	select Yr, Mon, Dy, Dyno, Dynum, Wochentag, yyyymmdd,
	dateadd(dd, ((Dynum - Wochentag) + ((Dyno - 1) * 7)) + case when Wochentag > Dynum then 7 else 0 end, yyyymmdd) as NewDate
	from (
		select Yr, Mon, Dy, Dyno, Dynum, datepart(weekday, yyyymmdd) as Wochentag, yyyymmdd
		from (
			select Yr, Mon, Dy, Dyno, Dynum,
			cast(cast(Yr as nvarchar(4)) + '-' + Monno + '-' + '01' as datetime) as yyyymmdd
			from @tbl
			inner join t on Monname = Mon
			inner join i on Dyname = Dy
		) as VIRTUAL_TABLE
	) as NEW_VIRTUAL_TABLE
) as LAST_VIRTUAL_TABLE
order by Yr

Tags: