Using a sub query in a where clause

Question

I need to have the results from this reports only show patients with a status of I. Not really sure how to do it where this report is so sub query heavy with a lot of them being correlated. Not sure if I could put a sub query in a where statement that would blanket the entire thing. Thanks in advance for any help. SQL Server 2005

SELECT patient_id,
   (
       SELECT p.case_status
       FROM   patient p
       WHERE  p.patient_id = btb.patient_id
              AND p.episode_id = (
                      SELECT MAX(episode_id)
                      FROM   patient p2
                      WHERE  p2.patient_id = p.patient_id
                  )
   )                            AS 'Status',
   (
       SELECT p.lname + ', ' + p.fname
       FROM   patient p
       WHERE  p.patient_id = btb.patient_id
              AND p.episode_id = (
                      SELECT MAX(episode_id)
                      FROM   patient p2
                      WHERE  p2.patient_id = p.patient_id
                  )
   )                            AS 'client',
   Coverage_plan_id,
   (
       SELECT proc_code
       FROM   billing_transaction bt
       WHERE  bt.clinical_transaction_no = btb.clinical_transaction_no
              AND bt.coverage_plan_id = btb.coverage_plan_id
              AND bt.coverage_plan_id = btb.coverage_plan_id
   )                            AS 'Procedure',
   proc_chron,
   (
       SELECT billing_amt
       FROM   billing_transaction bt
       WHERE  bt.clinical_transaction_no = btb.clinical_transaction_no
              AND bt.coverage_plan_id = btb.coverage_plan_id
   )                            AS 'Billing Amount',
   balance_amount,
   (
       SELECT MAX(accounting_date)
       FROM   billing_ledger bl
       WHERE  bl.clinical_transaction_no = btb.clinical_transaction_no
              AND subtype = 'pa'
              AND bl.coverage_plan_id = 'standard'
   )                            AS 'Last Payment on Trans',
   (
       SELECT MAX(instrument_date)
       FROM   payment p
       WHERE  p.patient_id = btb.patient_id
              AND p.coverage_plan_id = 'standard'
   )                            AS 'Last Payment on Acct',
   (
       SELECT SUM(balance_amount)
       FROM   billing_transaction_balance btb2
       WHERE  btb2.patient_id = btb.patient_id
              AND btb2.coverage_plan_id = btb.coverage_plan_id
              AND proc_chron <= CONVERT(
                      CHAR(6),
                      DATEADD(YEAR, -1, DATEDIFF(DAY, 0, GETDATE())),
                      112
                  ) + '01'
              AND btb2.coverage_plan_id   IN ('standard')
   )                            AS 'Balance'
FROM   billing_transaction_balance     btb
WHERE  proc_chron <= CONVERT(
           CHAR(6),
           DATEADD(YEAR, -1, DATEDIFF(DAY, 0, GETDATE())),
           112
       ) + '01'
       AND coverage_plan_id   IN ('standard')
GROUP BY
       patient_id,
       proc_chron,
       coverage_plan_id,
       balance_amount,
       clinical_transaction_no

Answer 1

Your FROM clause only includes one table (billing_transaction_balance) and that table is referenced by every sub-query.

If you limit the results of that one table to only those with Status='I', it will "blanket" your entire query. You can do this most effectively with an INNER JOIN to the table that has the Status column in it.