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

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
Asked:
MBoy
  • 5
  • 4
  • 2
  • +1
1 Solution
 
lofCommented:
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

Open in new window

0
 
Chris LuttrellSenior 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

Open in new window

0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Chris LuttrellSenior 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

Open in new window

0
 
MBoyAuthor Commented:
I couldn't work this one out on my own for all the tea in China - Thanks
0
 
MBoyAuthor 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
 
lofCommented:
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

Open in new window

0
 
MBoyAuthor Commented:
lof - I get....

Percentile NULL, DevicesInPercentile 2
0
 
lofCommented:
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
 
MBoyAuthor 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
 
lofCommented:
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
 
MBoyAuthor 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.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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