December 7, 2005 at 11:18 am
my current query looks like this:
SELECT RIC,COUNT(*) as myCount,
SUM(CASE WHEN compliant75pctRule = 'T' THEN 1 ELSE 0 END ) AS Compliant
FROM erehab_data
GROUP BY RIC
ORDER BY RIC
we want the results to contain a row for each RIC containing the RIC, the total # of records with that RIC and the total number or records containing that RIC where the Compliant75pctRule = T
this part works well.
but now i am being asked to throw another field into the mix. there is also a LOS field with contains an integer value 1-10. in addition to the query above i need to return 2 more fields:
1) the total of the integer values from the LOS field from records where the value of compliant75pctRULE is either T OR F
2) the the total of the integer values from the LOS field from records where the value of compliant75pctRULE is T
any ideas?
December 7, 2005 at 5:48 pm
>>the total of the integer values from the LOS field
What datatype is the LOS column, and what does it typically contain ? What does "integer value" mean ?
Is it a char/varchar that may contain alpha characters ? If so, you'll need to use IsNumeric() function in a Case ... When
Is it a numeric type, where you want to ignore non-whole numbers ? If so you'll need to Cast() to an int, then Cast() back to the numeric and compare to the original value to see if the portion to the right of a decimal point was removed.
December 8, 2005 at 7:09 am
Try this:
SELECT RIC,COUNT(*) as myCount,
SUM(CASE WHEN compliant75pctRule = 'T' THEN 1 ELSE 0 END) AS [Compliant],
SUM(CASE WHEN compliant75pctRule IN ('T', 'F') THEN LOS ELSE 0 END) AS [SumOfLOS_WithTorF],
SUM(CASE WHEN compliant75pctRule = 'T' THEN LOS ELSE 0 END) AS [SumOfLOS_WithT_Only],
FROM erehab_data
GROUP BY RIC
ORDER BY RIC
December 9, 2005 at 8:50 am
I was just wondering, can compliant75pctRule contain NULL?
Because if it is a mandatory NOT NULL then presumably all the rows will be either T or F, so the CASE statement when compliant75pctRule in ('T','F') will always return LOS.
David
If it ain't broke, don't fix it...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply