Hello all: I will look at any responses that may have come to my earlier email, but I found a perfect solution.
I was talking to Jeff Ward and he suggested a solution that I actually use at another client! My problem was that I was looking at the problem from beginning transaction to ending, when in fact I should have looked at ending to beginning! Remember I wanted all bonds with activity where the final transaction is not 'FR'. The added complication was that there could be >1 'FR' in a month so there had to be an 'FR' after the last transaction. All I really needed to do is determine what the last transaction is (ignoring the 'NC' transaction that I don't want). From there it is a simple select to find those where the last transaction is not 'FR'. I use this exact view at another client but didn't even realize I'm doing the same thing here. Here's the view: CREATE VIEW vFinal + (Bond#, Repdate, TranType) + AS SELECT bond#, Repdate, TranType FROM BondTrans t1 + WHERE tran# = + (SELECT MAX(tran#) FROM BondTrans t2 + WHERE t1.bond# = t2.bond# and trantype <> 'nc') Now I can do a "select all from vFinal where repdate between ..... and TranType <> 'fr' " to find my missing bonds! I haven't read any of the messages yet, so if anyone guessed this -- congratulations! Karen ================================================ TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: INTRO rbase-l ================================================ TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: UNSUBSCRIBE rbase-l ================================================ TO SEARCH ARCHIVES: http://www.mail-archive.com/rbase-l%40sonetmail.com/
