asked
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`
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)
