?
Solved

Add preset percentile counts to SQL SP

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

Expert Comment

by:dbidba
ID: 26191814
0
 
LVL 10

Expert Comment

by:lof
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

Open in new window

0
 
LVL 27

Expert Comment

by:Chris Luttrell
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

Open in new window

0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 27

Accepted Solution

by:
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

Open in new window

0
 
LVL 3

Author Closing Comment

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

Author Comment

by:MBoy
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

by:lof
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

Open in new window

0
 
LVL 3

Author Comment

by:MBoy
ID: 26210454
lof - I get....

Percentile NULL, DevicesInPercentile 2
0
 
LVL 10

Expert Comment

by:lof
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

by:MBoy
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

by:lof
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

by:MBoy
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

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

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…

579 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