So I have a table, each row is a sale and each sale has a customer# and a date

TableName = Sales

Date Customer#
1-1-2005 18
5-8-2004 14
1-19-2001 18
1-25-2006 16
10-8-2004 14
3-6-2000 16

and i want a report which has each customer # and the first and last date of sales to that customer.

How would i write this query?

1/31/2006 2:27:44 PM

SELECT customer_number, max(date), min(date) FROM Sales GROUP BY customer_number

1/31/2006 2:43:31 PM

yeah that works

[Edited on January 31, 2006 at 3:30 PM. Reason : *]

1/31/2006 3:30:09 PM

if that's not working, you probably have a retarded kind of SQL that doesn't handle dates well, in which case you'll just need to convert them to and from integer timestamps inside and outside the max/mins.

1/31/2006 5:41:01 PM

Assuming your data is exactly what you're getting from the database...

You'd smack the shit out of whoever didn't use a date, datetime, or timestamp column to handle the date information.

2/1/2006 2:00:44 PM

^unless you're using Oracle, in which case using datetime doesn't really work all that well for collecting unknown date fields. Not relevant for timestamps though.

2/1/2006 2:37:09 PM

^^ no those are made up values, we use a stamp to get the exact time

qntmfred's suggestion worked perfectly, though it gets a little more complex here:

Say I want a list, of each customer's name, their customer number, their first date of purchase, their last date of purchase, their first return date, and their last return date. I would think it would involve a couple joins, and/or IN statements but I get a little twisted around when I start factoring in the distinct and group by logic.

How would you write that query?

TableName = Sales

Date Customer#
1-1-2005 18
5-8-2004 14
1-19-2001 18
1-25-2006 16
10-8-2004 14
3-6-2000 16

TableName = Returns

ReturnDate Customer #
1-8-2005 18
1-30-2001 18
6-10-2004 14
3-8-2000 16

TableName = CustInfo

Customer # Name
18 Edgar Codd
16 Steve Jobs
14 Larry Ellis

And I am using MS SQL if that matters.

2/1/2006 2:58:27 PM

SELECT customers.id, customers.name, min( sales.date ) , max( sales.date ) , min( returns.date ) , max( returns.date )
FROM sales, returns, customers
WHERE sales.cust_id = customers.id
AND returns.cust_id = customers.id
GROUP BY customers.id

2/1/2006 3:22:06 PM

assuming all data formats match - that should work just fine

2/1/2006 3:33:14 PM

^^
bwn?

2/1/2006 6:45:16 PM

first you want to see pics of me in a speedo, now this?

2/1/2006 8:51:24 PM