June 6, 2018 at 12:12 pm
so i'm newish to SQL and SSRS but i'm working on a report that allows me to calculate average times for the resolution of tickets in out ticketing system. the main issue is that the tables don;t account for our business hours. so when is see the total time a ticket was in active it will show lets say 100,000 mins because the clock keeps running in SQL after our day is done.
so i have the below statement which produces the below results, how to i run a function or add to this statement to carve out business hours, our service desk is 6 am to 6 pm, no weekends
SELECT DISTINCT
IncidentStatusDurationFactvw.TotalTimeMeasure, IncidentDimvw.Id, SLAConfigurationDimvw.DisplayName, IncidentStatusvw.IncidentStatusValue, IncidentStatusDurationFactvw.StartDateTime,
IncidentStatusDurationFactvw.FinishDateTime
FROM IncidentStatusvw FULL OUTER JOIN
IncidentStatusDurationFactvw ON IncidentStatusvw.IncidentStatusId = IncidentStatusDurationFactvw.IncidentStatusId FULL OUTER JOIN
IncidentDimvw FULL OUTER JOIN
WorkItemDimvw ON IncidentDimvw.EntityDimKey = WorkItemDimvw.EntityDimKey FULL OUTER JOIN
SLAInstanceInformationFactvw ON WorkItemDimvw.WorkItemDimKey = SLAInstanceInformationFactvw.WorkItemDimKey FULL OUTER JOIN
SLAConfigurationDimvw ON SLAInstanceInformationFactvw.SLAConfigurationDimKey = SLAConfigurationDimvw.SLAConfigurationDimKey ON
IncidentStatusDurationFactvw.IncidentDimKey = IncidentDimvw.IncidentDimKey
where (IncidentDimvw.ResolvedDate BETWEEN '20180501' AND '20180530') and (SLAConfigurationDimvw.DisplayName = 'p2 Incident SLO') and (IncidentStatusDurationFactvw.TotalTimeMeasure != '0') AND (IncidentStatusvw.IncidentStatusValue != 'resolved')
order by Id
TotalTime Id DisplayName IncidentStatus StartDateTime FinishDateTime
37335 IR702681 P2 Incident SLO Pending 2018-04-27 22:06:48.227 2018-05-23 20:21:10.320
105212 IR702681 P2 Incident SLO Active 2018-02-13 20:34:38.357 2018-04-27 22:06:48.227
13317 IR774007 P2 Incident SLO Pending 2018-04-23 12:45:21.973 2018-05-02 18:42:01.283
19999 IR774007 P2 Incident SLO Active 2018-04-09 15:26:43.180 2018-04-23 12:45:21.973
8362 IR777568 P2 Incident SLO Active 2018-04-12 16:14:26.637 2018-04-18 11:36:04.780
18924 IR777568 P2 Incident SLO Pending 2018-04-18 11:36:04.780 2018-05-01 15:00:54.643
14378 IR779624 P2 Incident SLO Active 2018-04-17 22:29:22.257 2018-04-27 22:07:46.553
35982 IR779624 P2 Incident SLO Pending 2018-04-27 22:07:46.553 2018-05-22 21:49:27.470
8301 IR779941 P2 Incident SLO Pending 2018-04-26 19:03:46.200 2018-05-02 13:24:15.397
11768 IR779941 P2 Incident SLO Active 2018-04-18 14:55:48.920 2018-04-26 19:03:46.200
1710 IR783730 P2 Incident SLO Active 2018-04-24 18:10:48.573 2018-04-25 22:40:00.563
8219 IR783730 P2 Incident SLO Pending 2018-04-25 22:40:00.563 2018-05-01 15:39:50.720
3061 IR783936 P2 Incident SLO Active 2018-04-25 19:07:52.083 2018-04-27 22:08:16.670
15695 IR783936 P2 Incident SLO Pending 2018-04-27 22:08:16.670 2018-05-08 19:43:52.767
8395 IR786873 P2 Incident SLO Active 2018-05-01 21:20:28.267 2018-05-07 17:15:11.977
June 7, 2018 at 10:03 am
I had to do this once, and it's not all that easy. The first thing is to recognize that you are also going to need to recognize holidays, and without a calendar table, that's just not practical. In my case, the ticket might be assigned to different teams, and thus might not be covered under our SLA for those times either. Additionally, we had to measure the resulting resolution times against an SLA, and for each SLA there were different "business hours", so be sure to know exactly what your SLAs are, and exactly how they are different. I needed to derive the total resolution time down to the second in order to be able to have a consistent way to measure against all the different SLAs, and so a 1 hour SLA would mean 3600 seconds. Deriving time differences was something that I had to use SQL 2000 to do, without access to things like ROW_NUMBER() or LAG(). It was painful. However, I did have Crystal Reports handy, and thus not all of the calculation was being done in SQL Server. Most of it was calculated by Crystal by accumulating calculated time from each row that came in and had an effect on the resolution time. The key element is to be able to determine when each "event" occurs, and the duration of that event. Defining the meaning of "event" then becomes the bedrock of your calculation method. It's usually a good idea to actually map this out in a meeting with either fellow developers and/or your boss to be sure that the understanding of exactly what constitutes time against an SLA is fully understood by all the stake holders.
Thus this might be a LOT more complicated than you think at the moment. Trust me when I tell you that if your company starts to measure the IT department on the basis of the SLA, all of a sudden there will be a mad rush to negotiate exactly what constitutes that SLA, and exactly what all the rules are. IT will have the most to lose on this stuff.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 7, 2018 at 11:42 am
This is relatively straightforward (the difference between start time and end time, for selected hours only), but you'll need to post directly usable data. That is, CREATE TABLE and INSERT statements that we can run a query against, instead of just a splat of data on the screen. I'm already volunteering my time to write the query for you, I'm not gonna spend my time creating the test tables as well!
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
June 7, 2018 at 12:37 pm
You can create a function to calculate the durations for you.
The function below will calculate the time in seconds, using Mon-Fri 06h00 to 18h00.
However, it will not take holidays into account. For that you'd be better off with a calendar table.CREATE FUNCTION [dbo].[fn_ElapsedSeconds] (
/******************************************************
USAGE:
--------------------------------------------------------
-- Used with Variables ---------------------------------
DECLARE @StartDateTime AS datetime = '2018-06-04 09:15:22';
DECLARE @FinishDateTime AS datetime = '2018-06-04 11:23:19';
SELECT
StartDateTime = @StartDateTime
, FinishDateTime = @FinishDateTime
, TotalSeconds = sec.NumSeconds
, TotalTime = CAST(DATEADD(SECOND, sec.NumSeconds, 0) AS TIME(0)) -- NOTE - If the total time exceeds 24 hours, the days will be truncated.
FROM dbo.fn_ElapsedSeconds(@StartDateTime, @FinishDateTime) AS sec;
--------------------------------------------------------
-- Used with a table -----------------------------------
DECLARE @Tickets AS TABLE (
TicketNum int NOT NULL
, StartDateTime datetime NOT NULL
, FinishDateTime datetime NULL
);
INSERT INTO @Tickets (TicketNum, StartDateTime, FinishDateTime)
VALUES (1, '2018-06-04 10:15:22', '2018-06-04 11:23:19')
, (2, '2018-06-04 13:17:41', '2018-06-05 09:19:53')
, (3, '2018-06-04 14:27:11', NULL);
SELECT
t.TicketNum
, t.StartDateTime
, t.FinishDateTime
, TotalSeconds = sec.NumSeconds
, TotalTime = CAST(DATEADD(SECOND, sec.NumSeconds, 0) AS TIME(0)) -- NOTE - If the total time exceeds 24 hours, the days will be truncated.
FROM @Tickets AS t
OUTER APPLY dbo.fn_ElapsedSeconds(t.StartDateTime, t.FinishDateTime) AS sec
WHERE t.FinishDateTime IS NOT NULL;
******************************************************/
@StartDateTime AS datetime
, @FinishDateTime AS datetime
)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN
WITH
-- This is the base used to create the Virtual Tally Table below
T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0)
, (0),(0),(0),(0),(0),(0),(0),(0)
, (0),(0),(0),(0),(0),(0),(0),(0)
, (0),(0),(0),(0),(0),(0),(0),(0)
) AS X(N)
)
-- This builds a Virtual Tally Table of 1 to Number of days between @StartDateTime and @FinishDateTime
, Nums(rn) AS (SELECT TOP(DATEDIFF(DAY, @StartDateTime, @FinishDateTime) +1)
rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM T T1, T T2
)
-- This is the start end end time of each workday in the week
, cteDays(DayNum, IsWorkDay, StartTime, EndTime) AS (
SELECT DayNum, IsWorkDay, StartTime, EndTime
FROM (VALUES (0, 1, '06:00:00', '18:00:00') -- Monday
, (1, 1, '06:00:00', '18:00:00') -- Tuesday
, (2, 1, '06:00:00', '18:00:00') -- Wednesday
, (3, 1, '06:00:00', '18:00:00') -- Thursday
, (4, 1, '06:00:00', '18:00:00') -- Friday
, (5, 0, '06:00:00', '18:00:00') -- Saturday
, (6, 0, '06:00:00', '18:00:00') -- Sunday
) AS tabDays(DayNum, IsWorkDay, StartTime, EndTime)
WHERE IsWorkDay = 1
)
-- This calculates the Start and End Time of each day that the ticket was open.
, cteTimes AS (
SELECT
rn
, [Date] = DATEADD(DAY, DATEDIFF(DAY, 0, @StartDateTime), n.rn -1)
, DayNum = (DATEDIFF(DAY, 0, @StartDateTime) +n.rn -1) %7
, StartTime = CASE WHEN n.rn = 1 THEN CAST(@StartDateTime AS TIME(3)) ELSE d.StartTime END
, EndTime = CASE WHEN n.rn = DATEDIFF(DAY, @StartDateTime, @FinishDateTime) +1 THEN CAST(@FinishDateTime AS TIME(3)) ELSE d.EndTime END
FROM Nums AS n
INNER JOIN cteDays AS d
ON d.DayNum = (DATEDIFF(DAY, 0, @StartDateTime) +n.rn -1) %7
)
-- This sums up the total number of minutes that the ticket was open
SELECT NumSeconds = SUM(DATEDIFF(SECOND, t.StartTime, t.EndTime))
FROM cteTimes AS t;
GO
June 11, 2018 at 7:56 am
sgmunson - Thursday, June 7, 2018 10:03 AMI had to do this once, and it's not all that easy. The first thing is to recognize that you are also going to need to recognize holidays, and without a calendar table, that's just not practical. In my case, the ticket might be assigned to different teams, and thus might not be covered under our SLA for those times either. Additionally, we had to measure the resulting resolution times against an SLA, and for each SLA there were different "business hours", so be sure to know exactly what your SLAs are, and exactly how they are different. I needed to derive the total resolution time down to the second in order to be able to have a consistent way to measure against all the different SLAs, and so a 1 hour SLA would mean 3600 seconds. Deriving time differences was something that I had to use SQL 2000 to do, without access to things like ROW_NUMBER() or LAG(). It was painful. However, I did have Crystal Reports handy, and thus not all of the calculation was being done in SQL Server. Most of it was calculated by Crystal by accumulating calculated time from each row that came in and had an effect on the resolution time. The key element is to be able to determine when each "event" occurs, and the duration of that event. Defining the meaning of "event" then becomes the bedrock of your calculation method. It's usually a good idea to actually map this out in a meeting with either fellow developers and/or your boss to be sure that the understanding of exactly what constitutes time against an SLA is fully understood by all the stake holders.Thus this might be a LOT more complicated than you think at the moment. Trust me when I tell you that if your company starts to measure the IT department on the basis of the SLA, all of a sudden there will be a mad rush to negotiate exactly what constitutes that SLA, and exactly what all the rules are. IT will have the most to lose on this stuff.
Thank, yeah our IT department has been measured on the SLA for many years and this is the problem the metrics have been off because the ticketing system's reports and the ticketing system itself was not designed for averages. but try explaining that to a boss , "we can't give you this report because the system isn't designed like that" their answer is simple fix it. most of the time i can fix it but i know sql averages are beyond my skill set which is why this is a perfect learning experience. i put around 40 hours in trying to do this myself before i ended up here.
June 11, 2018 at 7:57 am
DesNorton - Thursday, June 7, 2018 12:37 PMYou can create a function to calculate the durations for you.The function below will calculate the time in seconds, using Mon-Fri 06h00 to 18h00.
However, it will not take holidays into account. For that you'd be better off with a calendar table.CREATE FUNCTION [dbo].[fn_ElapsedSeconds] (
/******************************************************
USAGE:
--------------------------------------------------------
-- Used with Variables ---------------------------------
DECLARE @StartDateTime AS datetime = '2018-06-04 09:15:22';
DECLARE @FinishDateTime AS datetime = '2018-06-04 11:23:19';SELECT
StartDateTime = @StartDateTime
, FinishDateTime = @FinishDateTime
, TotalSeconds = sec.NumSeconds
, TotalTime = CAST(DATEADD(SECOND, sec.NumSeconds, 0) AS TIME(0)) -- NOTE - If the total time exceeds 24 hours, the days will be truncated.
FROM dbo.fn_ElapsedSeconds(@StartDateTime, @FinishDateTime) AS sec;--------------------------------------------------------
-- Used with a table -----------------------------------
DECLARE @Tickets AS TABLE (
TicketNum int NOT NULL
, StartDateTime datetime NOT NULL
, FinishDateTime datetime NULL
);INSERT INTO @Tickets (TicketNum, StartDateTime, FinishDateTime)
VALUES (1, '2018-06-04 10:15:22', '2018-06-04 11:23:19')
, (2, '2018-06-04 13:17:41', '2018-06-05 09:19:53')
, (3, '2018-06-04 14:27:11', NULL);SELECT
t.TicketNum
, t.StartDateTime
, t.FinishDateTime
, TotalSeconds = sec.NumSeconds
, TotalTime = CAST(DATEADD(SECOND, sec.NumSeconds, 0) AS TIME(0)) -- NOTE - If the total time exceeds 24 hours, the days will be truncated.
FROM @Tickets AS t
OUTER APPLY dbo.fn_ElapsedSeconds(t.StartDateTime, t.FinishDateTime) AS sec
WHERE t.FinishDateTime IS NOT NULL;******************************************************/
@StartDateTime AS datetime
, @FinishDateTime AS datetime
)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN
WITH
-- This is the base used to create the Virtual Tally Table below
T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0)
, (0),(0),(0),(0),(0),(0),(0),(0)
, (0),(0),(0),(0),(0),(0),(0),(0)
, (0),(0),(0),(0),(0),(0),(0),(0)
) AS X(N)
)
-- This builds a Virtual Tally Table of 1 to Number of days between @StartDateTime and @FinishDateTime
, Nums(rn) AS (SELECT TOP(DATEDIFF(DAY, @StartDateTime, @FinishDateTime) +1)
rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM T T1, T T2
)-- This is the start end end time of each workday in the week
, cteDays(DayNum, IsWorkDay, StartTime, EndTime) AS (
SELECT DayNum, IsWorkDay, StartTime, EndTime
FROM (VALUES (0, 1, '06:00:00', '18:00:00') -- Monday
, (1, 1, '06:00:00', '18:00:00') -- Tuesday
, (2, 1, '06:00:00', '18:00:00') -- Wednesday
, (3, 1, '06:00:00', '18:00:00') -- Thursday
, (4, 1, '06:00:00', '18:00:00') -- Friday
, (5, 0, '06:00:00', '18:00:00') -- Saturday
, (6, 0, '06:00:00', '18:00:00') -- Sunday
) AS tabDays(DayNum, IsWorkDay, StartTime, EndTime)
WHERE IsWorkDay = 1
)
-- This calculates the Start and End Time of each day that the ticket was open.
, cteTimes AS (
SELECT
rn
, [Date] = DATEADD(DAY, DATEDIFF(DAY, 0, @StartDateTime), n.rn -1)
, DayNum = (DATEDIFF(DAY, 0, @StartDateTime) +n.rn -1) %7
, StartTime = CASE WHEN n.rn = 1 THEN CAST(@StartDateTime AS TIME(3)) ELSE d.StartTime END
, EndTime = CASE WHEN n.rn = DATEDIFF(DAY, @StartDateTime, @FinishDateTime) +1 THEN CAST(@FinishDateTime AS TIME(3)) ELSE d.EndTime END
FROM Nums AS n
INNER JOIN cteDays AS d
ON d.DayNum = (DATEDIFF(DAY, 0, @StartDateTime) +n.rn -1) %7
)
-- This sums up the total number of minutes that the ticket was open
SELECT NumSeconds = SUM(DATEDIFF(SECOND, t.StartTime, t.EndTime))
FROM cteTimes AS t;
GO
thanks i'm trying to do the calendar table but i'm not sure how to insert my sql statement into the calendar table or edit my statement to use the calendar table.
June 11, 2018 at 8:35 am
madmilitia - Monday, June 11, 2018 7:57 AMDesNorton - Thursday, June 7, 2018 12:37 PMYou can create a function to calculate the durations for you.The function below will calculate the time in seconds, using Mon-Fri 06h00 to 18h00.
However, it will not take holidays into account. For that you'd be better off with a calendar table.CREATE FUNCTION [dbo].[fn_ElapsedSeconds] (
/******************************************************
USAGE:
--------------------------------------------------------
-- Used with Variables ---------------------------------
DECLARE @StartDateTime AS datetime = '2018-06-04 09:15:22';
DECLARE @FinishDateTime AS datetime = '2018-06-04 11:23:19';SELECT
StartDateTime = @StartDateTime
, FinishDateTime = @FinishDateTime
, TotalSeconds = sec.NumSeconds
, TotalTime = CAST(DATEADD(SECOND, sec.NumSeconds, 0) AS TIME(0)) -- NOTE - If the total time exceeds 24 hours, the days will be truncated.
FROM dbo.fn_ElapsedSeconds(@StartDateTime, @FinishDateTime) AS sec;--------------------------------------------------------
-- Used with a table -----------------------------------
DECLARE @Tickets AS TABLE (
TicketNum int NOT NULL
, StartDateTime datetime NOT NULL
, FinishDateTime datetime NULL
);INSERT INTO @Tickets (TicketNum, StartDateTime, FinishDateTime)
VALUES (1, '2018-06-04 10:15:22', '2018-06-04 11:23:19')
, (2, '2018-06-04 13:17:41', '2018-06-05 09:19:53')
, (3, '2018-06-04 14:27:11', NULL);SELECT
t.TicketNum
, t.StartDateTime
, t.FinishDateTime
, TotalSeconds = sec.NumSeconds
, TotalTime = CAST(DATEADD(SECOND, sec.NumSeconds, 0) AS TIME(0)) -- NOTE - If the total time exceeds 24 hours, the days will be truncated.
FROM @Tickets AS t
OUTER APPLY dbo.fn_ElapsedSeconds(t.StartDateTime, t.FinishDateTime) AS sec
WHERE t.FinishDateTime IS NOT NULL;******************************************************/
@StartDateTime AS datetime
, @FinishDateTime AS datetime
)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN
WITH
-- This is the base used to create the Virtual Tally Table below
T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0)
, (0),(0),(0),(0),(0),(0),(0),(0)
, (0),(0),(0),(0),(0),(0),(0),(0)
, (0),(0),(0),(0),(0),(0),(0),(0)
) AS X(N)
)
-- This builds a Virtual Tally Table of 1 to Number of days between @StartDateTime and @FinishDateTime
, Nums(rn) AS (SELECT TOP(DATEDIFF(DAY, @StartDateTime, @FinishDateTime) +1)
rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM T T1, T T2
)-- This is the start end end time of each workday in the week
, cteDays(DayNum, IsWorkDay, StartTime, EndTime) AS (
SELECT DayNum, IsWorkDay, StartTime, EndTime
FROM (VALUES (0, 1, '06:00:00', '18:00:00') -- Monday
, (1, 1, '06:00:00', '18:00:00') -- Tuesday
, (2, 1, '06:00:00', '18:00:00') -- Wednesday
, (3, 1, '06:00:00', '18:00:00') -- Thursday
, (4, 1, '06:00:00', '18:00:00') -- Friday
, (5, 0, '06:00:00', '18:00:00') -- Saturday
, (6, 0, '06:00:00', '18:00:00') -- Sunday
) AS tabDays(DayNum, IsWorkDay, StartTime, EndTime)
WHERE IsWorkDay = 1
)
-- This calculates the Start and End Time of each day that the ticket was open.
, cteTimes AS (
SELECT
rn
, [Date] = DATEADD(DAY, DATEDIFF(DAY, 0, @StartDateTime), n.rn -1)
, DayNum = (DATEDIFF(DAY, 0, @StartDateTime) +n.rn -1) %7
, StartTime = CASE WHEN n.rn = 1 THEN CAST(@StartDateTime AS TIME(3)) ELSE d.StartTime END
, EndTime = CASE WHEN n.rn = DATEDIFF(DAY, @StartDateTime, @FinishDateTime) +1 THEN CAST(@FinishDateTime AS TIME(3)) ELSE d.EndTime END
FROM Nums AS n
INNER JOIN cteDays AS d
ON d.DayNum = (DATEDIFF(DAY, 0, @StartDateTime) +n.rn -1) %7
)
-- This sums up the total number of minutes that the ticket was open
SELECT NumSeconds = SUM(DATEDIFF(SECOND, t.StartTime, t.EndTime))
FROM cteTimes AS t;
GOthanks i'm trying to do the calendar table but i'm not sure how to insert my sql statement into the calendar table or edit my statement to use the calendar table.
so i added my select avg() statment to the bottom of this function and got an error, (it was a syntax thing that i fixed) but then when i ran it the second time i got
Msg 2714, Level 16, State 3, Procedure fn_ElapsedSeconds, Line 46
There is already an object named 'fn_ElapsedSeconds' in the database.
Msg 242, Level 16, State 3, Line 2
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
June 11, 2018 at 9:21 am
DesNorton - Thursday, June 7, 2018 12:37 PMYou can create a function to calculate the durations for you.The function below will calculate the time in seconds, using Mon-Fri 06h00 to 18h00.
However, it will not take holidays into account. For that you'd be better off with a calendar table.
ok so after some time i got the function to create. i'm most likely doing this wrong but i added my select statement after your function and when i execute it gave me result. but those results were exactly the same. like it's ignoring the function.
so now that i have the function in there how to incorporate it into my select statement for that matter any new select statements i run in the future too.
June 11, 2018 at 1:09 pm
Change the CREATE FUNCTION to ALTER FUNCTION as you are now altering an existing function
June 11, 2018 at 1:21 pm
madmilitia - Monday, June 11, 2018 9:21 AMDesNorton - Thursday, June 7, 2018 12:37 PMYou can create a function to calculate the durations for you.The function below will calculate the time in seconds, using Mon-Fri 06h00 to 18h00.
However, it will not take holidays into account. For that you'd be better off with a calendar table.ok so after some time i got the function to create. i'm most likely doing this wrong but i added my select statement after your function and when i execute it gave me result. but those results were exactly the same. like it's ignoring the function.
so now that i have the function in there how to incorporate it into my select statement for that matter any new select statements i run in the future too.
There are 2 examples of how to use the function in the comments.
The 1st example is the use of the function with variables for a single calculation.
The 2nd example is for the use of the function against a table (or in your select statement).
SELECT DISTINCT
IncidentStatusDurationFactvw.TotalTimeMeasure
, IncidentDimvw.Id
, SLAConfigurationDimvw.DisplayName
, IncidentStatusvw.IncidentStatusValue
, IncidentStatusDurationFactvw.StartDateTime
, IncidentStatusDurationFactvw.FinishDateTime
, TotalSeconds = sec.NumSeconds
, TotalTime = CAST(DATEADD(SECOND, sec.NumSeconds, 0) AS TIME(0)) -- NOTE - If the total time exceeds 24 hours, the days will be truncated.
FROM IncidentStatusvw
FULL OUTER JOIN IncidentStatusDurationFactvw
ON IncidentStatusvw.IncidentStatusId = IncidentStatusDurationFactvw.IncidentStatusId
FULL OUTER JOIN IncidentDimvw
FULL OUTER JOIN WorkItemDimvw
ON IncidentDimvw.EntityDimKey = WorkItemDimvw.EntityDimKey
FULL OUTER JOIN SLAInstanceInformationFactvw
ON WorkItemDimvw.WorkItemDimKey = SLAInstanceInformationFactvw.WorkItemDimKey
FULL OUTER JOIN SLAConfigurationDimvw
ON SLAInstanceInformationFactvw.SLAConfigurationDimKey = SLAConfigurationDimvw.SLAConfigurationDimKey
ON IncidentStatusDurationFactvw.IncidentDimKey = IncidentDimvw.IncidentDimKey
OUTER APPLY dbo.fn_ElapsedSeconds(IncidentStatusDurationFactvw.StartDateTime, IncidentStatusDurationFactvw.FinishDateTime) AS sec
WHERE (IncidentDimvw.ResolvedDate BETWEEN '20180501' AND '20180530')
AND (SLAConfigurationDimvw.DisplayName = 'p2 Incident SLO')
AND (IncidentStatusDurationFactvw.TotalTimeMeasure != '0')
AND (IncidentStatusvw.IncidentStatusValue != 'resolved')
ORDER BY Id
June 12, 2018 at 8:37 am
DesNorton - Monday, June 11, 2018 1:21 PMmadmilitia - Monday, June 11, 2018 9:21 AMDesNorton - Thursday, June 7, 2018 12:37 PMYou can create a function to calculate the durations for you.The function below will calculate the time in seconds, using Mon-Fri 06h00 to 18h00.
However, it will not take holidays into account. For that you'd be better off with a calendar table.ok so after some time i got the function to create. i'm most likely doing this wrong but i added my select statement after your function and when i execute it gave me result. but those results were exactly the same. like it's ignoring the function.
so now that i have the function in there how to incorporate it into my select statement for that matter any new select statements i run in the future too.
There are 2 examples of how to use the function in the comments.
The 1st example is the use of the function with variables for a single calculation.
The 2nd example is for the use of the function against a table (or in your select statement)
ok i think this is working so i see the new total time column i'm not sure where or how it is working, but i'll learn that later. is there any way to make it do the avg of the new totaltime column.
i tried totaltime = avg(DATEADD(SECOND, sec.NumSeconds, 0) AS TIME(0))
and avg(totaltime) but i don't think i'm putting two and two together in understanding cast()
June 12, 2018 at 9:01 am
madmilitia - Tuesday, June 12, 2018 8:37 AMok i think this is working so i see the new total time column i'm not sure where or how it is working, but i'll learn that later. is there any way to make it do the avg of the new totaltime column.i tried totaltime = avg(DATEADD(SECOND, sec.NumSeconds, 0) AS TIME(0))
and avg(totaltime) but i don't think i'm putting two and two together in understanding cast()
Perhaps I am not understanding your requirements, but I see the AVG of a single record as being EQUAL to the record.
If you want an AVG, then you need to extract your dataset, and then run an AVG over the dataset.
June 12, 2018 at 10:17 am
DesNorton - Tuesday, June 12, 2018 9:01 AMmadmilitia - Tuesday, June 12, 2018 8:37 AMok i think this is working so i see the new total time column i'm not sure where or how it is working, but i'll learn that later. is there any way to make it do the avg of the new totaltime column.i tried totaltime = avg(DATEADD(SECOND, sec.NumSeconds, 0) AS TIME(0))
and avg(totaltime) but i don't think i'm putting two and two together in understanding cast()Perhaps I am not understanding your requirements, but I see the AVG of a single record as being EQUAL to the record.
If you want an AVG, then you need to extract your dataset, and then run an AVG over the dataset.
so i now get these results which is great cause i can see which tickets are taking way to long and i can explain a few things. however i would love it if i could slim this down and get the avg time of the total time column. normally i would run a select distinct AVG(IncidentStatusDurationFactvw.TotalTimeMeasure) and it would return me one number the average of that column. this is different though because this statement is running TotalTime = CAST(DATEADD(SECOND, sec.NumSeconds, 0) AS TIME(0))
TotalTimeMeasure Id DisplayName IncidentStatusValue StartDateTime FinishDateTime TotalSeconds TotalTime
105212 IR702681 P2 Incident SLO Active 2018-02-13 20:34:38.357 2018-04-27 22:06:48.227 2295130 13:32:10
19999 IR774007 P2 Incident SLO Active 2018-04-09 15:26:43.180 2018-04-23 12:45:21.973 422318 21:18:38
8362 IR777568 P2 Incident SLO Active 2018-04-12 16:14:26.637 2018-04-18 11:36:04.780 156098 19:21:38
14378 IR779624 P2 Incident SLO Active 2018-04-17 22:29:22.257 2018-04-27 22:07:46.553 344304 23:38:24
11768 IR779941 P2 Incident SLO Active 2018-04-18 14:55:48.920 2018-04-26 19:03:46.200 274078 04:07:58
1710 IR783730 P2 Incident SLO Active 2018-04-24 18:10:48.573 2018-04-25 22:40:00.563 59352 16:29:12
3061 IR783936 P2 Incident SLO Active 2018-04-25 19:07:52.083 2018-04-27 22:08:16.670 97224 03:00:24
8395 IR786873 P2 Incident SLO Active 2018-05-01 21:20:28.267 2018-05-07 17:15:11.977 158083 19:54:43
6918 IR787176 P2 Incident SLO Active 2018-05-02 19:47:11.740 2018-05-07 15:05:42.100 112711 07:18:31
1157 IR787239 P2 Incident SLO Active 2018-05-02 19:19:27.920 2018-05-03 14:36:20.940 26213 07:16:53
1177 IR787279 P2 Incident SLO Active 2018-05-02 19:51:35.860 2018-05-03 15:28:15.327 27400 07:36:40
1657 IR791455 P2 Incident SLO Active 2018-05-10 17:47:47.503 2018-05-11 21:24:47.543 56220 15:37:00
5992 IR791800 P2 Incident SLO Active 2018-05-11 14:55:09.883 2018-05-15 18:47:54.320 100365 03:52:45
5400 IR792093 P2 Incident SLO Active 2018-05-11 20:50:09.777 2018-05-15 14:50:58.080 64849 18:00:49
4173 IR792094 P2 Incident SLO Active 2018-05-11 20:50:09.760 2018-05-14 18:23:59.673 34430 09:33:50
5358 IR792095 P2 Incident SLO Active 2018-05-11 21:33:43.997 2018-05-15 14:51:45.673 62282 17:18:02
1306 IR793108 P2 Incident SLO Active 2018-05-14 17:35:40.940 2018-05-15 15:21:01.920 35121 09:45:21
1236 IR793121 P2 Incident SLO Active 2018-05-14 22:25:21.537 2018-05-15 19:01:03.970 30942 08:35:42
4157 IR793687 P2 Incident SLO Active 2018-05-15 21:55:48.960 2018-05-18 19:12:25.347 119797 09:16:37
926 IR794347 P2 Incident SLO Active 2018-05-16 22:41:02.583 2018-05-17 14:07:22.420 12380 03:26:20
1220 IR794743 P2 Incident SLO Active 2018-05-17 22:20:38.113 2018-05-18 18:40:13.183 29975 08:19:35
4100 IR794937 P2 Incident SLO Active 2018-05-18 20:54:45.193 2018-05-21 17:14:19.613 29974 08:19:34
June 12, 2018 at 10:23 am
To get the avg total time, you could do this:
DATEADD(SECOND, SUM(DATEDIFF(SECOND, 0, TotalTime)) / COUNT(TotalTime), 0)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
June 12, 2018 at 12:07 pm
This should give you the the AVG per day as well as the AVG for the full result set.SELECT DISTINCT
isdfv.TotalTimeMeasure
, idv.Id
, scdv.DisplayName
, isv.IncidentStatusValue
, isdfv.StartDateTime
, isdfv.FinishDateTime
, IncidentSeconds = sec.NumSeconds
, IncidentTime = CAST(DATEADD(SECOND, sec.NumSeconds, 0) AS datetime) -- NOTE - Changed to DateTime as your sample data has some long time gaps.
, AvgSeconds = AVG(sec.NumSeconds) OVER () -- This will give you the AVG seconds across the entire result set
, AvgDaySeconds = AVG(sec.NumSeconds) OVER (PARTITION BY CAST(isdfv.StartDateTime AS date)) -- This will give you the AVG seconds per day (based on the date of StartDateTime)
FROM IncidentStatusvw AS isv
FULL OUTER JOIN IncidentStatusDurationFactvw AS isdfv
ON isv.IncidentStatusId = isdfv.IncidentStatusId
FULL OUTER JOIN IncidentDimvw AS idv
FULL OUTER JOIN WorkItemDimvw AS widv
ON idv.EntityDimKey = widv.EntityDimKey
FULL OUTER JOIN SLAInstanceInformationFactvw AS sifv
ON widv.WorkItemDimKey = sifv.WorkItemDimKey
FULL OUTER JOIN SLAConfigurationDimvw AS scdv
ON sifv.SLAConfigurationDimKey = scdv.SLAConfigurationDimKey
ON isdfv.IncidentDimKey = idv.IncidentDimKey
OUTER APPLY dbo.fn_ElapsedSeconds(isdfv.StartDateTime, isdfv.FinishDateTime) AS sec
WHERE (idv.ResolvedDate BETWEEN '20180501' AND '20180530')
AND (scdv.DisplayName = 'p2 Incident SLO')
AND (isdfv.TotalTimeMeasure != '0')
AND (isv.IncidentStatusValue != 'resolved')
ORDER BY idv.Id
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply