?
Solved

Need help fixing SQL SP - Percentiles

Posted on 2010-01-08
5
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
Comment
Question by:MBoy
  • 2
  • 2
5 Comments
 
LVL 10

Expert Comment

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

Author Comment

by:MBoy
ID: 26212822
What do you mean?
0
 
LVL 27

Accepted Solution

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

Open in new window

0
 
LVL 10

Expert Comment

by:Rakesh Jaimini
ID: 26212997
i meant what CGLuttrell: explained
now you got me?
0
 
LVL 3

Author Comment

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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

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…

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.

Join & Ask a Question