JS56 asked on # Make weekends and holdidays have the preceding work day number.

I have the following code that gives me the working day number per month but what I need now is to make the weekends and holidays have the same workday number as the preceding work day. For instance, if Friday is workday number 5, then the following Saturday and Sunday should also be number 5. Also, if Monday is a holiday then that should also be number 5 and then Tuesday would be the next work day with a number 6. Is this Possible? The code below sets the weekends and holidays to 0

select

thedate,

weekend_or_holiday,

workdaynum=case

when weekend_or_holiday=1 then 0

else row_number() over (

partition by datediff(m,0,thedate), weekend_or_holiday

order by thedate) end

from tbldates

order by thedate

-- Add column

alter table tbldates add workdaynum int

-- update column

;with tmp as (

select

thedate,

weekend_or_holiday,

workdaynum,

setto=case

when weekend_or_holiday=1 then 0

else row_number() over (

partition by datediff(m,0,thedate), weekend_or_holiday

order by thedate) end

from tbldates

)

update tmp set workdaynum=setto

select

thedate,

weekend_or_holiday,

workdaynum=case

when weekend_or_holiday=1 then 0

else row_number() over (

partition by datediff(m,0,thedate), weekend_or_holiday

order by thedate) end

from tbldates

order by thedate

-- Add column

alter table tbldates add workdaynum int

-- update column

;with tmp as (

select

thedate,

weekend_or_holiday,

workdaynum,

setto=case

when weekend_or_holiday=1 then 0

else row_number() over (

partition by datediff(m,0,thedate), weekend_or_holiday

order by thedate) end

from tbldates

)

update tmp set workdaynum=setto

Microsoft SQL Server 2005

dan_mason

What is the underlying requirement here? Is it so you can calculate the number of business days expired so far, or that exist in a given month? If that is the object there are probably better ways to go about it that messing about with the numbers in this way.

JS56

The requirement is they want each day that is a working in the month numbered so when they compare to the previous month, etc, they know the work days they are comparing. The weekends should display the same number as the Friday before. I cannot change their requirement

Log in or sign up to see answer

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

or

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a questiondan_mason

Actually it might be easier than that; if you have an attribute weekend_or_holiday against each date can you not do a COUNT against that attribute to determine the effective workday number?

Something like I've shown below (though I haven't had a chance to test it).

Alternatively if you're able to get another column in your table that will flag a positive '1' against each row that is a workday, you could just SUM for the workdaynum whether it's a weekday or not.

Something like I've shown below (though I haven't had a chance to test it).

Alternatively if you're able to get another column in your table that will flag a positive '1' against each row that is a workday, you could just SUM for the workdaynum whether it's a weekday or not.

```
select
thedate,
weekend_or_holiday,
workdaynum= CASE weekend_or_holiday
WHEN 1 THEN (SELECT COUNT(td.weekend_or_holiday) FROM tbldates tbldb WHERE tbldb.thedate<tblda.thedate AND DATEPART(m,tbldb.thedate)=DATEPART(m,tblda.thedate)) AND weekend_or_holiday=0
ELSE row_number() over (
partition by datediff(m,0,thedate), weekend_or_holiday
order by thedate) end
from tbldates tblda
order by thedate
```

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat

William Peck

dan_mason

Sorry, I misplaced a bracket in that last piece of code...

```
select
thedate,
weekend_or_holiday,
workdaynum= CASE weekend_or_holiday
WHEN 1 THEN
(SELECT
COUNT(td.weekend_or_holiday)
FROM tbldates tbldb
WHERE tbldb.thedate<tblda.thedate
AND DATEPART(m,tbldb.thedate)=DATEPART(m,tblda.thedate) AND weekend_or_holiday=0)
ELSE row_number() over (
partition by datediff(m,0,thedate), weekend_or_holiday
order by thedate) end
from tbldates tblda
order by thedate
```

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

JS56

The suggestions don't seem to work when there are two holiday days in a row, such as Thanksgiving when the holidays are Thursday and Friday. It should load the work day number for Wednesday for both Thursday and Friday and it does not. the case statement works well for weekends however.

cyberkiwi

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

JS56

I'm working on to make sure I am doing it right.

JS56

Ok, I have it working. The only part that I needed to get working was populating the Weekends and holidays with the correct working day number. I used pieces of the suggestions above to come up with the following:

1) This part was working anyway

;WITH tmp as(select

CALENDAR_DATE,

WORK_DAY_NUMBER,

setto=case

when IS_WEEKEND='X' or IS_HOLIDAY = 'X' then 0

else row_number() over (

partition by datediff(m,0,CALENDAR_DATE),IS_WEEKEND_HOLIDAY

order by CALENDAR_DATE) end

from dbo.TIME_DIM

)

update tmp set WORK_DAY_NUMBER = setto

2) This section uses some of your suggestions above to populate the Working Day number for the Weekends and Holidays

--update Holidays and Weekends with preceding workday number

;with holiday_weekends as(select

a.CALENDAR_DATE,

a.WORK_DAY_NUMBER,

setto=case when WORK_DAY_NUMBER = 0 then (select max(b.WORK_DAY_NUMBER) from dbo.TIME_DIM b

where b.CALENDAR_DATE >=

convert(datetime,convert(varchar(10),Dateadd(mm,-0,a.CALENDAR_DATE)-Day(Dateadd(mm,-0,a.CALENDAR_DATE))+1,120),120) --this gives first day of month

AND b.CALENDAR_DATE <= a.CALENDAR_DATE

and month(b.CALENDAR_DATE)= month(a.CALENDAR_DATE))

else row_number() over (

partition by datediff(m,0,a.CALENDAR_DATE),a.IS_WEEKEND_HOLIDAY

order by CALENDAR_DATE) end

from dbo.TIME_DIM a)

update holiday_weekends set WORK_DAY_NUMBER = setto

3) This last part updates the Working Day number when it is still 0 because it is the first day of the month and I set it to 1 to match the first working day in that month.

--update Working Day Number when it is set to zero because it is the first day of the month

update a

set a.WORK_DAY_NUMBER = 1

from dbo.TIME_DIM a

where a.WORK_DAY_NUMBER = 0

END

1) This part was working anyway

;WITH tmp as(select

CALENDAR_DATE,

WORK_DAY_NUMBER,

setto=case

when IS_WEEKEND='X' or IS_HOLIDAY = 'X' then 0

else row_number() over (

partition by datediff(m,0,CALENDAR_DATE

order by CALENDAR_DATE) end

from dbo.TIME_DIM

)

update tmp set WORK_DAY_NUMBER = setto

2) This section uses some of your suggestions above to populate the Working Day number for the Weekends and Holidays

--update Holidays and Weekends with preceding workday number

;with holiday_weekends as(select

a.CALENDAR_DATE,

a.WORK_DAY_NUMBER,

setto=case when WORK_DAY_NUMBER = 0 then (select max(b.WORK_DAY_NUMBER) from dbo.TIME_DIM b

where b.CALENDAR_DATE >=

convert(datetime,convert(v

AND b.CALENDAR_DATE <= a.CALENDAR_DATE

and month(b.CALENDAR_DATE)= month(a.CALENDAR_DATE))

else row_number() over (

partition by datediff(m,0,a.CALENDAR_DA

order by CALENDAR_DATE) end

from dbo.TIME_DIM a)

update holiday_weekends set WORK_DAY_NUMBER = setto

3) This last part updates the Working Day number when it is still 0 because it is the first day of the month and I set it to 1 to match the first working day in that month.

--update Working Day Number when it is set to zero because it is the first day of the month

update a

set a.WORK_DAY_NUMBER = 1

from dbo.TIME_DIM a

where a.WORK_DAY_NUMBER = 0

END

cyberkiwi

This would have worked as well, a quick tweak to my query above.

update a

set workdaynum = isnull(b.workdaynum, 1)

from tbldates a

outer apply (

select top(1) workdaynum

from tbldates b

where b.thedate < a.thedate and b.thedate > a.thedate - 31

and MONTH(b.thedate) = MONTH(a.thedate)

and b.workdaynum > 0

order by b.thedate desc) b

where a.workdaynum = 0

update a

set workdaynum = isnull(b.workdaynum, 1)

from tbldates a

outer apply (

select top(1) workdaynum

from tbldates b

where b.thedate < a.thedate and b.thedate > a.thedate - 31

and MONTH(b.thedate) = MONTH(a.thedate)

and b.workdaynum > 0

order by b.thedate desc) b

where a.workdaynum = 0

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst

William Peck

JS56

Thanks, I will keep a copy of that.