The trouble with data…

Someone asked me if we could create a running total of working days per month that could easily be placed in a Date dimension, so I cooked up this CTE

WITH   mycte as
        (
        select cast('2000-01-01' as datetime) DateValue,
        case when datepart(dw, cast('2000-01-01' as datetime) -1) < 6 then 1 else 0 end IS_WEEKEND
        union all
        select DateValue + 1, 
        case when datepart(dw, DateValue) < 6 then 1 else 0 end IS_WEEKEND
        from    mycte   
        where   DateValue + 1 < '2050-12-31'
        )
select DateValue as CAL_DATE
,       datename(dw, DateValue) CAL_DAY
,       datepart(dw, DateValue-1) CAL_DAY_IN_WEEK
,       datepart(dd, DateValue) CAL_DAY_IN_MONTH
,       datepart(dy, DateValue) CAL_DAY_OF_YEAR
,       datepart(ww, DateValue) CAL_WEEK
,       datename(mm, DateValue) CAL_MONTH
,       substring(datename(mm, DateValue), 1, 3) CAL_SHORT_MONTH
,       datepart(mm, DateValue) CAL_MONTH_IN_YEAR
,       datepart(qq, DateValue) CAL_QUARTER
,       case when datepart(mm, DateValue) < 7 then 1 else 2 end CAL_HALF_YEAR
,       datepart(yy, DateValue) CAL_YEAR
,        IS_WEEKEND
,        (SELECT sum(IS_WEEKEND) FROM mycte 
              WHERE 
              DateValue <= m.datevalue 
              and datepart(yy, DateValue) = datepart(yy, m.DateValue) 
              and datename(mm, DateValue) = datename(mm, m.DateValue) 
         ) AS WORKING_DAY_NO

FROM    mycte m

OPTION  (MAXRECURSION 0)

The results look like this