Adding Values to a calander table

Question

I need to add a workday value to a calander table

The work day is YYYY### as a char 7

The ### is three digits but should not consider weekends for the workday so 2012-01-02 should = 2012001 then monday, 2012-01-09 will be 2012006

I also need my week # to begin on my fiscal calander of dec to november

Something like

WITH cteSequence ( SeqNo) as

(
      SELECT 0
      UNION ALL
      SELECT SeqNo + 1
      FROM cteSequence
      WHERE SeqNo < 365
)

SELECT DATEADD(day, SeqNo, '2012-01-01')DT,
       case  
         when DATEPART(dw, DATEADD(day, SeqNo, '2012-01-01')) in (1,7) 
         then 0
         else  convert(char(7),DATEPART(year, DATEADD(day, SeqNo, '2012-01-01')) )
         end as WORK_DAY ,
          left('000'+convert(char(3),(DATEPART(d, DATEADD(day, SeqNo, '2012-01-01')))),3),
       case
         when        DATEPART(month, DATEADD(day, SeqNo, '2012-01-01')) = 12
         then        datepart(year,dateadd(year, 1,DATEADD(day, SeqNo, '2012-01-01')))
         else DATEPART(year, DATEADD(day, SeqNo, '2012-01-01'))
       end  as FISCAL_YR,
       case
         when        DATEPART(month, DATEADD(day, SeqNo, '2012-01-01')) = 12
         then        datepart(wk,dateadd(year, 1,DATEADD(day, SeqNo, '2012-01-01')))
         else DATEPART(wk, DATEADD(day, SeqNo, '2012-01-01'))
       end  as FISCAL_WK,

       DATEPART(dw, DATEADD(day, SeqNo, '2012-01-01')) DAYNUM

FROM cteSequence
OPTION ( MAXRECURSION 0)

GO`

Answer 1

This probably makes a few assumptions about what weekday the week starts on, and how to treat the last week of November/first week of December and so on, but it should get you started. Also I'm not sure a recursive CTE is ideal for generating lists of numbers. I know it's only 365 in this case...

    WITH cteSequence ( SeqNo) as
(
      SELECT 0
      UNION ALL
      SELECT SeqNo + 1
      FROM cteSequence
      WHERE SeqNo < 365
)
, step_2 as
(
SELECT DATEADD(day, SeqNo, '2012-01-01')DT,
        case    
            when DATEPART(dw, DATEADD(day, SeqNo, '2012-01-01')) in (1,7) 
            then 0
            else  convert(char(7),DATEPART(year, DATEADD(day, SeqNo, '2012-01-01')) )
            end as WORK_DAY ,
        ROW_NUMBER() OVER(PARTITION BY case when DATEPART(dw, DATEADD(day, SeqNo, '2012-01-01')) in (1,7) 
                                                 then 0
                                            else  convert(char(7),DATEPART(year, DATEADD(day, SeqNo, '2012-01-01')) )
                                        end
                              ORDER BY SeqNo) as work_day_seq,
             --left('000'+convert(char(3),(DATEPART(d, DATEADD(day, SeqNo, '2012-01-01')))),3),
        case
            when        DATEPART(month, DATEADD(day, SeqNo, '2012-01-01')) = 12
            then        datepart(year,dateadd(year, 1,DATEADD(day, SeqNo, '2012-01-01')))
            else    DATEPART(year, DATEADD(day, SeqNo, '2012-01-01'))
        end  as FISCAL_YR,
        --case
        --    when        DATEPART(month, DATEADD(day, SeqNo, '2012-01-01')) = 12
        --    then        datepart(wk,dateadd(year, 1,DATEADD(day, SeqNo, '2012-01-01')))
        --    else    DATEPART(wk, DATEADD(day, SeqNo, '2012-01-01'))
        --end  as FISCAL_WK,
        DATEPART(dw, DATEADD(day, SeqNo, '2012-01-01')) DAYNUM
FROM cteSequence
)
   SELECT DT 
          ,WORK_DAY
          ,case when WORK_DAY = '0' then null 
                else cast(FISCAL_YR as char(4)) + right('000' + cast(work_day_seq as varchar(4)) ,3) 
            end as  WORK_DAY_NUMBER
          ,FISCAL_YR
          ,DAYNUM
          ,datepart(week,DT) as weeknum
          ,((datepart(week,DT)+ 3) %53) + 1
 as [weeknum%53]
     FROM step_2 
 ORDER BY DT
OPTION ( MAXRECURSION 0)

Adding Values to a calander table

Follow this question

Related Questions