For some reason, the reply I tried to send to the list went directly to Karen's account. I'm forwarding it to the list in case anyone is interested.
Hey Sami -- How about an SQL competition at the conference? > Anyway, I think what you want is: > > SELECT BondNo FROM BondTrans B1 WHERE + > RepDate BETWEEN 1/1/02 AND 1/31/02 AND + > TranType <> 'nc' AND + > NOT EXISTS (SELECT * FROM BondTrans B2 WHERE + > B2.BondNo = B1.BondNo AND + > B2.RepDate BETWEEN 1/1/02 AND 1/31/02 AND + > B2.TranType = 'fr' AND + > B2.RepDate > B1.RepDate) > > In translation, this asks R:Base to get you all the activity records for > January for which it CANNOT find an FR record for the same bond in the same > month at a later date. If the FR record can occur on the SAME date, you > need to change the final > sign to >=. > > > What I need to do is trap for records where there has been activity > > during the month (defined as anything other than 'NC') and there > > is no 'FR' yet. Something like: > > select bondno from bondtrans where > > repdate between 1/1/02 and 1/31/02 > > and trantype <> 'nc' and > > < there is no 'fr' transaction during that period> > > > > This gets me ALMOST there. > > > > The biggest winner will be the one to take this one step further. > > There can be MULTIPLE 'FR' transactions. They could post all the > > activity, generate this 'FR', then post more activity but forget > > to do another 'FR'. So what I really need to do is check for the > > absence of an 'FR' following the LATEST trantype <> 'nc' > -- > Larry Lustig > www.larrylustig.com ================================================ 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/
