Solved

Posted on 2010-01-06

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

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].[GetPercentageOfMain

(

@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

12 Comments

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
```

```
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
```

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
```

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

One vehicle is 37% and 1 is 77%

Any ideas?

```
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
```

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.

You may want to list them all without grouping and investigate the two problematic ones

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.Tot

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

Title | # Comments | Views | Activity |
---|---|---|---|

Insert statement is inserting duplicate records | 15 | 48 | |

How SSL connection can works without SSL Cert installed on Windows SQL 2008 R2? | 14 | 38 | |

Find data in sql table column which is not integer | 5 | 19 | |

Dynamically create stored procedure using c# | 7 | 15 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**21** Experts available now in Live!