Here are two possibilities.. the first is prolly best and works only if your ID's are integer based.. The 2nd is just flat out ugly but should work.. I'd explore it a little further before implementing either of these if i were you, but seeing how you've had no responses yet thought I'd just throw this out there..
select c.customerid, o.orderdate from customers c, orders o where c.customerid = o.customerid and o.orderid = (select min orderid from orders o2 where o2.customerid= c.customerid) and o.orderdate >= @Min_Date and o.orderdate <= @Max_Date select distinct c.customerid, orderdate = (select min orderdate from orders o2 where o2.customerid = c.customerid) from customers c, orders o where c.customerid = o.customerid and o.orderdate >= @Min_Date and o.orderdate <= @Max_Date Nat -----Original Message----- From: Andres [mailto:[EMAIL PROTECTED]] Sent: Monday, March 04, 2002 12:55 PM To: CF-Talk Subject: getting customers that placed their first order in a given time period Hello everyone, I have two sql server 2000 db tables with the following fields in each: orders==== orderid customerid orderdate orderamt customers======= customerid fname lname how can i query the total amount (sum(orderamt)) purchased by customers who placed THEIR FIRST ORDER in a given time period? I have been successfull in getting this info for customers who have placed ONE OF THEIR ORDERS in a time period, but i do not know how to get the first order for each customer. Any help will be much appreciated! -------------------------------------------- Andres Leon [EMAIL PROTECTED] Vitacost.com 2049 High Ridge Road Boynton Beach, Fl 33426 ______________________________________________________________________ Why Share? Dedicated Win 2000 Server � PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation � $99/Month � Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
