Solved

# Need help fixing SQL SP - Percentiles

Posted on 2010-01-08
Medium Priority
179 Views
Last Modified: 2012-05-08
I've got 2 vehicles - 1 = 38% and 1 = 77%

I'm trying to get...
Range 26-50, Cnt 1
Range 75-100, Cnt 1

But I get...
Range 75-100, Cnt 2

From SP:

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 - Cast(((MaxRunningTotal + TotalHourMeterDifference) / 60) As Integer)) AS DOUBLE PRECISION) / Hours) * 100)AS DOUBLE PRECISION) 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
0
Question by:MBoy
• 2
• 2
5 Comments

LVL 10

Expert Comment

ID: 26212771
your inner query is returning 3 rows please check it first
then apply group by clouse
0

LVL 3

Author Comment

ID: 26212822
What do you mean?
0

LVL 27

Accepted Solution

Chris Luttrell earned 2000 total points
ID: 26212947
run just your inner query and see what PctUsed is being returned, you have changed it from the original we did the other day.  It seems to group on the pcts returned when I test with my sample data.  If you are still getting the 38% & 77% can you provide the details that make up those numbers, there may be something we missed.
``````            SELECT      D.[GPRSDeviceHardwareID],
CAST(((Cast((Hours - Cast(((MaxRunningTotal + TotalHourMeterDifference) / 60) As Integer)) AS DOUBLE PRECISION) / Hours) * 100)AS DOUBLE PRECISION) 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
``````
0

LVL 10

Expert Comment

ID: 26212997
i meant what CGLuttrell: explained
now you got me?
0

LVL 3

Author Comment

ID: 26213096
CGLuttrel - It's returning

015712379      89
024978755      94

This does not match the results from the original SP by GPRSDeviceHardwareID

I think I need to dig into the demo data.

Thank you very much for all your help.
0

## Featured Post

Question has a verified solution.

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

In this article I will describe the Detach & Attach 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.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
###### Suggested Courses
Course of the Month16 days, 21 hours left to enroll

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

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