December 12, 2002 at 7:05 am
I am trying to calculate a follow up date based on business days. I also need to take into consideration company holidays. Has anyone ever used a SQL function or formula to do this type of calculation.
December 12, 2002 at 7:47 am
You're going to be in custom code territory with a function like this. You might try creating a calendar table with all business dates in it, then select the N+1 row from your current date.
create table Businessdays(
Calendar datetime
RowId int
)
Populate Calendar with all your business days. Set ROWID to a sequential integer for each successive day. You can find the next business day by looking at your "calendar" column, getting its ROWID, then picking the ROWID+N for your follow up day.
December 27, 2004 at 7:10 pm
I need the same thing, I hope there are some examples
December 29, 2004 at 5:30 pm
This was posted 12/18.
I have used a lookup table.
CREATE TABLE dbo.BusinessDays (
BusinessDay datetime NOT NULL PRIMARY KEY CLUSTERED)
go
--Load the table with dates, starting with the first business day of the year:
TRUNCATE TABLE dbo.BusinessDays
declare @i smallint
SET @i = 0
WHILE @i < 365
BEGIN
INSERT INTO dbo.BusinessDays
SELECT DateAdd(dd, @i, '2005-01-03')
SET @i = @i + 1
END
-- add the sequence number
ALTER TABLE dbo.BusinessDays ADD DayNumber smallint IDENTITY (1, 1)
-- go back through the table manually and delete all the dates that are NOT business days, like weekends and holidays.
-- an example
declare @DateReceived datetime, @DateAssigned datetime, @ElapsedDays smallint
SET @DateReceived = '2005-01-13 8:00AM'
SET @DateAssigned = '2005-01-17 12:05PM'
SET @ElapsedDays =
(SELECT DayNumber FROM dbo.BusinessDays
WHERE BusinessDay = CONVERT( varchar(8), @DateAssigned, 112)) -
(SELECT DayNumber FROM dbo.BusinessDays
WHERE BusinessDay = CONVERT( varchar(8), @DateReceived, 112))
PRINT CAST(@ElapsedDays AS varchar(3))
If you need elapsed time as well, you are going to have to fiddle with it.
Luck, Dave
November 17, 2009 at 6:07 pm
My query is as follows:
I have a call tracking database with two smalldatetime fields. I would like to run some reports on this database (via SQL 2005 Reporting Services) using MS SQL 2005 / Query Analyser.
What I'm trying to extract is all the calls that haven't met a specific service level agreement (eg: all calls with a time difference of more than 4 hours between the two smalldatetime fields).
The logic holds true from Monday to Sunday, so no need to exclude weekends, etc.
Is there a simple query to achieve this?
Detailed examples of what I need is shown below (Business hours 8:30 AM - 6:00 PM):
Example1:
smalldatetime1 = 16/11/2009 9:00:00
smalldatetime2 = 17/11/2009 14:00:00
Result (business hours) = 14 hours and 30 mins
Example2:
smalldatetime1 = 16/11/2009 9:00:00
smalldatetime2 = 16/11/2009 12:00:00
Result (business hours) = 3 hours
Any help would be much appreciated.
Thanks heaps,
Sujit.
November 18, 2009 at 7:08 am
Sujizulu - this is a really old thread, try looking here, especially in the discussion surrounding the article:
http://www.sqlservercentral.com/columnists/jmoden/calculatingworkdays.asp
Article is specific to workdays in the US, but the lengthy discussion may help point you in the right direction if your needs are outside of that.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
November 18, 2009 at 4:21 pm
Thank you. Will check it out.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply