Solved

# Add preset percentile counts to SQL SP

Posted on 2010-01-06
Medium Priority
254 Views
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
Question by:MBoy
• 5
• 4
• 2
• +1

LVL 5

Expert Comment

ID: 26191814
0

LVL 10

Expert Comment

ID: 26192421
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

LVL 27

Expert Comment

ID: 26196084
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

LVL 27

Accepted Solution

Chris Luttrell earned 2000 total points
ID: 26196248
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

LVL 3

Author Closing Comment

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

LVL 3

Author Comment

ID: 26210116
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

LVL 10

Expert Comment

ID: 26210192
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

LVL 3

Author Comment

ID: 26210454
lof - I get....

Percentile NULL, DevicesInPercentile 2
0

LVL 10

Expert Comment

ID: 26210504
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

LVL 3

Author Comment

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

LVL 10

Expert Comment

ID: 26210775
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

LVL 3

Author Comment

ID: 26210993

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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server.Â RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at: Â  htâ€¦
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Integration Management Part 2
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micrâ€¦
###### Suggested Courses
Course of the Month12 days, 19 hours left to enroll