Using Common Table Expression and Outer queries 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 balance amount.

So what I'm doing here is in CTE, I'm getting two numeric fields called recovered_amount and total_amount. In second query I'm checking whether recovered_amount is not null and if it's not null I'm calculating the balance.

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 &gt;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?
I find the query rather difficult to read due to your indentation style.  For your benefit and for the benefit of other reviewers, I've reformatted your query, changing only the whitespace:

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 &gt; 0


In particular, note:


For anything other than a trivial SELECT, use a separate line for each column being selected.  (My use of leading commas may be controversial.  I find that it helps prevent silly mistakes when editing the query to add and remove columns.)
The indentation matches the clauses of each expression.  For example,

The big-picture structure is WITH (CTEs) SELECT (something).

Within the CTE, SELECT takes a FROM clause and a GROUP BY clause.

The FROM clause consists of two JOINs.

The main SELECT has a FROM clause and a WHERE clause.

Within the CASE, make it more obvious that the THEN is associated with WHEN.



Use a space on each side of binary operators such as !=, -, and &gt;.