?
Solved

Add percentile count to SQL SP

Posted on 2010-01-05
12
Medium Priority
?
222 Views
Last Modified: 2012-05-08
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
0
Comment
Question by:MBoy
  • 4
  • 3
  • 3
  • +1
12 Comments
 
LVL 5

Expert Comment

by:fhillyer1
ID: 26184253
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?
0
 
LVL 13

Expert Comment

by:zadeveloper
ID: 26184327
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

0
 
LVL 3

Author Comment

by:MBoy
ID: 26184512
fhillyer1 - It's beyond my SQL experience.

zadeveloper - I need this done through a SQL SP to fit into my framework.  
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 5

Expert Comment

by:fhillyer1
ID: 26184628
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

0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 26184769
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
0
 
LVL 13

Accepted Solution

by:
zadeveloper earned 2000 total points
ID: 26184814
Try this then:


/*

First thing you need to do is: CREATE TYPE GPRSDeviceHardwareList ...
Then run ALTER PROCEDURE [dbo].[GetPercentageOfMaintenanceCycleUsedByGPRSDeviceHardwareID] ...
(These 2 are found below)

then you can use code below
*/

declare @MyIds GPRSDeviceHardwareList

INSERT INTO @MyIds VALUES ('ID1')
INSERT INTO @MyIds VALUES ('ID2')
INSERT INTO @MyIds VALUES ('ID3')


DECLARE @MyValues TABLE (GPRSDeviceHardwareID varchar(50), PercentageUsed Int)

insert into @MyValues exec GetPercentageOfMaintenanceCycleUsedByGPRSDeviceHardwareID @MyIds


select t1.* from GPRSDevice t1 inner join @MyValues t2 on t1.GPRSDeviceHardwareID = t2.GPRSDeviceHardwareID where t2.PercentageUsed between 0 and 25



--This is a new table type that you can use to pass multiple GPRSDeviceHardwareID's to the new stored proc
CREATE TYPE GPRSDeviceHardwareList AS TABLE 
( 
	GPRSDeviceHardwareID varchar(50)
)


ALTER PROCEDURE [dbo].[GetPercentageOfMaintenanceCycleUsedByGPRSDeviceHardwareID]
(
     @GPRSDeviceList As GPRSDeviceHardwareList readonly
)
AS

DECLARE @Return TABLE 
(
	GPRSDeviceHardwareID varchar(50),
	X Int,
	Y Int,
	A Int,
	B Int,
	HL Int,
	Z Int,
	ZZ  Double Precision,
	ZZZ Double Precision,
	PercentageUsed Int
)

BEGIN
	INSERT INTO @Return (GPRSDeviceHardwareID) SELECT GPRSDeviceHardwareID FROM @GPRSDeviceList
	
	UPDATE t1 set X = t2.[PMHourMeter] from @Return t1 inner join [PMSchedule] t2 on t2.GPRSDeviceHardwareID  = t1.GPRSDeviceHardwareID	
            --SET @X = (SELECT [PMHourMeter] FROM [PMSchedule] WHERE GPRSDeviceHardwareID = @GPRSDeviceHardwareID)
            
    UPDATE t1 set A = t2.MAX(RunningTotal) from @Return t1 inner join [HourMeter] t2 on t2.GPRSDeviceHardwareID  = t1.GPRSDeviceHardwareID AND MaintenanceBit = 0
            --SET @A = (SELECT MAX(RunningTotal) FROM HourMeter WHERE GPRSDeviceHardwareID = @GPRSDeviceHardwareID AND MaintenanceBit = 0)

	UPDATE t1 set B = t2.[TotalHourMeterDifference] from @Return t1 inner join HourMeterDifference t2 on t2.GPRSDeviceHardwareID  = t1.GPRSDeviceHardwareID AND MaintenanceBit = 0
            --SET @B =(SELECT TotalHourMeterDifference FROM HourMeterDifference WHERE GPRSDeviceHardwareID = @GPRSDeviceHardwareID AND MaintenanceBit = 0)

	UPDATE @Return set Y = (A + B) /60
            --Set @Y = (@A + @B) / 60
            
    UPDATE @Return set HL = (X + Y)
            --Set @HL = (@X - @Y)
    
    UPDATE t1 set Z = t2.[Hours] from @Return t1 inner join GPRSDevice t2 on t2.GPRSDeviceHardwareID  = t1.GPRSDeviceHardwareID
            --SET @Z = (SELECT [Hours] FROM [GPRSDevice] WHERE [GPRSDeviceHardwareID] = @GPRSDeviceHardwareID)

	UPDATE @Return set ZZ = (Z - HL)
            --Set @ZZ = (@Z - @HL)
	
	UPDATE @Return set ZZZ = ((ZZ / Z) * 100)
            --Set @ZZZ = ((@ZZ / @Z) * 100)

	UPDATE @Return set @PercentageUsed = ZZZ
            --SET @PercentageUsed = @ZZZ

	Select GPRSDeviceHardwareID, PercentageUsed from @Return
--RETURN @PercentageUsed

END
GO 

Open in new window

0
 
LVL 5

Expert Comment

by:fhillyer1
ID: 26184817
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
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 26185645
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.
0
 
LVL 13

Expert Comment

by:zadeveloper
ID: 26185780
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.
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 26185875
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.  :)
0
 
LVL 13

Expert Comment

by:zadeveloper
ID: 26185927
- well Thats why we should have used a function - but this was not acceptable.
0
 
LVL 3

Author Comment

by:MBoy
ID: 26190287
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.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

840 members asked questions and received personalized solutions in the past 7 days.

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

Join & Ask a Question