April 9, 2009 at 11:53 am
I'm trying to calculate Business Days (excluding weekends and holidays). I have gone as far as excluding weekends. As for holidays, my coding only seems to subtract one holiday case statement and ignores the other holiday case statements. Can someone help? Below is the code
SELECT DISTINCT
CADM.PATIENT_NO,
( DATEDIFF (DD, CADM.APPLIED_DATE, CADM.SERVICE_AUTHORIZED_DATE) + 1) /* start with total number of days including weekends */
- ( DATEDIFF (WK, CADM.APPLIED_DATE, CADM.SERVICE_AUTHORIZED_DATE) * 2) /* subtract 2 days for each full weekend */
- ( CASE WHEN DATENAME (DW, CADM.APPLIED_DATE) = 'Sunday' THEN 1 ELSE 0 END) /* if SERVICE_AUTHORIZED_DATE (end date) is a Sunday, subtract 1*/
- ( CASE WHEN DATENAME (DW, CADM.SERVICE_AUTHORIZED_DATE) = 'Sunday' THEN 1 ELSE 0 END) /* if SERVICE_AUTHORIZED_DATE (end date) is a Sunday, subtract 1*/
- ( CASE WHEN DATEPART ( MONTH, TBHOLDAY.HOLIDAY_DATE ) = '1' AND DATEPART ( DAY, TBHOLDAY.HOLIDAY_DATE ) = '1' THEN 1 ELSE 0 END ) /*NewYear,*/
- ( CASE WHEN DATEPART ( MONTH, TBHOLDAY.HOLIDAY_DATE ) = '1' AND DATEPART ( DAY, TBHOLDAY.HOLIDAY_DATE ) = '19' THEN 1 ELSE 0 END )/* MLKBirthDay*/
- ( CASE WHEN DATEPART ( MONTH, TBHOLDAY.HOLIDAY_DATE ) = '2' AND DATEPART ( DAY, TBHOLDAY.HOLIDAY_DATE ) = '16' THEN 1 ELSE 0 END ) /*PresidDay,*/
- ( CASE WHEN DATEPART ( MONTH, TBHOLDAY.HOLIDAY_DATE ) = '4' AND DATEPART ( DAY, TBHOLDAY.HOLIDAY_DATE ) = '10' THEN 1 ELSE 0 END ) /*GoodFriday,*/
- ( CASE WHEN DATEPART ( MONTH, TBHOLDAY.HOLIDAY_DATE ) = '5' AND DATEPART ( DAY, TBHOLDAY.HOLIDAY_DATE ) = '25' THEN 1 ELSE 0 END ) /*MemorDay,*/
- ( CASE WHEN DATEPART ( MONTH, TBHOLDAY.HOLIDAY_DATE ) = '7' AND DATEPART ( DAY, TBHOLDAY.HOLIDAY_DATE ) = '3' THEN 1 ELSE 0 END ) /*IndependDay,*/
- ( CASE WHEN DATEPART ( MONTH, TBHOLDAY.HOLIDAY_DATE ) = '9' AND DATEPART ( DAY, TBHOLDAY.HOLIDAY_DATE ) = '7' THEN 1 ELSE 0 END ) /*LaborDay,*/
- ( CASE WHEN DATEPART ( MONTH, TBHOLDAY.HOLIDAY_DATE ) = '10' AND DATEPART ( DAY, TBHOLDAY.HOLIDAY_DATE ) = '12' THEN 1 ELSE 0 END ) /*ColumDay,*/
- ( CASE WHEN DATEPART ( MONTH, TBHOLDAY.HOLIDAY_DATE ) = '11' AND DATEPART ( DAY, TBHOLDAY.HOLIDAY_DATE ) = '11' THEN 1 ELSE 0 END ) /*VeterDay,*/
- ( CASE WHEN DATEPART ( MONTH, TBHOLDAY.HOLIDAY_DATE ) = '11' AND DATEPART ( DAY, TBHOLDAY.HOLIDAY_DATE ) = '26' THEN 1 ELSE 0 END ) /*hxgiving,*/
- ( CASE WHEN DATEPART ( MONTH, TBHOLDAY.HOLIDAY_DATE ) = '12' AND DATEPART ( DAY, TBHOLDAY.HOLIDAY_DATE ) = '25' THEN 1 ELSE 0 END ) /*ChristDay*/
'ExcludeWkend&Hol'
April 9, 2009 at 12:53 pm
There is a MUCH easier way to calculate business days between two dates. Create a calendar table, and pre-populate the data into it, then just select the rowcount between the dates, with whatever Where clause you want to exclude.
It's faster, easier, more accurate, etc. Just needs a little maintenance to make sure it stays up-to-date. Even in that regard, it's easier to set someone up to maintain data in a table than it is to set someone up to maintain dates or date-forumlae in a piece of code.
create table dbo.Calendar (
Date datetime primary key,
constraint CK_JustTheDate check (Date = dateadd(day, datediff(day, 0, Date), 0)),
DayOfWeek tinyint,
Holiday bit not null default(0));
Then just plug in a decade of dates (or more/less depending on your needs), and update the holidays and days of the week. If you have a Numbers/Tally table, the initial population should take a few seconds, using DateAdd(Day) with the numbers in that.
Then, to get the number of business days:
select count(*) as BusinessDays
from dbo.Calendar
where Date between @MyFirstDate and @MyLastDate
and DayOfWeek between 2 and 6 -- or whatever your server and location uses
and Holiday = 0;
If you do that a lot, add an index on DayOfWeek and Holiday.
You can, of course, make it more complex, and potentially more useful, by changing Holiday from a bit to a field that references a table of holidays, so you can easily see which holiday(s) are between two dates, and so on. You can change DayOfWeek to char(3) and use "Mon", "Tue", "Wed", etc., instead of 1-7.
Can even be expanded to easily deal with international needs, where holidays might location-specific.
There are lots of other uses for Calendar tables. This is just the most basic and common one. Usually takes about an hour to set one up properly, including all holidays. Well worth the time spent on it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 20, 2009 at 8:00 am
I figured it out with your response.
Thanks,
AKC
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply