Ben Let's assume you have two tables, NewData, containing all the correct rows, and OldData, with rows missing in the date range. Assume the Date field is TransDate in both, and the other three fields are TerminalID, ResultCode and TransTime, existing in both tables.
The rows in NewData that are MISSING from OldData would be; SELECT * FROM NewData T1 WHERE T1.TerminalID NOT IN (SELECT T2.TerminalID FROM OldData WHERE T2.TerminalID = T1.TerminalID and T2.ResultCode = T1.ResultCode AND T2.TransTime = T1.TransTime) David Blocker [EMAIL PROTECTED] 781-784-1919 Fax: 781-784-1860 Cell: 339-206-0261 ----- Original Message ----- From: "Ben Fogt" <[EMAIL PROTECTED]> To: "RBG7-L Mailing List" <[EMAIL PROTECTED]> Sent: Tuesday, July 27, 2004 4:07 PM Subject: [RBG7-L] - Re: Joins? > Essentially I need to know what rows are missing from the one table for a > given date range. The uniqueness of the rows is provided through several > fields (terminal ID, a non-unique result code and time, most likely) So I > suppose that there's a simple way to select them but I'm not sure how. It > seems like a Loop routine would work fine, but I'd really like to learn how > to do this in a single SQL statement. > > Sorry for leaving out those details. > > Ben > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of David M. > Blocker > Sent: Tuesday, July 27, 2004 12:53 PM > To: RBG7-L Mailing List > Subject: [RBG7-L] - Re: Joins? > > > Ben > > What you're not making clear is how you can identify the 'incomplete' > transactions. IF you could suppose that there was a WHERE clause that > would define those rows, and IF you could import the other, COMPLETE data > into a new table, then we could help you! > > For example, if the COMPLETE data were in table NewTable with a PK column > PKID, and the same column is in the table that is INCOMPLETE (table > OldTable), then the "missing transactions" would simply be: > > SELECT * from NewTable WHERE PK_ID NOT IN (SELECT PK_ID FROM OldTable) > > I suspect it's not that simple, but you're not giving us any more > information to work with! > > David Blocker > [EMAIL PROTECTED] > 781-784-1919 > Fax: 781-784-1860 > Cell: 339-206-0261 > ----- Original Message ----- > From: "Ben Fogt" <[EMAIL PROTECTED]> > To: "RBG7-L Mailing List" <[EMAIL PROTECTED]> > Sent: Tuesday, July 27, 2004 11:46 AM > Subject: [RBG7-L] - Joins? > > > > Being fairly new to SQL and everything, it looks like I'm going to need to > > see if there's an easier way to do something with a join instead of a > > complicated series of temp tables. Here's the situation. > > > > Found out that some data that we received and processed was incomplete. I > > have the correct data now in several files. What I need to do is create a > > list of everything missing from our imports and eventually use that list > to > > process just the missing transactions. > > > > From what I know about Outer Joins (very limited) it doesn't look like it > > would do what I need. Is there something that would make this quick? > We're > > looking at about 30,000 transactions and the table missing transactions is > > over a million rows. > > > > (David, I've got your book, but it's at home. I promise I'll read > > everything as soon as I fix this!) > > > > Thanks for any help. > > > > Ben Fogt aka "The Shark" > > > > >
