January 29, 2011 at 10:54 am
Hi
I am writing some code to calculate an end SLA expiry date for any given period. How it works is if the SLA start is in either tuesday, wendnesday, thursday add 24 hours to the SLA Start. If the SLA start is on a friday before 18.00 add 86 hours (as the SLA clock stops between 18.00 saturday and 08.00 monday). If the SLA time is on a friday after 18.00 the code firstly sets the time back to friday 00.00 then adds 104 hours to make the SLA expiry 08.00 tuesday. If the SLA date is on a Saturday it changes the time back to 00.00 on saturday then adds 80 hours (making the SLA expiry time tuesday morning at 08.00) If the SLA date is on a Sunday the code first changes the code back to 00.00 sunday and then adds 56 hours to make the SLA expiry time 08.00 tuesday. If the SLA start is on a monday before 08.00 the code changes the time back to 00.00 monday and then adds 32 hours making the SLA expiry 08.00 on tuesday. If the SLA start time is after 08.00 on monday the SLA expiry time is just + 24 hours.
The problem I have is that when I enter different dates in the code calculates all of the SLA expiry times correctly apart from a monday...I know the code is correct if I move the code to calculate the monday to a different part of the IF ELSE statement.
Here is the code I am using
declare @start datetime
declare @end datetime
declare @startofday float
declare @currenthours int
declare @endofday DECIMAL
declare @datetoadd int
declare @diff int
declare @timediff int
set @start = '2011-01-25 05:11:21.000'
set @endofday = 18.00
set @startofday = 8
--select @end = select case
IF DATENAME(dw, @start) IN ('Tuesday', 'Wednesday', 'Thursday')
Begin
set @end = DATEADD(Hour, 24, @Start)
END
ELSE
IF DATENAME(dw, @start) IN ('Sunday')
BEGIN
set @start = DATEADD(Day, DATEDIFF(Day, 0, @start), 0)
SET @end = DATEADD(Hour, 56, @Start)
END
ELSE
IF DATENAME(dw, @start) IN ('Saturday')
BEGIN
set @start = DATEADD(Day, DATEDIFF(Day, 0, @start), 0)
SET @end = DATEADD(Hour, 80, @Start)
END
ELSE
IF DATENAME(dw, @start) IN ('Friday')
Begin
SET @CurrentHours = CAST(@start - DATEADD(dd,0, DATEDIFF(dd,0,@start)) AS FLOAT) * 24
IF @CurrentHours < @endofday
BEGIN
set @end = DATEADD(Hour, 86, @Start)
END
ELSE
IF @CurrentHours >= @endofday
BEGIN
set @start = DATEADD(Day, DATEDIFF(Day, 0, @start), 0)
SET @end = DATEADD(Hour, 104, @Start)
END
ELSE
IF DATENAME(dw, @start) IN ('Monday')
Begin
SET @CurrentHours = CAST(@start - DATEADD(dd,0, DATEDIFF(dd,0,@start)) AS FLOAT) * 24
IF @CurrentHours >= @startofday
BEGIN
set @end = DATEADD(Hour, 24, @Start)
END
ELSE
IF @CurrentHours < @startofday
BEGIN
set @start = DATEADD(Day, DATEDIFF(Day, 0, @start), 0)
SET @end = DATEADD(Hour, 32, @Start)
END
END
END
--END
PRINT @END
January 30, 2011 at 5:14 am
I have found the solution...using a select case to get the days of the week and then using an IF ELSE to calculate the SLA times based upon the result of the select case
declare @end datetime
declare @start datetime
declare @period varchar(15)
set @start = '2011-01-12 19:59:00.000'
SET @period =
case
when DATENAME(dw, @start) IN ('Sunday')then 'Sunday'
when DATENAME(dw, @start) IN ('Saturday') then 'Saturday'
when DATENAME(dw, @start) IN ('Friday') and datepart(hour,@start) >= 18 then 'FridayNight'
when DATENAME(dw, @start) IN ('Friday') and datepart(hour,@start) < 18 then 'FridayDay'
WHEN DATENAME(dw, @start) IN ('Tuesday', 'Wednesday', 'Thursday') then 'Midweek'
when DATENAME(dw, @start) IN ('Monday') and datepart(hour,@start) < 8.00 then 'MondayMorning'
when DATENAME(dw, @start) IN ('Monday') and datepart(hour,@start) >= 8.00 then 'MondayDay'
END
PRINT @period
IF @period = 'Midweek'
BEGIN
set @end = DATEADD(Hour, 24, @Start)
END
ELSE IF @period = 'Saturday'
begin
set @start = DATEADD(Day, DATEDIFF(Day, 0, @start), 0)
SET @end = DATEADD(Hour, 80, @Start)
END
ELSE IF @period = 'Sunday'
BEGIN
set @start = DATEADD(Day, DATEDIFF(Day, 0, @start), 0)
SET @end = DATEADD(Hour, 56, @Start)
END
ELSE IF @period = 'MondayMorning'
BEGIN
set @start = DATEADD(Day, DATEDIFF(Day, 0, @start), 0)
SET @end = DATEADD(Hour, 32, @Start)
END
ELSE IF @period = 'MondayDay'
BEGIN
SET @end = DATEADD(Hour, 24, @Start)
END
ELSE IF @period = 'FridayDay'
BEGIN
set @end = DATEADD(Hour, 86, @Start)
END
ELSE IF @period = 'FridayNight'
BEGIN
set @start = DATEADD(Day, DATEDIFF(Day, 0, @start), 0)
SET @end = DATEADD(Hour, 104, @Start)
END
PRINT @END
April 8, 2012 at 12:26 pm
Hi, did you manage to get your code working ? I need some input for an internal SLA calculation and would like an example of your code and sql if possible.
April 8, 2012 at 9:46 pm
If you don't need @period for anything, you can do it this way.
declare @end datetime
declare @start datetime
set @start = '2011-01-12 19:59:00.000'
IF DATENAME(dw, @start) IN ('Sunday')
select @start = DATEADD(Day, DATEDIFF(Day, 0, @start), 0), @end = DATEADD(Hour, 56, @Start)
ELSE IF DATENAME(dw, @start) IN ('Saturday')
select @start = DATEADD(Day, DATEDIFF(Day, 0, @start), 0), @end = DATEADD(Hour, 80, @Start)
ELSE IF DATENAME(dw, @start) IN ('Friday') and datepart(hour,@start) >= 18
select @start = DATEADD(Day, DATEDIFF(Day, 0, @start), 0), @end = DATEADD(Hour, 104, @Start)
ELSE IF DATENAME(dw, @start) IN ('Friday') and datepart(hour,@start) < 18
select @end = DATEADD(Hour, 86, @Start)
ELSE IF DATENAME(dw, @start) IN ('Tuesday', 'Wednesday', 'Thursday')
select @end = DATEADD(Hour, 24, @Start)
ELSE IF DATENAME(dw, @start) IN ('Monday') and datepart(hour,@start) < 8.00
select @start = DATEADD(Day, DATEDIFF(Day, 0, @start), 0), @end = DATEADD(Hour, 32, @Start)
ELSE IF DATENAME(dw, @start) IN ('Monday') and datepart(hour,@start) >= 8.00
SELECT @end = DATEADD(Hour, 24, @Start)
Looks cleaner to me.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 9, 2012 at 3:52 am
Thanx, works like a charm.
April 9, 2012 at 10:28 am
Part of the problem is that the OP specified a particular route to the destination when only the destination is important. There are really only three conditions:
Between Friday at 18:00 and Monday at 08:00
Between Monday at 08:00 and Friday at 00:00
Between Friday at 00:00 and 18:00
The CTEs just set up a tally table and a set of dates and times to test on. I used three hour increments.
WITH Numbers(n) AS (
SELECT TOP 64 ROW_NUMBER() OVER( ORDER BY Table_Schema ) - 1
FROM Information_Schema.Columns
)
, SLA AS (
SELECT DATEADD(HOUR, 3*n, '2012-04-06') AS SLAStart
FROM Numbers
)
SELECT SLAStart
, CASE
WHEN SLAStart < DATEADD(WEEK, DATEDIFF(HOUR, '1900-01-05 18:00', SLAStart)/24/7, '1900-01-08 08:00')
THEN DATEADD(WEEK, DATEDIFF(HOUR, '1900-01-05 18:00', SLAStart)/24/7, '1900-01-09 08:00')
WHEN SLAStart < DATEADD(WEEK, DATEDIFF(HOUR, '1900-01-05 08:00', SLAStart)/24/7, '1900-01-12')
THEN DATEADD(DAY, 1, SLAStart)
ELSE
DATEADD(HOUR, 86, SLAStart)
END AS SLAExpires
--, Dates.*
FROM SLA
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply