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

avatar image

siera_gld
1k 82 88 93

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

1 answer: sort voted first

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

avatar image

GPO
4.9k 41 51 58

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.

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:

x441
x437
x35
x24
x9

asked: Dec 29, 2011 at 03:46 PM

Seen: 1135 times

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

Copyright 2017 Redgate Software. Privacy Policy