In following query I'm use both Common Table Expression and Outer queries. Apparently both looks same to me.
If I summarize my requirement, I have employees in
SalaryTrans table and I want to find out amounts to be recovered from their salaries monthly. Table
Rcovery contains all recovery data. Table
Instalment contains all previously paid amounts so I want to sum all paid amounts and then it is deducted from total amount to calculate
So what I'm doing here is in
CTE, I'm getting two numeric fields called
total_amount. In second query I'm checking whether
recovered_amount is not null and if it's not null I'm calculating the
In outer query I have a criteria to filter out less than 0 balances from the result.
WITH CTE (employee_id, RID, instalment, reference, recovered_amount, total_amount) AS (SELECT SalaryTrans.employee_id, Recovery.RID, Recovery.amount / Recovery.duration AS instalment, SalaryTrans.reference, SUM(Instalment.recovered_amount) AS recovered_amount, Recovery.amount AS total_amount FROM Recovery INNER JOIN SalaryTrans ON Recovery.emp_id = SalaryTrans.employee_id LEFT OUTER JOIN Instalment ON Recovery.RID = Instalment.recovery_id GROUP BY SalaryTrans.employee_id, SalaryTrans.reference, Recovery.RID, Recovery.amount / Recovery.duration, Recovery.amount) SELECT RID, Balance, reference FROM (SELECT RID, "Balance" = CASE WHEN recovered_amount !=NULL THEN total_amount-recovered_amount ELSE total_amount END , reference FROM CTE) AS Result WHERE Balance >0
This query gives intended output. But I want to know Can I do whole thing using CTE or simply should I use outer queries in this case. Also any idea to make it look better?
Could you please review this and give your feedback?