Just taking a stab at this... don't have a SQL box to test it with... SELECT sum(orderamt), (
SELECT TOP 1 orderdate FROM orders or WHERE or.orderdate <= '1/1/2001' AND or.orderdate >= '1/1/2002' AND or.customerid = cu.customerid ORDER BY orderdate ) first_order FROM orders or JOIN customers cu ON or.customerid = cu.customerid WHERE first_order IS NOT NULL -----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
