May 28, 2009 at 6:20 am
I am trying to use an aggregate function on an aggregate function (sum and max funcitons below) but it obviously keep failing. Is there another way that I can rewrite the query to get the desired result.
SELECT
'OP',
OP.Specialty_Code,
'OP',
COUNT(OP.Attendance_ID) AS Activity,
SUM(MAX(OP_Procedure_Tariff.Tariff)) AS Total_Cost
FROM
nwcscmdsdata.dbo.Outpatient_CMDS_Data OP INNER JOIN
dbo.OP_Procedure_Tariff ON OP.Cost_category = dbo.OP_Procedure_Tariff.Cost_category INNER JOIN
dbo.OP_Tariff ON OP.Specialty_Code = dbo.OP_Tariff.SpecialtyCode LEFT OUTER JOIN
dbo.PBC_Providers ON dbo.udf_providerCode(OP.PAS_data_source) = dbo.PBC_Providers.providerCode LEFT OUTER JOIN
nwcscmdsdata.dbo.Outpatient_CMDS_Procedures OP_Procs ON OP.Attendance_ID = OP_Procs.Attendance_ID
WHERE
(dbo.OP_Procedure_Tariff.OPCS_Code IN
(OP.Primary_Procedure_Code,
OP_Procs.First_Secondary_Procedure,
OP_Procs.Second_Secondary_Procedure,
OP_Procs.Third_Secondary_Procedure,
OP_Procs.Fourth_Secondary_Procedure,
OP_Procs.Fifth_Secondary_Procedure,
OP_Procs.Sixth_Secondary_Procedure,
OP_Procs.Seventh_Secondary_Procedure,
OP_Procs.Eighth_Secondary_Procedure,
OP_Procs.Ninth_Secondary_Procedure,
OP_Procs.Tenth_Secondary_Procedure,
OP_Procs.Eleventh_Secondary_Procedure)) AND
(OP.Purchaser_ID LIKE '5K5%') AND
(NOT ISNULL(OP.Contract_Line_Number,'') = 'NONCHARGE') AND
(OP.month_of_attendance BETWEEN '200804' AND '200903') AND
(OP.First_Attendance IN ('1', '2')) AND
(OP.Attended_or_DNAd IN ('5', '6')) AND
(OP.month_of_attendance BETWEEN '200804' AND '200903') AND
(OP.First_Attendance IN ('1', '2')) AND
(OP.Attended_or_DNAd IN ('5', '6'))
GROUP BY
OP.Specialty_Code,OP_Procedure_Tariff.Tariff
May 28, 2009 at 6:57 am
what is your desired result? you're grouping by
OP.Specialty_Code,OP_Procedure_Tariff.Tariff
but also seem to want to sum and/or max of .Tariff. if so, .Tarriff shouldn't be in the group by. you also may want to consider using WITH ROLLUP.
May 28, 2009 at 7:04 am
My desired result is to count the number of attendances as activity and sum up the cost. However where I am selecting the cost i need to select the highest cost and then sum it up. What does the ROLL WITH statement do as I have never used it?
May 28, 2009 at 9:09 am
eseosaoregie (5/28/2009)
My desired result is to count the number of attendances as activity and sum up the cost. However where I am selecting the cost i need to select the highest cost and then sum it up. What does the ROLL WITH statement do as I have never used it?
Do you mean you want to :-
1. Identify the highest cost per event
2. Count each attendance as having that highest cost
3. Sum the total hypothetical cost
If so, you could multiply the attendances by the highest cost:
select count(1) as Attendances, EventID, (Select max(cost) from table where table.eventID = otherquery.eventID) as HighestCost
from (complex query)
group by EventID
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply