• Status: Solved
• Priority: Medium
• Security: Public
• Views: 257

# Add preset percentile counts to SQL SP

Add percentile count to SQL SPQuestion: I need to find the number of vehicles in the following percentiles .... 0 - 25, 26 - 50, 51 - 75, 76 - 100, 101+

Here is a SP that get's the percentage figure for a single vehicle

I'M LOOKING FOR:
Range   Cnt
00-25    13
26-50    22
51-75    24
76-100  10
100+      3

CREATE PROCEDURE [dbo].[GetPercentageOfMaintenanceCycleUsedByGPRSDeviceHardwareID]
(
@GPRSDeviceHardwareID As varchar(50)
)
AS

DECLARE @X As Int
DECLARE @Y As Int
DECLARE @A As Int
DECLARE @B As Int
DECLARE @HL As Int
DECLARE @Z As Int
DECLARE @ZZ As Double Precision
DECLARE @ZZZ As Double Precision
DECLARE @PercentageUsed As Int

BEGIN

SET @X = (SELECT [PMHourMeter] FROM [PMSchedule] WHERE GPRSDeviceHardwareID = @GPRSDeviceHardwareID)

SET @A = (SELECT MAX(RunningTotal) FROM HourMeter WHERE GPRSDeviceHardwareID = @GPRSDeviceHardwareID AND MaintenanceBit = 0)

SET @B =(SELECT TotalHourMeterDifference FROM HourMeterDifference WHERE GPRSDeviceHardwareID = @GPRSDeviceHardwareID AND MaintenanceBit = 0)

Set @Y = (@A + @B) / 60

Set @HL = (@X - @Y)

SET @Z = (SELECT [Hours] FROM [GPRSDevice] WHERE [GPRSDeviceHardwareID] = @GPRSDeviceHardwareID)

Set @ZZ = (@Z - @HL)

Set @ZZZ = ((@ZZ / @Z) * 100)

SET @PercentageUsed = @ZZZ

RETURN @PercentageUsed

END
0
MBoy
• 5
• 4
• 2
• +1
1 Solution

Commented:
0

Commented:
Hi MBoy,

Consider the sample code attached. It may look a bit more complicated but it should perform much better

If you wish instead of CTE you could enclose first bit in a table-valued function so you can query it as a table whenever you need a list of all devices with their percentages or a percentage of one device only.

Hope that helps
Lof
``````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
``````
0

Senior Database ArchitectCommented:
Try this simplified SP/Query to retrun all your HardwareIDs and the PctUsed of each.
``````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
``````
0

Senior Database ArchitectCommented:
Sorry, should have read the full details above first,  this query looks a little more complicated but the guts are the same, just had to do some tricks to get the Ranges and keep them in the right order.  You can just use the query or put it inside the SP like I did above if you need to.
HTH,
Chris
``````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
``````
0

Author Commented:
I couldn't work this one out on my own for all the tea in China - Thanks
0

Author Commented:
CGLuttrell - 26196248 is not working correctly.  I tried to fix it but was not successful.

The return data is RANGE '00-25' , Cnt '2'

One vehicle is 37% and 1 is 77%

Any ideas?
0

Commented:
have you tried my solution with CTE? if you prefere more compact query here is updated version. I have also found one bag there - I forgot to change one test variable I was testing it with with your column name.
``````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
``````
0

Author Commented:
lof - I get....

Percentile NULL, DevicesInPercentile 2
0

Commented:
you may change all left outer to inner joins
but it probably means you are missing some records in the tables.

basically you are using 4 tables and it looks like each GPRS device should have entries in all of them but some rows are missing.

I just converted your query and the only change I've made was to use left outer joins not to assume too much.
0

Author Commented:
I tried changing to inner joins with no change.  I'm playing with your SP to see why the Percentile is returning NULL
0

Commented:
the only reason is that one of values is null for two records.
You may want to list them all without grouping and investigate the two problematic ones
0

Author Commented:

Returns:  Minus  2

The problem is in the math - I'm trying to fix it.

with Devices as
(
select Z.GPRSDeviceHardwareID, (Z.[Hours] - X.[PMHourMeter] - (((A.MaxRunningTotal+B.TotalHourMeterDifference)/60))/Z.[Hours])*100 as Percentage
from [GPRSDevice] Z
inner join [PMSchedule] X on Z.GPRSDeviceHardwareID = X.GPRSDeviceHardwareID
inner join (
SELECT MAX(RunningTotal) as MaxRunningTotal, GPRSDeviceHardwareID
FROM HourMeter
WHERE MaintenanceBit = 0
GROUP BY GPRSDeviceHardwareID
) A on Z.GPRSDeviceHardwareID = A.GPRSDeviceHardwareID
inner join HourMeterDifference B on MaintenanceBit = 0 and Z.GPRSDeviceHardwareID = B.GPRSDeviceHardwareID
)
select
Percentile
,count(*) as DevicesInPercentile
from (
select
*
,CASE
WHEN Percentage < 0 THEN 'Minus'
WHEN Percentage <= 25 THEN '00-25'
WHEN Percentage <= 50 THEN '26-50'
WHEN Percentage <= 75 THEN '51-75'
WHEN Percentage <= 100 THEN '75-100'
ELSE '101+'
END as Percentile
FROM Devices
) DevicesWithPercentiles
group by Percentile
0
Question has a verified solution.

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.

## Featured Post

• 5
• 4
• 2
• +1
Tackle projects and never again get stuck behind a technical roadblock.