August 2, 2012 at 9:07 am
I have a table called dbo.Incidents and i need to get the time difference in seconds between the fields Open_Time and Close_Time and return either a 0 or a 1 if the make their time target based on their Priority_Code, i have all this working but now i need to insert the time difference in seconds and the field that will contain the 0 or 1 back into the the incidents table in the right row, Number is the field that identifies each row. I would like the new fields to be called Seconds_Until_Resolution and SLA_Hit_Miss (for the 0 or 1 field)
I have this written so far:
SELECT [SLA_Hit_Miss] =
CASE
WHEN PRIORITY_CODE = '1' AND ResolvedSeconds <= 14400 THEN 1
WHEN PRIORITY_CODE = '2' AND ResolvedSeconds <= 28800 THEN 1
WHEN PRIORITY_CODE = '3' AND ResolvedSeconds <= 259200 THEN 1
WHEN PRIORITY_CODE = '4' AND ResolvedSeconds <= 2592000 THEN 1
ELSE 0
END,
*
FROM (SELECT DATEDIFF(second, OPEN_TIME, CLOSE_TIME) AS [Seconds_Until_Resolution], PRIORITY_CODE
FROM dbo.Incidents)
dtFinal
and it returns this:
SLA_Hit_Miss - Seconds_Until_Resolution - Priority_Code
1 - 1904 - 3
0 - 1191599 - 3
1 - 1250 - 3
0 - 757034 - 3
Please help me and thank you to anyone who helps! Im and intern and trying to get hired on full time so i would like to try and get this working. Thanks again
August 2, 2012 at 9:12 am
well, you could use a view on dbo.Incidents to automatically generate your calculation as a new column, or you could add a calculated, persisted column to the base tabe; both would do what you are asking, without the need for an insert/update;
would that be a possibility?
CREATE VIEW VW_Incidents
AS
SELECT [SLA_Hit_Miss] =
CASE
WHEN PRIORITY_CODE = '1' AND ResolvedSeconds <= 14400 THEN 1
WHEN PRIORITY_CODE = '2' AND ResolvedSeconds <= 28800 THEN 1
WHEN PRIORITY_CODE = '3' AND ResolvedSeconds <= 259200 THEN 1
WHEN PRIORITY_CODE = '4' AND ResolvedSeconds <= 2592000 THEN 1
ELSE 0
END,
*
FROM (SELECT DATEDIFF(second, OPEN_TIME, CLOSE_TIME) AS [Seconds_Until_Resolution], PRIORITY_CODE
FROM dbo.Incidents)
dtFinal
or
ALTER TABLE dbo.Incidents ADD [Seconds_Until_Resolution] AS DATEDIFF(second, OPEN_TIME, CLOSE_TIME) PERSISTED
Lowell
August 2, 2012 at 10:41 am
Thanks very much for the reply but a co-worker and i came up with this and it works great, we already have hundreds of views and we don't want to add any more if not necessary, thanks for you solutions though!!!
/***
*Updates dbo.Incidents Table with the Secunds_Until_Resolution and SLA_Hit_Miss
***/
Update dbo.Incidents
Set SLA_Hit_Miss = inc.SLA_Hit_Miss, Seconds_Until_Resolution = inc.Seconds_Until_Resolution
From
(
SELECT [SLA_Hit_Miss] =
CASE
WHEN PRIORITY_CODE = '1' AND Seconds_Until_Resolution <= 14400 THEN 1
WHEN PRIORITY_CODE = '2' AND Seconds_Until_Resolution <= 28800 THEN 1
WHEN PRIORITY_CODE = '3' AND Seconds_Until_Resolution <= 259200 THEN 1
WHEN PRIORITY_CODE = '4' AND Seconds_Until_Resolution <= 2592000 THEN 1
ELSE 0
END,
*
FROM (SELECT DATEDIFF(second, OPEN_TIME, CLOSE_TIME) AS [Seconds_Until_Resolution], PRIORITY_CODE, Number
FROM dbo.Incidents)
dtFinal
) inc
Where Incidents.Number = inc.Number
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply