x

Adding Values to a calander table

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`
more ▼

asked Dec 29, 2011 at 03:46 PM in Default

siera_gld gravatar image

siera_gld
1k 77 82 84

(comments are locked)
10|1200 characters needed characters left

1 answer: sort newest

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)
more ▼

answered Dec 29, 2011 at 06:07 PM

GPO gravatar image

GPO
2k 33 37 41

I need to do a better job understanding the ability to re use the cte's as you've done above...thank you so much
Dec 29, 2011 at 07:57 PM siera_gld
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x343
x279
x26
x21
x10

asked: Dec 29, 2011 at 03:46 PM

Seen: 866 times

Last Updated: Dec 29, 2011 at 10:45 PM