March 19, 2004 at 6:56 am
I need help on subtracting weekends and company holidays for a date range
starting 12/01/2003 through 12/31/2003.
Select
Datediff(dd,MailRecd,MailReply)
As NumberDays
from tblMail
-- MailRecd has a value of 12/01/2003
-- MailReply has a value of 12/31/2003
-- The company holidays I need to subtract
are 12/23/2003 through 12/25/2003
March 19, 2004 at 7:22 am
This is one of many ways to do it. Very simplified and really depends on the overall use and palnned implementation as to what is the best way to handle.
Either go from this example or go into more detail on what you are trying to accomplish, the number of times it will run, how often, etc.
/* Don't want to see all those 1 record affected messages. */
SET NOCOUNT ON
/* Verify our temp table does not already exist for this connection, if does drop.*/
IF OBJECT_ID('tempDb.dbo.#tblDates') IS NOT NULL
DROP TABLE #tblDates
/* Build our temp table. */
CREATE TABLE #tblDates (
[DateVal] [datetime] NOT NULL PRIMARY KEY,
[IsWkEnd] AS (case when ((datepart(weekday,[DateVal]) = 7 or datepart(weekday,[DateVal]) = 1)) then 1 else 0 end)
)
/* These may move or additional added based on how implemented. */
DECLARE @strt_dte datetime,
@end_dte datetime
/* Initialization of data variables, may change based on implementation. */
SET @strt_dte = '20031201'
SET @end_dte = '20031231'
/* Loop thru range and populate our temp data table. */
WHILE @strt_dte <= @end_dte
BEGIN
INSERT #tblDates (DateVal) VALUES (@strt_dte)
SET @strt_dte = DATEADD(d,1,@strt_dte)
END
/* Get count of all days where not a weekend and not in a given range. Can change DateVal NO BETWEEN to DateVal Not In if you have a table with company holidays in it. */
SELECT COUNT(*) As BusDays FROM #tblDates WHERE IsWkEnd = 0 AND (DateVal NOT BETWEEN '20031223' AND '20031225')
/* Free used space in the tempdb by removing our temp table. */
IF OBJECT_ID('tempDb.dbo.#tblDates') IS NOT NULL
DROP TABLE #tblDates
March 22, 2004 at 8:48 am
Here's more detail:
Select
Datediff(dd,MailRecd,MailReply)
As NumberDays
from tblMail
There are approx. 100,000 records in
tblMail. The dates in MailRecd and MailReply range from 12/01/2003 to 01/31/2004.
Example of records from tblmail:
MailRecd MailReply
12/03/2003 12/04/2003
12/03/2003 12/08/2003
12/03/2003 12/31/2003
12/04/2003 01/28/2004
12/22/2003 01/05/2004
The MailRecd date is <= MailReply. I need to subtract weekends and company holidays
that fall within the range between MailRecd
and MailReply.
Company holidays are 12/23/2003 to 12/25/2003 and 01/01/2004 to 01/02/2004.
March 22, 2004 at 3:00 pm
suppose you have a Calendar table with Working days Marked as 1 and Weekends and company holidays marked as 0
EX:
Create table Calendar (Dte Datetime, Workday tinyint)
Populate that table with 2 -3 years according to your case and:
Select
Mailrecd
, MailReply
, Select Sum(Workday) From Calendar where Dte between Mailrecd and MailReply
From tblMail
* Noel
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply