Thank you Jochem, I knew it had to be possible! I didn't think to link the table to itself.
Josh > -----Original Message----- > From: Jochem van Dieten [mailto:[EMAIL PROTECTED] > Sent: Monday, June 30, 2003 11:43 AM > To: CF-Talk > Subject: Re: SQL Repeated Number Query > > > Josh Remus wrote: > > > > I have a RA database which tracks incoming returns and service > performed on > > those units. That table (RA) contains a SerialNumber and a > CreateDate/Time > > stamp. What I need is SQL that will check if there is more > than one entry > > for a particular serial number in a 90-day time-span. > > > > IE: > > > > RAID SerialNumber CreateDate > > -------------------------------------------------------------- > > 1 ABC12345 05/20/2003 > > 2 DFA23466 05/21/2003 > > 3 GBA16623 06/01/2003 > > etc > > etc > > 156 ABC12345 07/26/2003 > > > > Since ABC12345 occured twice within a 90-day period, I need to get that > > serial number. > > SELECT DISTINCT > a.SerialNumber > FROM RA a INNER JOIN RA b ON a.SerialNumber = b.SerialNumber > WHERE a.CreateDate - b.CreateDate < INTERVAL '90 DAYS' > AND > a.RAID <> b.RAID > > Jochem > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
