January 10, 2006 at 8:46 am
This query, after I put in the comparison at the end produces no results. If I take out the last AND comparison it does. I can't figure out how to form my last AND statement correctly. I'm trying to incorporate CurrentBalance in a comparison to rmsbalance where the rmstrandte is the latest date for the record where rmsbalance is pulled
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 (rf.rmstranamt - rf.rmstranamt10) = (select top 1 rff.rmsbalance from RFINANL rff inner join RMASTER rrr ON rff.RMSFILENUM = rrr.RMSFILENUM
order by rff.rmstrandte desc)
January 10, 2006 at 9:04 am
Can you not pull out the last SELECT TOP 1 and store that in a variable. Then Not sure why you have () = (). Are you looking for an AND condiution there.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 10, 2006 at 9:11 am
So, the expression:
(rf.rmstranamt - rf.rmstranamt10) AS [Current Balance]
... is Current Balance ? And you want to filter on it by comparing it to the top 1 of another sub-query ? What are the data types in question ? If you leave off this filter, what are the values of [Current Balance] in your resultset ? What is the result of your top 1 query if you run it by itsef ? Are you perhaps getting into rounding issues where you're comparing money/numeric types to a float ?
January 10, 2006 at 9:35 am
I want to compare CurrentBalance with the rmsbalance but the rmsbalance whose record in RFINANL is related to rf.RMSFILENUM = rm.RMSFILENUM and that we are pulling rmsbalance based on the latest (max) rmstrandte from RFINANL for that record (where rf.RMSFILENUM = rm.RMSFILENUM) just like we have joined on rf.RMSFILENUM = rm.RMSFILENUM in the other select statements
January 10, 2006 at 10:04 am
This might work for you.
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
inner join
(select rff.RMSFILENUM,rff.rmsbalance,max(rmstrandte)rmstrandte
from rfinanl rff
group by rff.RMSFILENUM,rff.rmsbalance
) bal
on bal.rmsfilenum = rm.rmsfilenum
--where (rm.rmsacctnum = '4264287999892165' OR
--rm.rmsacctnum = '4264290999892300')
where 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 bal.rmsbalance = (rf.rmstranamt - rf.rmstranamt10)
Tom
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply