March 27, 2017 at 2:24 pm
Here is my sample data:
fy year month name report week end date outage_hr_decimal_calc remedy alg
FY17 Mar Mar 24th 2017-03-17 08:23:00 1.33 Critical
FY17 Mar Mar 24th 2017-03-17 09:00:00 0.40 High
FY17 Mar Mar 24th 2017-03-17 23:48:00 0.55 Medium
FY17 Mar Mar 24th 2017-03-23 09:01:00 2.10 High
FY17 Mar Mar 17th 2017-03-10 07:55:00 0.88 Critical
FY17 Mar Mar 17th 2017-03-14 08:05:00 0.41 Medium
FY17 Mar Mar 17th 2017-03-14 12:07:00 22.48 High
FY17 Mar Mar 17th 2017-03-16 12:45:00 0.58 Critical
I'm needing to calculate the average 'outage_hr_decimal_calc' where 'remedy alg' = critical or high for the most recent report week.
I'm thinking that if I use the max function to determine max 'end date', use the 'fy year', 'month name', and 'report week' from the max end date, then calculate the average where 'remedy alg' = critical or high.
Thanks in advance for your assistance!!
March 27, 2017 at 2:50 pm
crowegreg - Monday, March 27, 2017 2:24 PMHere is my sample data:
fy year month name report week end date outage_hr_decimal_calc remedy alg
FY17 Mar Mar 24th 2017-03-17 08:23:00 1.33 Critical
FY17 Mar Mar 24th 2017-03-17 09:00:00 0.40 High
FY17 Mar Mar 24th 2017-03-17 23:48:00 0.55 Medium
FY17 Mar Mar 24th 2017-03-23 09:01:00 2.10 High
FY17 Mar Mar 17th 2017-03-10 07:55:00 0.88 Critical
FY17 Mar Mar 17th 2017-03-14 08:05:00 0.41 Medium
FY17 Mar Mar 17th 2017-03-14 12:07:00 22.48 High
FY17 Mar Mar 17th 2017-03-16 12:45:00 0.58 CriticalI'm needing to calculate the average 'outage_hr_decimal_calc' where 'remedy alg' = critical or high for the most recent report week.
I'm thinking that if I use the max function to determine max 'end date', use the 'fy year', 'month name', and 'report week' from the max end date, then calculate the average where 'remedy alg' = critical or high.Thanks in advance for your assistance!!
Firstly, let's get the data into a usable format
CREATE TABLE #Data (
[fy year] VARCHAR(4) NOT NULL
, [month name] VARCHAR(3) NOT NULL
, [report week] VARCHAR(8) NOT NULL
, [end date] SMALLDATETIME NOT NULL
, [outage_hr_decimal_calc] DECIMAL(5,2) NOT NULL
, [remedy alg] VARCHAR(8) NOT NULL
);
INSERT INTO #Data ( [fy year], [month name], [report week], [end date], [outage_hr_decimal_calc], [remedy alg] )
VALUES ( 'FY17', 'Mar', 'Mar 24th', '2017-03-17 08:23:00', 1.33, 'Critical' )
, ( 'FY17', 'Mar', 'Mar 24th', '2017-03-17 09:00:00', 0.40, 'High' )
, ( 'FY17', 'Mar', 'Mar 24th', '2017-03-17 23:48:00', 0.55, 'Medium' )
, ( 'FY17', 'Mar', 'Mar 24th', '2017-03-23 09:01:00', 2.10, 'High' )
, ( 'FY17', 'Mar', 'Mar 17th', '2017-03-10 07:55:00', 0.88, 'Critical' )
, ( 'FY17', 'Mar', 'Mar 17th', '2017-03-14 08:05:00', 0.41, 'Medium' )
, ( 'FY17', 'Mar', 'Mar 17th', '2017-03-14 12:07:00', 22.48, 'High' )
, ( 'FY17', 'Mar', 'Mar 17th', '2017-03-16 12:45:00', 0.58, 'Critical' );
Now, lets's try to get the required results
WITH cteRecentWeek AS (
SELECT [fy year], [month name], [report week]
, rn = ROW_NUMBER() OVER (ORDER BY [end date] DESC)
FROM #Data
)
SELECT d.[fy year], d.[month name], d.[report week], d.[remedy alg]
, [avg_outage_hr_decimal_calc] = AVG(d.[outage_hr_decimal_calc])
FROM cteRecentWeek AS rw
INNER JOIN #Data AS d
ON rw.[fy year] = d.[fy year]
AND rw.[month name] = d.[month name]
AND rw.[report week] = d.[report week]
WHERE rw.rn = 1
AND d.[remedy alg] IN ('Critical', 'High')
GROUP BY d.[fy year], d.[month name], d.[report week], d.[remedy alg];
March 27, 2017 at 3:02 pm
Thank you!! I'll test this out, and get back with you!!
March 27, 2017 at 4:05 pm
That works great. I only have 1 change. I need the average of the sum of the 'critical' & 'high' records.
March 27, 2017 at 10:18 pm
crowegreg - Monday, March 27, 2017 4:05 PMThat works great. I only have 1 change. I need the average of the sum of the 'critical' & 'high' records.
Average of the sum? That is just the sum.
I assume that you are looking for the average of both critical and high together. That can be achieved by simply removing d.[remedy alg] from the final query
WITH cteRecentWeek AS (
SELECT [fy year], [month name], [report week]
, rn = ROW_NUMBER() OVER (ORDER BY [end date] DESC)
FROM #Data
)
SELECT d.[fy year], d.[month name], d.[report week]
, [avg_outage_hr_decimal_calc] = AVG(d.[outage_hr_decimal_calc])
FROM cteRecentWeek AS rw
INNER JOIN #Data AS d
ON rw.[fy year] = d.[fy year]
AND rw.[month name] = d.[month name]
AND rw.[report week] = d.[report week]
WHERE rw.rn = 1
AND d.[remedy alg] IN ('Critical', 'High')
GROUP BY d.[fy year], d.[month name], d.[report week];
March 31, 2017 at 9:24 am
DesNorton - Monday, March 27, 2017 10:18 PMcrowegreg - Monday, March 27, 2017 4:05 PMThat works great. I only have 1 change. I need the average of the sum of the 'critical' & 'high' records.Average of the sum? That is just the sum.
I assume that you are looking for the average of both critical and high together. That can be achieved by simply removing d.[remedy alg] from the final query
WITH cteRecentWeek AS (
SELECT [fy year], [month name], [report week]
, rn = ROW_NUMBER() OVER (ORDER BY [end date] DESC)
FROM #Data
)
SELECT d.[fy year], d.[month name], d.[report week]
, [avg_outage_hr_decimal_calc] = AVG(d.[outage_hr_decimal_calc])
FROM cteRecentWeek AS rw
INNER JOIN #Data AS d
ON rw.[fy year] = d.[fy year]
AND rw.[month name] = d.[month name]
AND rw.[report week] = d.[report week]
WHERE rw.rn = 1
AND d.[remedy alg] IN ('Critical', 'High')
GROUP BY d.[fy year], d.[month name], d.[report week];
Thank you, I figured it out but I forgot to report.
I do have another question. Using the query above, a record is written to a table which contains the following fields:
[FY Year]
[FY Report Week]
[MTTR]
[13WeekMTTRAvg]
[SQLID]
When the record is written, it does not contain any value within the [13WeekMTTRAvg]
So this table accumulates a record for every week. What I'm needing to do is calculate the average using the [MTTR] field for the last 13 weeks. Then update that week's record [13WeekMTTRAvg] field with that calculated average.
Thanks in advance!!
March 31, 2017 at 9:48 am
You haven't provided enough sample data to test this properly, but it's something like this:
AVG(MTTR) OVER (ORDER BY [FY Year], [FY Report Week] ROWS BETWEEN 12 PRECEDING AND CURRENT ROW)
Important: note that this relies on there being exactly one row for each week.
John
March 31, 2017 at 12:46 pm
Attached is an export of the table into an excel spreadsheet.
For report week Mar 24th, the [13MTTR-W] is the average of [MTTR-W] for the weeks Mar 24th up to Dec 30th. Within the query, I think using the [SQL_ID] will be easier than [FY Year] & [Report Week].
Thanks
March 31, 2017 at 3:55 pm
crowegreg - Friday, March 31, 2017 12:46 PMAttached is an export of the table into an excel spreadsheet.For report week Mar 24th, the [13MTTR-W] is the average of [MTTR-W] for the weeks Mar 24th up to Dec 30th. Within the query, I think using the [SQL_ID] will be easier than [FY Year] & [Report Week].
Thanks
Not useful for our testing. Please see the first link under "Helpful Links" in my signature line below.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply