carving out bussiness hours.

Question

carving out bussiness hours.

Viewing 6 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101202 More actions · Answer 1

DesNorton - Tuesday, June 12, 2018 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

Btw, why would you leave one day out of a month, viz: (idv.ResolvedDate BETWEEN '20180501' AND '20180530')?😀 That typo is a good reminder to never use "between" on date/datetime values, instead use: (idv.ResolvedDate >= '20180501' AND idv.ResolvedDate < '20180601')

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

madmilitia SSC Veteran Points: 295 More actions · Answer 2

ScottPletcher - Tuesday, June 12, 2018 1:04 PM
DesNorton - Tuesday, June 12, 2018 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
Btw, why would you leave one day out of a month, viz: (idv.ResolvedDate BETWEEN '20180501' AND '20180530')?😀 That typo is a good reminder to never use "between" on date/datetime values, instead use: (idv.ResolvedDate >= '20180501' AND idv.ResolvedDate < '20180601')

because those dates are going to be @startdate and @enddate and be in a SSRS report. i just have those random dates in there to test the statement before i make it into a SSRS report. 🙂

madmilitia SSC Veteran Points: 295 More actions · Answer 3

DesNorton - Tuesday, June 12, 2018 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

hmm this is starting to get further away from what i'm looking for, let me drop some screen shots of the reports that work. then you 'll see better what i'm trying to explain 🙂

so that is what i'm getting with business hours carved out which helps. but when i don't have to worry about business hours carved out i run this.
you can see when i run it like this with using the AVG() it does the math and retunes just the avg of the column.

so how do i run the below but get the TotalTime column to show in one cell with the average time of everything that returns.

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

DesNorton SSC-Insane Points: 24570 More actions · Answer 4

Try adding this line ...
, AvgSeconds = AVG(IncidentStatusDurationFactvw.TotalTimeMeasure) OVER ()

I do not have access to your data, so anything that I post is simply an educated guess.
You have not provided us with any consumable data, so my code is not tested.
You need to get an understanding of the code, rather than just using it verbatim.

How to post data/code on a forum to get the best help.

madmilitia SSC Veteran Points: 295 More actions · Answer 5

DesNorton - Monday, June 18, 2018 1:32 PM
Try adding this line ...
, AvgSeconds = AVG(IncidentStatusDurationFactvw.TotalTimeMeasure) OVER ()
I do not have access to your data, so anything that I post is simply an educated guess.
You have not provided us with any consumable data, so my code is not tested.
You need to get an understanding of the code, rather than just using it verbatim.

i'm trying to get an understanding of the code that's why i'm here, can you explain to me how the avgseconds = (code) works compared to the builtin function of do a select avg()

if i some how insulted you i'm sorry.

DesNorton SSC-Insane Points: 24570 More actions · Answer 6

madmilitia - Tuesday, June 19, 2018 10:33 AM
DesNorton - Monday, June 18, 2018 1:32 PM
Try adding this line ...
, AvgSeconds = AVG(IncidentStatusDurationFactvw.TotalTimeMeasure) OVER ()
I do not have access to your data, so anything that I post is simply an educated guess.
You have not provided us with any consumable data, so my code is not tested.
You need to get an understanding of the code, rather than just using it verbatim.
i'm trying to get an understanding of the code that's why i'm here, can you explain to me how the avgseconds = (code) works compared to the builtin function of do a select avg()
if i some how insulted you i'm sorry.

I am not insulted.

Now, the Microsoft documentation on the subject can be found here AVG (Transact-SQL)
The documentation for the OVER Clause (Transact-SQL) can be found here.

In a nutshell, The std AVG/MIN/MAX/etc functions are used in conjunction with a GROUP BY clause to get aggregated data at a granularity that matches the GROUP BY.
The addition of SQL Window Functions (OVER()) allows us to not do the same calculations on every row. The OVER( PARTITION BY) is equivalent to the GROUP BY in that it does a calculation across the result set, with the granularity of the PARTITION BY clause.

The best way to understand the code is to try it, and see what it does. Then change it and see what changes.

How to post data/code on a forum to get the best help.