- Home /

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`

Comment

GPO

Best Answer

**Answer** by GPO
·
Dec 29, 2011 at 06:07 PM

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)

Copyright 2019 Redgate Software. Privacy Policy

- Anonymous
- Sign in
- Create
- Ask a question
- Spaces
- Ask SSC Site Issues (meta-askssc)
- Explore
- Topics
- Questions
- Users
- Badges