day generator for powerpivot with weekday and holiday subselect!!! mssql 2012, powerpivot excel 2016

tages_generator_fuer_powerpivot.sql

/* SOURCE:
http://stackoverflow.com/questions/2157282/generate-days-from-date-range 
https://gist.github.com/josy1024/cb1487d66d9e0ccbd420bc4a23b6e90e
+ subselect for holiday subselect
*/

with [dates] as (
    select convert(datetime, '2016-01-01') as [date] --start
    union all
    select dateadd(day, 1, [date])
    from [dates]
    where [date] < '2018-01-01' --end
)
select [date]
, DATEPART (y,[date]) as Jahr
, DATEPART (m,[date]) as Monat
, DATEPART (dw,[date]) as Wochentag
, (select holidayname from holidaytable 
where holidaytable.hdate = [date]) 
as Feiertag
from [dates]
where [date] between '2016-01-01' and '2016-31-12'
option (maxrecursion 0)