December 7, 2011 at 12:49 am
I have a table that contains stat holidays for a company, like so:
DECLARE @StatHoliday TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[StatDate] [date] NOT NULL,
[Title] [varchar](150) NOT NULL,
[OccursYearly] [bit] NULL)
INSERT INTO @StatHoliday (StatDate, Title, OccursYearly) VALUES ('12-25-2011', 'Xmas', 'True');
The OccursYearly flag can be set so the user doesn't have to enter the dates in every year.
I am trying to calculate the number of business days between 2 dates. I don't want to include Saturday or Sunday in the calculation, or any dates that exist in the @StatHoliday table.
I was thinking something like this:
declare @startdate date = '12-22-2012'
declare @enddate date = '12-26-2012'
SELECT
(DATEDIFF(dd, @StartDate, @EndDate) + 1)
-(DATEDIFF(wk, @StartDate, @EndDate) * 2)
-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)
- (SELECT COUNT(*) FROM @StatHoliday WHERE (StatDate BETWEEN @startdate AND @enddate) AND DATENAME(dw, StatDate) <> 'Saturday' AND DATENAME(dw, statdate) <> 'Sunday')
In this example I am trying to calculate the # of business days over christmas (22nd to 26th) in 2012.
The example returns 3 days, but I need it to include the stat holiday (the 25th) in 2012. So it would return 2 days.
if I could ignore the year in the start and enddates and check that OccursYearly is true, that would work.
Then I could use WHERE StatDate BETWEEN @startdate AND @enddate AND OccursYearly = 'True'.
Any suggestions on how to do this?
Thanks!
December 7, 2011 at 11:07 am
Thanks BrainDonor.
I did search before posting and was able to figure most of the issue out.
The part I am struggling with is determining how to ignore the year when the OccursYearly bit is true.
In the example above I am querying from 12-22-2012 to 12-26-2012. This returns a count of 3 days business days.
I am trying to subtract the # of stat days from a query to the StatHoliday table like this:
(SELECT COUNT(*) FROM @StatHoliday WHERE (StatDate BETWEEN @startdate AND @enddate) AND DATENAME(dw, StatDate) <> 'Saturday' AND DATENAME(dw, statdate) <> 'Sunday')
The problem is that the StatDate is 2011, which then get's filtered out by the 'StatDate BETWEEN @startdate AND @enddate' clause.
However, the OccursYearly flag is true so it needs to be included in the query results.
Something like:
'StatDate BETWEEN @startdate AND @enddate OR OccursYearly = 'True''
This doesn't work as all items that have OccursYearly set to 'True' are returned. The month and day need to be taken into account, but the year can be ignored.
December 7, 2011 at 12:18 pm
Bubs,
Your best bet in this is to create an actual calendar table. Search calendar table on the forum and you'll come up with some articles I wrote and some other very fine articles on the subject. Having a proper calendar table projected out several years makes this sort of query trivial. In the table you can have a flag for IsBusDate for each day.
If you make a mistake initially on which days are holidays, you can just edit the table and fix it in the table.
Then all queries looking for number of business days look essentially the same.
Todd Fifield
December 7, 2011 at 6:03 pm
I am trying to calculate the number of business days between 2 dates. I don't want to include Saturday or Sunday in the calculation, or any dates that exist in the @StatHoliday table.
couple o' things - you are trying to count a number of days from ether - you'll need an actual set of days with which to compare against your holiday table, i'll assume that you have real data that you'll be comparing. For this purpose, i'll use a recursive cte to get some dates to work with.
the other - you can use a cheap string conversion on the dates to check against the holidays that occur yearly:
left(convert(varchar(10), DateValue,101),5) not in (select left(convert(varchar(10), StatDate,101),5) from @StatHoliday where OccursYearly =1)
all together:
--set up variables for date range
declare @startdate datetime , @enddate datetime
set @startdate = '2012-12-22'
set @enddate = '2012-12-26'
;
--set up holidays table
DECLARE @StatHoliday TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[StatDate] [datetime] NOT NULL,
[Title] [varchar](150) NOT NULL,
[OccursYearly] [bit] NULL)
--add row(s) to holiday table
INSERT INTO @StatHoliday (StatDate, Title, OccursYearly) VALUES ('12-25-2011', 'Xmas', 'True')
;
--get all non-holiday business dates
--dumped a list of dates into a temp table. you don't really have dates to select from in your sample.
with datestemp as
(
select @startdate DateValue
union all
select DateValue + 1
from datestemp
where DateValue + 1 < @EndDate + 1
)
select
DateValue
,DayOfWeekInt=DATEPART(DW,DateValue)
into #BusDates
from datestemp
where
DATEPART(DW,DateValue) NOT IN (1,7) --SUN = 1, SAT = 7 --adjust for your server setting
and
--this is a way to compare the holidays that occur yearly - sloppily with a string conversion
left(convert(varchar(10), DateValue,101),5) not in (select left(convert(varchar(10), StatDate,101),5) from @StatHoliday where OccursYearly =1)
OPTION (MAXRECURSION 0)
;
SELECT * FROM #BusDates
DROP TABLE #BusDates
Returns:
DateValueDayOfWeekInt
2012-12-24 00:00:00.0002
2012-12-26 00:00:00.0004
December 8, 2011 at 7:44 am
I think I'd vote with Todd and have an actual dates table that gets built out for the next few years because what about holidays that don't fall on the same day each year and even holidays that do but fall on a Saturday or Sunday so the company adjusts the day off. For example, Christmas is on Sunday this year so a company might make the Friday before or Monday after a holiday. It does present a bit of management to populate the table but would make queries simpler in the long run.
Cliff
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply