May 14, 2007 at 10:41 am
I have this query below:
Select Count (DISTINCT providerlink.PLid) FROM workrequest, providerlink
WHERE providerlink.PLworkrequestID = workrequest.WRid
AND providerlink.PLcurrentStatus IN ('11', '25', '30', '31', '32', '33', '35', '41', '51', '53', '55', '75', '80', '82', '86')
It finds all the work requests(PLid) within a given set of status numbers(PLcurrentStatus).
is there a way to get the percentage of work requests that are within that range of status numbers?
I want to find the percentage of workrequests that are that set of status numbers.
So say there are 10,000 total work requests, and 5000 are in that set of status numbers, then I would want the query to return 50% rather than just the total of 5000.
Thanks!
May 14, 2007 at 11:26 am
DECLARE @WorkRequest TABLE (WRid int)
INSERT INTO @WorkRequest
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9 UNION ALL
SELECT 10
DECLARE @ProviderLink TABLE (PLid int IDENTITY(1,1), PLworkrequestID int, PLcurrentStatus varchar(10))
INSERT INTO @ProviderLink
SELECT 1,'11' UNION ALL
SELECT 2,'25' UNION ALL
SELECT 3,'30' UNION ALL
SELECT 4,'31' UNION ALL
SELECT 5,'32' UNION ALL
SELECT 6,'99' UNION ALL
SELECT 7,'99' UNION ALL
SELECT 8,'99' UNION ALL
SELECT 9,'99' UNION ALL
SELECT 10,'99'
DECLARE @TotalCount decimal(10,2),
@MatchCount decimal(10,2)
SELECT @TotalCount = COUNT(*)
FROM @WorkRequest WR
INNER JOIN @ProviderLink PL
ON WR.WRid = PL.PLworkrequestID
SELECT @MatchCount = COUNT(*)
FROM @WorkRequest WR
INNER JOIN @ProviderLink PL
ON WR.WRid = PL.PLworkrequestID
WHERE PL.PLcurrentStatus IN ('11', '25', '30', '31', '32', '33', '35', '41', '51', '53', '55', '75', '80', '82', '86')
SELECT CAST((CAST((@MatchCount / @TotalCount) AS decimal(5,2)) * 100) AS varchar) + '%'
May 14, 2007 at 12:13 pm
wow...that is a lot. Would I put all of this into one query?
May 14, 2007 at 12:34 pm
You could put it all into one query like this:
SELECT CAST((CAST((CAST(SUM((CASE WHEN PL.PLcurrentStatus IN ('11', '25', '30', '31', '32', '33', '35', '41', '51', '53', '55', '75', '80', '82', '86') THEN 1 ELSE 0 END)) AS decimal) / COUNT(*) * 100) AS decimal(5,2))) AS varchar) + '%'
FROM @WorkRequest WR
INNER JOIN @ProviderLink PL
ON WR.WRid = PL.PLworkrequestID
May 14, 2007 at 1:06 pm
Why would you need 3 CAST statements?
May 14, 2007 at 1:09 pm
I'm not John, but I'm pretty sure he's using the two innermost casts to guarantee you don't get the int division issue, where the results are also an int. For example, SELECT 3/2 returns a 1. The outermost is to turn the results into a varchar so you can add the percent symbol to it.
May 14, 2007 at 1:11 pm
David is correct. You can remove the outer most CAST if you aren't planning on having the % as part of the results. I was under the assumption that it was needed as your original example included it.
May 14, 2007 at 1:11 pm
May 14, 2007 at 2:11 pm
What I don't understand about this query, is how is it getting the total # of workrequests? Because shouldn't it have to get the total, then get the total that are within the status range, and then divide those 2 numbers to get the percentage?
Thanx!
May 14, 2007 at 2:17 pm
That's exactly what it is doing. The total comes from the COUNT(*), the number within the range comes from the CASE statement. As you can see, the CASE statement results are divided by the COUNT(*) to get the average. This value is then multiplied by 100 to give you the percentage. All the CASTing is for division percision and formatting.
May 15, 2007 at 8:33 am
Oh ok...I see that part now...thank you!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply