asked on

Add percentile count to SQL SP

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

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
GO

fhillyer1

create a temp table get the source for the vehicles, then use your sp, get the percentages and dump them into that temp table
then categorize their percentage and count the categories

temp table looks like this
vehicleID, percentage, category

insert into temp table select disctinct vehicleid, '',''
update temp table set percentage = sp(vehicleid) where vehicleid in (Select vehicleid from temp table)

update temp table set category = A where (percentage >=0 and percentage <=25)

something like that or use the case clause

and then run the following
select count(category) 'A' from temp table where cateogry = 'A'

or modify it as you like you get the idea?

Jarrod

try this function

and call like so :

select t1.* from GPRSDevice t1 where dbo.fnPercentageOfMaintenanceCycleUsedByGP(t1.GPRSDeviceHardwareID) between 0 and 25

create FUNCTION [dbo].[fnPercentageOfMaintenanceCycleUsedByGP]
(	
	@GPRSDeviceHardwareID As varchar(50)
)
RETURNS Int
AS
BEGIN
	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

	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)
	return ((@ZZ / @Z) * 100)
END

Open in new window

MBoy

ASKER

fhillyer1 - It's beyond my SQL experience.

zadeveloper - I need this done through a SQL SP to fit into my framework.

fhillyer1

here you go try this then
call it like
ProcessAllVehiclesBetween 0, 25

CREATE PROCEDURE [dbo].[ProcessAllVehiclesBetween]
(
     @LowPercent As int,
     @HighPercent as int 
)
AS
dim @NumOfCars as int
Begin
 set @NumOfCars =  select count(A.Vehicle) 'Total' from vehiclelistsource A where
   dbo.fnPercentageOfMaintenanceCycleUsedByGP(A.Vehicle) 
   between @LowPercent and @HighPercent
   return @NumOfCars
end

Open in new window

Chris Luttrell

fhillyer1,
how do they get 101+ then, what is passed in for HighPercent?

MBoy,
Are you wanting one output showing the counts something like this?
Range Cnt
00-25 13
26-50 22
51-75 24
76-100 10
100+ 3

ASKER CERTIFIED SOLUTION

Jarrod

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

fhillyer1

good one luttrell i guess he has to use the top percentage they have
if a car percentage will never be higher than 1000 then he can use 101 - 1000

Chris Luttrell

MBoy, you accepted that answer? Good luck to you. It does not run as is and is structured very badly (as was the original SP, looked like it was written by a procedural language programmer that does not know how a database should work.) Not to mention, to get it to work you would have to load all your IDS into that table type to pass in, what is the point of that.
If you give up and want a better answer, post a related question (the link at the top of the new Post Comment block) and someone can provide you with a proper SQL database SP.

Jarrod

CGLuttrell:

If I could explain ...

As far as loading all the IDs into the type table - this I am assuming would be as simple as insert into var select ids from table where a, b and c

Using the Table Type parameter is the best option in this case ... to update all fields at once far out performs doing the calculation on the fly (as with a computed column) - as for the structure of the SP as you can plainly see - it was not rewritten, but rather ported over from a single value to a multi-value dimention.

I am sure that with the SP running (which it should - maybe 1 or 2 little tweaks which I am sure he would have fixed) - yes I was unable to test this as I did not have all the tables :) He is in a better position to optimise the proc.

Chris Luttrell

But there is not a reason to go to all the trouble of a table variable and loading or passing it, the same results can be accomplished in a single query (with one subquery).
I know you just ported the existing SP logic (one line at a time though), but I just cringe when I see what looks like COBOL or C code in a database. :)

Jarrod

- well Thats why we should have used a function - but this was not acceptable.

MBoy

ASKER

I will start a new question and award points to anyone that puts this together in a single SP. The problem I have here is this is very new SQL terrain for me. Put me in your watch list.