Larry - Excellent idea! But probably not this year - there are already SO MANY sessions on SO MANY topics that it's going to be difficult to squeeze them all in as it is! but I'll work on it!
See you Friday! Sami ----- Original Message ----- From: "Lawrence Lustig" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, April 12, 2002 9:03 PM Subject: Fw: Another complicated SQL query > 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/ > > > ================================================ 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/
