Exclude Weekends.
-
How do I exclude Weekends in my query below? I need to return data that is 10 WORKING DAYS ONLY
DECLARE @StartDate CHAR(8), @EndDate CHAR(8)
SET @StartDate = '20040101'
SET @EndDate = '20050101'
UPDATE Orb SET NumberInStandard =
(SELECT COUNT(*) AS 'Total'
FROM claim_details cd INNER JOIN benefit_details bd ON cd.claim_key = bd.claim_key
WHERE CONVERT(CHAR(8), bd.secretariate_date, 112) BETWEEN @StartDate AND @EndDate
AND DATEDIFF(dd, bd.benefit_decision_date, bd.secretariate_date) < 10
AND bd.benefit_cat_code = 'RIH'
GROUP BY bd.benefit_cat_code)
-
Rather than copying and pasting a whole lot of links, go to the search for this site
http://www.sqlservercentral.com/search/turbo.asp
Enter "Business Days" and click all three check boxes. Peruse the results till you find something that suits your need
--------------------
Colt 45 - the original point and click interface
-
http://www.sqlservercentral.com/columnists/sjones/businessdays.asp
this is the exact article phillcart is talking about
My Blog:
http://dineshasanka.spaces.live.com/ -
The general idea is to subtract 2 days for the number of weeks between the dates. If you know that the start and end dates are always weekdays, that is the simplest approach. If you need something more robust, there have been several recent scripts published here to demonstrate it.
So long, and thanks for all the fish,
Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3
-
The following link http://www.sqlservercentral.com/columnists/sjones/businessdays.asp
has a brilliant article on how to calculate the number of business days between 2 dates. This is not what I'm trying to do.
In my original post the query needs to to do a DATEDIFF between 2 dates that is less than 10 Business Days. How do I do it given the link above?
Have I missed something obvious here? as I I'm having difficulty incorporating the above link with the problem I have with my query!
-
Define "Business days". Is it always just non-weekends ? What about public holidays ? Other custom definitions for your business ?
-
When I tackle a problem in T-SQL, I kind of deal with portions of it at at a time, cause if I think of it as a whole I get confused.
At the moment I'm trying to get the query right for excluding weekends only. I do have exclude public holidays as well but I just wanted to deal with the weekends part of it first.
-
for the public holidays, you need to keep them in a table. then you can modify the above function to work accordingly
My Blog:
http://dineshasanka.spaces.live.com/ -
>>I do have exclude public holidays as well but I just wanted to deal with the weekends part of it first.
Unfortunately this is a case where looking at a small portion of the problem would lead you to the wrong solution. Weekends can easily be resolved by a function, but as mentioned, once public holidays and other complexities start creeping into the whole "calendar" requirements area, you need to start looking at a Calendar table.
Each row of a calendar table would typically have the actual date, then a bunch of bit columns for the attributes you care about, typical columns being IsWeekend, IsHoliday, IsBossOnVacationSoICanSlackOff etc.
Of course, with the table comes the requirement to write a script to loop through a set of dates and populate the table, or at least the parts that can be set automatically.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply