|
|
Hooray! I think this did the trick! Thank you so kindly for your patience and
for sharing your knowledge!
Here's the final code:
<cfquery datasource="#db#" name="paidReg">
SELECT
wr.EmployeeID AS wr_EmployeeID,
wr.WorkshopRegistrationID AS wr_WorkshopRegistrationID,
wr.WorkshopSessionID AS wr_WorkshopSessionID,
wr.WorkshopRegPaymentID AS wr_WorkshopRegPaymentID,
wr.WorkshopPaid AS wr_WorkshopPaid,
wr.WorkshopCompleted AS wr_WorkshopCompleted,
wrp.WorkshopRegPaymentID AS wr_WorkshopRegPaymentID,
COALESCE(wrp.CheckAmount,0) AS wrp_CheckAmount,
wrp.SchoolID AS wrp_SchoolID,
wes.WorkshopSessionID AS wes_WorkshopSessionID,
COALESCE(wes.WorkshopSessionCost,0) AS wes_WorkshopSessionCost,
e.EmployeeID AS e_EmployeeID,
e.SchoolID AS e_SchoolID,
s.SchoolID AS s_SchoolID,
s.SchoolName AS s_SchoolName
FROM WorkshopRegistration as wr
LEFT JOIN WorkshopRegPayment AS wrp
ON wr.WorkshopRegPaymentID = wrp.WorkshopRegPaymentID
LEFT JOIN WorkshopEventSessions AS wes ON wr.WorkshopSessionID =
wes.WorkshopSessionID
LEFT JOIN Employee AS e ON wr.EmployeeID = e.EmployeeID
INNER JOIN School AS s ON e.SchoolID = s.SchoolID
WHERE wr.WorkshopPaid = 'True'
AND wr.WorkshopCompleted = 'True'
AND wes.WorkshopSessionCost <> 0
ORDER BY s_SchoolName
</cfquery>
<!--- use a QoQ to calculate preliminary subtotals for Checks Received--->
<cfquery name="getSTChecks" dbtype="query">
SELECT s_SchoolName, SUM(wrp_CheckAmount) AS STChecks
FROM paidReg
GROUP BY s_SchoolName
</cfquery>
<!--- use a QoQ to calculate preliminary subtotals for WorkshopSessionCost--->
<cfquery name="getSTWSCost" dbtype="query">
SELECT s_SchoolName, SUM(wes_WorkshopSessionCost) AS STWSCost
FROM paidReg
GROUP BY s_SchoolName
</cfquery>
<cfquery name="getSubTotal" dbtype="query">
SELECT getSTChecks.s_SchoolName AS s_SchoolName,
getSTWSCost.s_SchoolName,
SUM(getSTChecks.STChecks - getSTWSCost.STWSCost) AS SubTotal
FROM getSTChecks, getSTWSCost
WHERE getSTChecks.s_SchoolName = getSTWSCost.s_SchoolName
GROUP BY s_SchoolName
</cfquery>
<!--- join it back to your main query using another a QoQ--->
<cfquery name="paidReg" dbtype="query">
SELECT paidReg.*, getSubTotal.SubTotal, getSubTotal.s_SchoolName AS
s_SchoolName
FROM paidReg, getSubTotal
WHERE paidReg.s_SchoolName = getSubTotal.s_SchoolName
</cfquery>
|
|