Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Make weekends and holdidays have the preceding work day number.

Avatar of JS56
JS56 asked on
Microsoft SQL Server 2005
12 Comments1 Solution394 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Avatar of dan_mason
dan_masonFlag of United Kingdom of Great Britain and Northern Ireland image

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Commented:
This problem has been solved!
Unlock 1 Answer and 12 Comments.
See Answers