Get percentages
-
Based upon some earlier help from people on this forum, I came up with the following query:
Select Count (DISTINCT PL.PLid)
FROM workrequest WR
INNER JOIN ProviderLink PL
ON WR.WRid = PL.PLworkrequestID
WHERE PL.PLcurrentStatus < '90'
AND ((PL.Pltat + PL.Plunworkable) > '75' AS decimal) / COUNT(*) * 100) AS decimal(5,2)I always get a "Incorrect syntax near the keyword 'AS'." error when running it.
What it SHOULD do, is get the percentage of workrequests that are over 75 days old.
So I am trying to query that and then divide the total to get the percentage.
Could anyone see any errors?
thanks!
-
Change your last line to the following:
AND (cast(((PL.Pltat + PL.Plunworkable) > '75' AS decimal) / COUNT(*) * 100) as decimal(5,2))
the "as" clause as you were using it was trying to assign the resulting column a "name", additionally you had an extra ")" in the mix. I don't know if your query will produce what you want but this should get you past the syntax error.
James.
-
Now I get a "Line 6: Incorrect syntax near '>'."
tried switching some things around, but can't get around this error
-
What is (pl.pltat + pl.plunworkable) > '75' supposed to do? That actually ends that part of the statement and the "AS DECIMAL" accomplishes nothing.
I guess I should have looked closer. This statement will not work. I really can only make a guess at what you want to do.
how about this:
AND (pl.plat + pl.plunworkable) / count(*) * 100) > 75
That looks like what you want, but you can't put an aggregate in the where clause like that. So basically the entire premis is bad.
Post a sample table with four or five test records along with the expected results. I can then help you achive the result you want.
-
providerlink table
PLid PLworkRequestID PLcurrentStatus PLtat PLunworkable
PL203602 WR27904 90 39 73
PL203605 WR30397 90 2 27
PL204371 WR29892 11 38 76
PL204374 WR29892 90 23 61
PL205765 WR30018 90 72 27
PL207872 WR29683 11 79 0
PL207874 WR30775 11 12 0
So I want to find the percentage of records that, where PLtat + PLunworkable is greater than 75. So for the sample data above, I can see that 5 out of the 7 records meets that criteria, so that would be 71%.
-
Using your example data, the following should work
BEGIN
set nocount on
declare @ProviderLink table (PLid varchar(10), PLworkRequestID varchar(10), PLcurrentStatus int, PLtat int, PLunworkable int)
insert into @ProviderLink values ('PL203602', 'WR27904', 90, 39, 73)
insert into @ProviderLink values ('PL203605', 'WR30397', 90, 2 , 27)
insert into @ProviderLink values ('PL204371', 'WR29892', 11, 38, 76)
insert into @ProviderLink values ('PL204374', 'WR29892', 90, 23, 61)
insert into @ProviderLink values ('PL205765', 'WR30018', 90, 72, 27)
insert into @ProviderLink values ('PL207872', 'WR29683', 11, 79, 0)
insert into @ProviderLink values ('PL207874', 'WR30775', 11, 12, 0)
--This is the only statement you need, adjust to fit your actual table/column names
select cast(((select cast(count(*) as decimal) from @providerlink where pltat + plunworkable > 75) /
(select cast(count(*) as decimal) from @providerlink) * 100) as decimal(4,2)) as [percent]
END
/* OUTPUT:
percent
71.43
*/
-
See if this helps
SELECT
((SUM(CASE WHEN (PL.Pltat + PL.Plunworkable) > 75 THEN 1.0 ELSE 0.0 END) / CAST(Count(*) as decimal(10,5))) * 100.00) AS Percentage,
ROUND(((SUM(CASE WHEN (PL.Pltat + PL.Plunworkable) > 75 THEN 1.0 ELSE 0.0 END) / CAST(Count(*) as decimal(10,5))) * 100.00),0) AS RoundedPercentage
FROM
ProviderLink PL
-
Antares686: I receive an "Arithmetic overflow error converting numeric to data type numeric." error when running that query...I assume I need to do another CAST somewhere?
Thanks
-
No need for even a single CAST or CASE...
SELECT (
SELECT COUNT(*) * 100.0
FROM @ProviderLink
WHERE PLtat + PLunworkable > 75
)
/ (COUNT(*)) AS Percentage
FROM @ProviderLink
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff: Nice! But maybe just one little cast to get the decimal to round to 2 places
-
LOL! This is where I'm supposed to say something like "Do it in the app"...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff...that is beautiful...thank you!
-
My pleasure, Maqy... thank you and James for the feedback.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Yes, saving a CASE is nice thing, but I would prefer to use one CASE instead of repeating query to the same table:
SELECT COUNT(case when PLtat + PLunworkable > 75 then 1 else NULL end) * 100.0
/ COUNT(*) AS Percentage
FROM @ProviderLink
Gonna be twice faster.
_____________
Code for TallyGenerator -
Heh... nice job Serqiy... guess I didn't have enough coffee
I didn't think of the crosstab-like solution. Very cool.--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply