Friday, March 9, 2012

Reuse calculation later on in query

How can I reuse the field CurrentBalance in my last AND statement?

SELECT rm.rmsacctnum AS [Rms Acct Num],
rf.rmstranamt10 as total_10,
rf.rmstranamt,
(rf.rmstranamt - rf.rmstranamt10) AS [Current Balance]
FROM RMASTER rm
INNER JOIN
(
SELECT
RMSFILENUM,
RMSTRANCDE,
SUM(rmstranamt) AS rmstranamt,
SUM(CASE WHEN RMSTRANCDE = '10' THEN rmstranamt ELSE 0 END) AS rmstranamt10
FROM RFINANL
GROUP BY RMSFILENUM, RMSTRANCDE
) AS rf ON rf.RMSFILENUM = rm.RMSFILENUM
--where (rm.rmsacctnum = '4264287999892165' OR
--rm.rmsacctnum = '4264290999892300')
AND rf.RMSTRANCDE IN ('16','18','19','20','21','22','29','30','31','36','37','38','3A','3B','3C','3D','3E','3F','3M','3N','3O','3P','3Q','3R','3T',
'3U','3X','3Z','40','41','42','43','44','45','46','47','48','49','4A','4B','4D','4E','4H','4J','4X','4Z','50','51','52','53',
'55','56','57','58','5A','5B','5C','5P','5Q','5R','5X','5Z')
AND [Current Balance] <> (select rff.rmsbalance from RFINANL rff inner join RMASTER rrr ON rff.RMSFILENUM = rrr.RMSFILENUM
where rrr.rmstrandate = ( select max( rmstrandate) from RMASTER) )

You can use a derived table or view or inline TVF or CTE (Only SQL Server 2005) to encapsulate the SELECT statement with the calculated expressions and then use it in your SELECT statement. If you do not want to create persistent objects then derived table or CTE is the way to go. Ex:

-- derived table

select j

from (select i + 1 from t) as t1(j)

where j > 10;

-- cte

with t1 (j)

as

(

select i+1 from t

)

select j from t1

where j > 10;

No comments:

Post a Comment