Solved

Make weekends and holdidays have the preceding work day number.

Posted on 2010-11-08
12
347 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:JS56
  • 5
  • 4
  • 3
12 Comments
 
LVL 6

Expert Comment

by:dan_mason
ID: 34086150
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.
0
 

Author Comment

by:JS56
ID: 34086187
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
0
 
LVL 6

Accepted Solution

by:
dan_mason earned 250 total points
ID: 34086289
How about a correlated subquery that uses your existing CASE statement, but instead of assigning 0 to the value when it's a holiday or weekend, gets the number for the 'maximum' date before the current date that is a workday?
0
 
LVL 6

Expert Comment

by:dan_mason
ID: 34086407
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.


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    

Open in new window

0
 
LVL 6

Expert Comment

by:dan_mason
ID: 34086438
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     

Open in new window

0
 
LVL 58

Assisted Solution

by:cyberkiwi
cyberkiwi earned 250 total points
ID: 34086622
After running the block of code in the question (which sets them as day / zero), run this additional query:

update a
set workdaynum = b.workdaynum
from tbldates a
cross 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

Note that some days will still have workdaynum = 0, that is because those are the first days of the month, and that month starts with a weekend/holiday.
0
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 

Author Comment

by:JS56
ID: 34088151
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.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34088173
> The suggestions don't seem to work

You mean http:#a34086622 inclusive? That would be strange..
0
 

Author Comment

by:JS56
ID: 34088380
I'm working on to make sure I am doing it right.
0
 

Author Comment

by:JS56
ID: 34089096
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

0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34089153
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
0
 

Author Comment

by:JS56
ID: 34089364
Thanks, I will keep a copy of that.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
OPENDATASOURCE 8 36
sql help 5 52
How to disable/enable multiple sql jobs in efficient way 11 100
Following an example - removing duplicate strings 4 51
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now