April 9, 2012 at 12:26 pm
Updated with Test Data...
This may not be the correct place, but I am desperate for help with the calculation of a certain helpdesk log's RAG status based on a pre-defined SLA. I need to use this in ASP, but some of the calculations may also have to be made in SQL via a Stored Proc or a function. Any assistance would be appreciated as I just cannot get my head around this one.
Background:
I need to display the RAG status of a certain internal helpdesk log in either Green, Orange or Red. The colors are displayed by setting a variable, which corresponds to a specific image in aforementioned colors.
The RAG status works according to an SLA, which is defined on a specific table. I also have a
calendar table, which specifies "WORK/WEEKEND/PUBLIC HOLIDAY" and every date for past and the current year. (Updated every year for the next year from government published calendar).
The SLA states that a PRE-Warning period is reached at 5 hours before the SLA time is reached (Ex. Day_End - Day_Start or 17:00 - 08:00 = 9hrs). Therefore the PRE_Warning time is reached 9 - 5 = 4hrs after the call is logged. If the log is between PRE-Warning and SLA-Exceeded time, therefore after 4 hours have passed, but before 9 hours have passed (4 < now >= 9) then the RAG status would be Orange. Should the log exceed 9 hours, then the RAG status becomes Red.
The problem arrises in that the SLA is configurable to exclude weekends and public holidays and after
hours, but can also include any or all of the above.
The scenarios are thus as follows:
1. Daytime only: Mon to Fri 08:00 to 17:00
2. Daytime with After Hours: Mon - Fri 24 hrs
3. Daytime and Weekend: Mon - Sun 08:00 to 17:00
4. All Week plus After Hours (24/7): Mon - Sun 24 hrs
PRE_WARNING: 5 Hrs
Some Test Data:
USE mySLATest ;
GO
--===== Drop Temp Tables if they exist
IF OBJECT_ID(N'tempdb..#SLA_Def') IS NOT NULL
DROP TABLE #SLA_Def ;
IF OBJECT_ID(N'tempdb..#SLA_Calendar') IS NOT NULL
DROP TABLE #SLA_Calendar ;
IF OBJECT_ID(N'tempdb..#Tickets') IS NOT NULL
DROP TABLE #Tickets ;
--===== Now Create New Tables
CREATE TABLE [#SLA_Def]
( [SLAID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[SLAText] VARCHAR(50) NULL,
[VendorID] INT NULL,
[SLA_StartDate] DATETIME NULL,
[SLA_EndDate] DATETIME NULL,
[WorkDay_Start] DATETIME NULL,
[WorkDay_End] DATETIME NULL,
[DoAfterHours] BIT CONSTRAINT [DF_SLA_Def_DoAfterHours] DEFAULT ((0)),
[DoWeekends] BIT CONSTRAINT [DF_SLA_Def_DoWeekends] DEFAULT ((0))
)
CREATE TABLE [#SLA_Calendar]
( [CalID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[CalDate] DATETIME NOT NULL,
[CalType] VARCHAR(20) NULL,
[Country] VARCHAR(20) NULL
)
CREATE TABLE [#Tickets]
( [TicketID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[Heading] VARCHAR(50) NULL,
[VendorID] INT NOT NULL,
[DateLogged] DATETIME NOT NULL
)
--===== Insert some test data
INSERT INTO [#SLA_Def]
([SLAText], [VendorID], [SLA_StartDate], [SLA_EndDate], [WorkDay_Start], [WorkDay_End], [DoAfterHours], [DoWeekends])
VALUES('DayTime Only No AH', 1, '01 Jan 2012 00:00:00:000', '31 Dec 2012 23:59:59:000', '01 Jan 1900 08:00:00:000', '01 Jan 1900 17:00:00:000', 0, 0),
('DayTime with AH', 2, '01 Jan 2012 00:00:00:000', '31 Dec 2012 23:59:59:000', '01 Jan 1900 08:00:00:000', '01 Jan 1900 17:00:00:000', 1, 0),
('Include Weekend No AH', 3, '01 Jan 2012 00:00:00:000', '31 Dec 2012 23:59:59:000', '01 Jan 1900 08:00:00:000', '01 Jan 1900 17:00:00:000', 0, 1),
('Include Weekend with AH', 4, '01 Jan 2012 00:00:00:000', '31 Dec 2012 23:59:59:000', '01 Jan 1900 08:00:00:000', '01 Jan 1900 17:00:00:000', 1, 1) ;
INSERT INTO [#SLA_Calendar]
([CalDate], [CalType], [Country])
VALUES('01 Apr 2012 00:00:00:000', 'WEEKEND', 'USA'),
('02 Apr 2012 00:00:00:000', 'WORK', 'USA'),
('03 Apr 2012 00:00:00:000', 'WORK', 'USA'),
('04 Apr 2012 00:00:00:000', 'WORK', 'USA'),
('05 Apr 2012 00:00:00:000', 'WORK', 'USA'),
('06 Apr 2012 00:00:00:000', 'WORK', 'USA'),
('07 Apr 2012 00:00:00:000', 'WEEKEND', 'USA'),
('08 Apr 2012 00:00:00:000', 'WEEKEND', 'USA'),
('09 Apr 2012 00:00:00:000', 'WORK', 'USA'),
('10 Apr 2012 00:00:00:000', 'WORK', 'USA'),
('11 Apr 2012 00:00:00:000', 'WORK', 'USA'),
('12 Apr 2012 00:00:00:000', 'WORK', 'USA'),
('13 Apr 2012 00:00:00:000', 'WORK', 'USA'),
('14 Apr 2012 00:00:00:000', 'WEEKEND', 'USA'),
('15 Apr 2012 00:00:00:000', 'WEEKEND', 'USA'),
('16 Apr 2012 00:00:00:000', 'WORK', 'USA'),
('17 Apr 2012 00:00:00:000', 'WORK', 'USA'),
('18 Apr 2012 00:00:00:000', 'WORK', 'USA'),
('19 Apr 2012 00:00:00:000', 'WORK', 'USA'),
('20 Apr 2012 00:00:00:000', 'WORK', 'USA'),
('21 Apr 2012 00:00:00:000', 'WEEKEND', 'USA'),
('22 Apr 2012 00:00:00:000', 'WEEKEND', 'USA'),
('23 Apr 2012 00:00:00:000', 'WORK', 'USA'),
('24 Apr 2012 00:00:00:000', 'WORK', 'USA'),
('25 Apr 2012 00:00:00:000', 'WORK', 'USA'),
('26 Apr 2012 00:00:00:000', 'WORK', 'USA'),
('27 Apr 2012 00:00:00:000', 'PUBLIC HOLIDAY', 'USA'),
('28 Apr 2012 00:00:00:000', 'WEEKEND', 'USA'),
('29 Apr 2012 00:00:00:000', 'WEEKEND', 'USA'),
('30 Apr 2012 00:00:00:000', 'WORK', 'USA')
INSERT INTO [#Tickets]
([Heading], [VendorID], [DateLogged])
VALUES('Issue 1', 1, '04 Apr 2012 09:00'),
('Issue 2', 1, '04 Apr 2012 16:00'),
('Issue 3', 2, '06 Apr 2012 10:00'),
('Issue 4', 2, '06 Apr 2012 14:00'),
('Issue 5', 3, '08 Apr 2012 11:00'),
('Issue 6', 3, '08 Apr 2012 16:00'),
('Issue 7', 4, '09 Apr 2012 09:00'),
('Issue 8', 4, '09 Apr 2012 15:00')
SELECT * FROM [#SLA_Def]
SELECT * FROM [#SLA_Calendar]
SELECT * FROM [#Tickets]
SELECT [#Tickets].[Heading], [#Tickets].[VendorID], [#Tickets].[DateLogged], [#SLA_Def].[SLAText], [#SLA_Def].[VendorID], [#SLA_Def].[SLA_StartDate], [#SLA_Def].[SLA_EndDate], [#SLA_Def].[WorkDay_Start], [#SLA_Def].[WorkDay_End], [#SLA_Def].[DoAfterHours], [#SLA_Def].[DoWeekends]
FROM [#Tickets]
INNER JOIN [#SLA_Def] ON [#Tickets].[VendorID] = [#SLA_Def].[VendorID]
I have the following code to get the exceeded time for each log, depending on what time it was logged. http://www.sqlservercentral.com/Forums/FindPost1280035.aspx, but it only looks at defined hours and ignores the minutes. If the log was thus created at 16:30 on a weekday except a Friday, then the RAG Green status will be valid till 11:30 the next day as it cannot go past 17:00, unless it is Friday in which case it will be Mon 11:30, if Weekends and AH are excluded. Orange status would thus be till 11:30 + 5 Hrs after which it would become Red. The minutes and hours are therefor important except if it was logged over a weekend, in which case Mon 08:00 + 4Hrs is ok.
I however also need assistance with the above all in one. Maybe I have complicated the issue too much, but as I said, I just cannot wrap my head around this one.
An ASP/SQL solution would be best, but SQL only is also ok ?
April 9, 2012 at 12:52 pm
Please follow the following link to know more on how to post data in readily consumable format.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Without your data, we will have hard time figuring out what do you really want, given the length and descriptive natur of your post!
April 9, 2012 at 1:06 pm
What you need to add to this is an hours of the day table. It would list whether an hour is "work", "after", "overnight", or whatever business category an hour would fall into.
Then, you join that to your calendar table, and select the number of rows between now and when the ticket was issued. If you need to be more accurate than hours, create the table by minutes.
Either hours or minutes, you can include the categories in your Join criteria.
Here's a sample of how that can work:
USE ProofOfConcept ;
GO
IF OBJECT_ID(N'tempdb..#Tickets') IS NOT NULL
DROP TABLE #Tickets ;
IF OBJECT_ID(N'tempdb..#Calendar') IS NOT NULL
DROP TABLE #Calendar ;
IF OBJECT_ID(N'tempdb..#Hours') IS NOT NULL
DROP TABLE #Hours ;
CREATE TABLE #Tickets
(Issued DATETIME,
Category VARCHAR(25)) ;
INSERT INTO #Tickets
(Issued, Category)
VALUES ('2012-04-06 16:30:00', 'Regular'),
('2012-04-06 16:30', 'After Hours'),
('2012-04-06 16:30', 'Work the Weekend') ;
CREATE TABLE #Calendar
(DT DATETIME,
Category VARCHAR(25)) ;
INSERT INTO #Calendar
(DT, Category)
VALUES ('2012-04-06', 'Regular'),
('2012-04-07', 'Work the Weekend'),
('2012-04-08', 'Work the Weekend'),
('2012-04-09', 'Regular') ;
CREATE TABLE #Hours
(Hr INT,
Category VARCHAR(25)) ;
INSERT INTO #Hours
(Hr, Category)
VALUES (0, 'After Hours'),
(1, 'After Hours'),
(2, 'After Hours'),
(3, 'After Hours'),
(4, 'After Hours'),
(5, 'After Hours'),
(6, 'After Hours'),
(7, 'After Hours'),
(8, 'Regular'),
(9, 'Regular'),
(10, 'Regular'),
(11, 'Regular'),
(12, 'Regular'),
(13, 'Regular'),
(14, 'Regular'),
(15, 'Regular'),
(16, 'Regular'),
(17, 'Regular'),
(18, 'After Hours'),
(19, 'After Hours'),
(20, 'After Hours'),
(21, 'After Hours'),
(22, 'After Hours'),
(23, 'After Hours') ;
SELECT *
FROM #Tickets
CROSS APPLY (SELECT COUNT(*) AS Hrs
FROM #Calendar
CROSS JOIN #Hours
WHERE DATEADD(HOUR, Hr, DT) BETWEEN Issued AND GETDATE()
AND (#Tickets.Category = 'Regular'
AND #Calendar.Category = 'Regular'
AND #Hours.Category = 'Regular'
OR #Tickets.Category = 'Work the Weekend'
OR #Tickets.Category = 'After Hours'
AND #Calendar.Category = 'Regular')) AS DaysHours ;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 9, 2012 at 1:07 pm
Additional:
Change "GETDATE()" to a specific date and time, like 9 AM today. You'll see very different "hours counts" for the different categories of tickets.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 9, 2012 at 1:32 pm
Thanx for answering GSquared.
If I change the getdate to '2012-04-12 21:17' then I get the results below.
Issued Category Hrs
2012-04-06 16:30:00.000Regular 1
2012-04-06 16:30:00.000After Hours 7
2012-04-06 16:30:00.000Work the Weekend 53
I might be misunderstanding, but what I require back is that if the ticket issue time was 2012-04-06 16:30:00.000, then the call would have been in Green status until 2012-04-06 16:30:00.000 + 4 Hrs, which should equate to 2012-04-07 11:30:00.000 if no after hours or 2012-04-06 20:30:00.000 if using after hours. The ticket would be in Orange status if it was between 2012-04-07 11:30:00.000 and (2012-04-07 11:30:00.000 + 5 Hrs) = 2012-04-07 16:30:00.000 if no after hours or( 2012-04-06 20:30:00.000 + 5 Hrs) = 2012-04-08 01:30:00.000 if using after hours.
Since today's date is 2012-04-08 21:17:00.000, the ticket's time is way past the SLA of 9
hours from being issued, so the RAG status should automatically be "Red". (The above obviously excludes calulations for weekends etc as it is only an example.)
I'll update my original post with proper table data and some more examples.
Thanx again for your assistance so far.
April 12, 2012 at 6:20 am
All you need to do to build whatever rules you need is make sure the hours table has in it what you want for your rules. If, for example, "after hours" only extends till 10 PM, and doesn't start the next day till 8 AM, then don't include 11 PM, midnight, 1 AM, et al, just have the hours that match your rules. The "work the weekend" thing is just a sample to show what you can do with this.
It all comes down to categorizing the hours and days based on the rules that apply to you.
Then a simple count of the hours, as per the final query, gives you how "old" the ticket the is, per your rules.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply