This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.
with Devices as
(
select Z.GPRSDeviceHardwareID, (Z - X - (((A+B)/60))/Z)*100 as Percentage
from (
SELECT [Hours] Z, GPRSDeviceHardwareID
FROM [GPRSDevice]
) Z
left outer join (
SELECT [PMHourMeter] X, GPRSDeviceHardwareID
FROM [PMSchedule]
) X on Z.GPRSDeviceHardwareID = X.GPRSDeviceHardwareID
left outer join (
SELECT MAX(RunningTotal) as A, GPRSDeviceHardwareID
FROM HourMeter
WHERE MaintenanceBit = 0
GROUP BY GPRSDeviceHardwareID
) A on Z.GPRSDeviceHardwareID = A.GPRSDeviceHardwareID
left outer join (
SELECT TotalHourMeterDifference B, GPRSDeviceHardwareID
FROM HourMeterDifference
WHERE MaintenanceBit = 0
) B on Z.GPRSDeviceHardwareID = B.GPRSDeviceHardwareID
)
select
Percentile
,count(*) as DevicesInPercentile
from (
select
*
,case
when @v=0 then '0-25'
when @v>100 then '100+'
else
case cast((@v-1)/25 as int)
when 0 then '0-25'
when 1 then '26-50'
when 2 then '51-75'
when 3 then '76-100'
end
end as Percentile
from Devices
) DevicesWithPercentiles
) T
group by Percentile
CREATE PROCEDURE [dbo].[GetPercentageOfMaintenanceCycleUsedAllGPRSDeviceHardwareIDs]
AS
BEGIN
SELECT D.[GPRSDeviceHardwareID],
CAST(((CAST(([Hours] - ([PMHourMeter] - ((MaxRunningTotal + TotalHourMeterDifference) / 60))) AS DOUBLE PRECISION) / [Hours]) * 100) AS INT) PctUsed
FROM [GPRSDevice] D
INNER JOIN [PMSchedule] S ON D.[GPRSDeviceHardwareID] = S.[GPRSDeviceHardwareID]
INNER JOIN HourMeterDifference HMD ON S.[GPRSDeviceHardwareID] = HMD.[GPRSDeviceHardwareID]
INNER JOIN (SELECT GPRSDeviceHardwareID, MAX(RunningTotal) AS MaxRunningTotal
FROM HourMeter
WHERE MaintenanceBit = 0 GROUP BY GPRSDeviceHardwareID) MRT
ON MRT.GPRSDeviceHardwareID = D.GPRSDeviceHardwareID
RETURN
END
SELECT
CASE WHEN x.PctUsed <= 25 THEN '00-25'
WHEN x.PctUsed <= 50 THEN '26-50'
WHEN x.PctUsed <= 75 THEN '51-75'
WHEN x.PctUsed <= 100 THEN '75-100'
ELSE '101+'
END RANGE,
COUNT(*) Cnt
FROM (
SELECT D.[GPRSDeviceHardwareID],
CAST(((CAST(([Hours] - ([PMHourMeter] - ((MaxRunningTotal + TotalHourMeterDifference) / 60))) AS DOUBLE PRECISION) / [Hours]) * 100) AS INT) PctUsed
FROM [GPRSDevice] D
INNER JOIN [PMSchedule] S ON D.[GPRSDeviceHardwareID] = S.[GPRSDeviceHardwareID]
INNER JOIN HourMeterDifference HMD ON S.[GPRSDeviceHardwareID] = HMD.[GPRSDeviceHardwareID]
INNER JOIN (SELECT GPRSDeviceHardwareID, MAX(RunningTotal) AS MaxRunningTotal
FROM HourMeter
WHERE MaintenanceBit = 0 GROUP BY GPRSDeviceHardwareID) MRT
ON MRT.GPRSDeviceHardwareID = D.GPRSDeviceHardwareID
) x
GROUP BY
CASE WHEN x.PctUsed <= 25 THEN '00-25'
WHEN x.PctUsed <= 50 THEN '26-50'
WHEN x.PctUsed <= 75 THEN '51-75'
WHEN x.PctUsed <= 100 THEN '75-100'
ELSE '101+'
END,
CASE WHEN x.PctUsed <= 25 THEN 1
WHEN x.PctUsed <= 50 THEN 2
WHEN x.PctUsed <= 75 THEN 3
WHEN x.PctUsed <= 100 THEN 4
ELSE 5
END
ORDER BY
CASE WHEN x.PctUsed <= 25 THEN 1
WHEN x.PctUsed <= 50 THEN 2
WHEN x.PctUsed <= 75 THEN 3
WHEN x.PctUsed <= 100 THEN 4
ELSE 5
END
with Devices as
(
select Z.GPRSDeviceHardwareID, (Z.[Hours] - X.[PMHourMeter] - (((A.MaxRunningTotal+B.TotalHourMeterDifference)/60))/Z.[Hours])*100 as Percentage
from [GPRSDevice] Z
left outer join [PMSchedule] X on Z.GPRSDeviceHardwareID = X.GPRSDeviceHardwareID
left outer join (
SELECT MAX(RunningTotal) as MaxRunningTotal, GPRSDeviceHardwareID
FROM HourMeter
WHERE MaintenanceBit = 0
GROUP BY GPRSDeviceHardwareID
) A on Z.GPRSDeviceHardwareID = A.GPRSDeviceHardwareID
left outer join HourMeterDifference B on MaintenanceBit = 0 and Z.GPRSDeviceHardwareID = B.GPRSDeviceHardwareID
)
select
Percentile
,count(*) as DevicesInPercentile
from (
select
*
,case
when Percentage=0 then '0-25'
when Percentage>100 then '100+'
else
case cast((Percentage-1)/25 as int)
when 0 then '0-25'
when 1 then '26-50'
when 2 then '51-75'
when 3 then '76-100'
end
end as Percentile
from Devices
) DevicesWithPercentiles
group by Percentile
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
http://www.sqlteam.com/article/computing-percentiles-in-sql-server
http://www.devx.com/dbzone/Article/35550