using column values to compute other columns

[SirDaveTheBrave]

Hey guys.
I'm trying something I've never done before.

Basically what I want to do is use the value of a column (in this case 'App Id') to allow me to calculate on the fly how much is owed on a particular application (oustanding)

The code below will produce the correct result for when when I hard code in a value for application_id_int (in this case 76)

I want to be able to use the application_id_int returned in the first column as the value that is used in the nested select statement.

Any ideas folks? Help much appreciated.

DS

SELECT 
 
		claimant.claimant_id_int AS 'Claimant', 
			application_id_int AS 'App ID', 
			application_date AS DATE,  
			claimant.office_no_int AS Office,  
			application_type_id_int AS TYPE, 
			amount_requested_dec AS Requested, 
			amount_awarded_dec AS Awarded,
 
 
			(SELECT(Amount_awarded_dec) FROM [Application] WHERE application_id_int = '76') - (SELECT ISNULL(SUM(repayment_amount_dec), 0) FROM repayments WHERE application_id_int = '76') AS Outstanding,
 
 
			claimant.username_str AS Username
		FROM dbo.application, dbo.claimant
		WHERE (claimant.claimant_id_int = @claimant_id) AND claimant.claimant_id_int = application.claimant_id_int

[r937]

SELECT claimant.claimant_id_int AS 'Claimant'
     , application_id_int AS 'App ID'
     , application_date AS Date
     , claimant.office_no_int AS Office
     , application_type_id_int AS Type
     , amount_requested_dec AS Requested
     , amount_awarded_dec AS Awarded
     , Amount_awarded_dec - COALESCE(r.repayments,0) AS Outstanding
     , claimant.username_str AS Username
  FROM dbo.application
INNER
  JOIN dbo.claimant
    ON claimant.claimant_id_int = application.claimant_id_int 
LEFT OUTER
  JOIN ( SELECT application_id_int
              , SUM(repayment_amount_dec) AS repayments
           FROM repayments
         GROUP
             BY application_id_int ) AS r
    ON r.application_id_int = application.application_id_int
 WHERE claimant.claimant_id_int = @claimant_id

[SirDaveTheBrave]

Thanks a million for the quicke reply.
Still getting a small error and since your code is something preeeetty complicated for me I'm not sure what I should be doing to correct it!

Line 8
The multi-part identifier "application.application_id_int" could not be bound.

Thanks again.

DS

[r937]

that's because i didn't realize (and your query did not specify) which table that column happens to be in

prefix it correctly and let's see what happens