synapse play so hard 60940 Posts user info edit post |
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 |
qntmfred retired 40817 Posts user info edit post |
SELECT customer_number, max(date), min(date) FROM Sales GROUP BY customer_number 1/31/2006 2:43:31 PM |
30thAnnZ Suspended 31803 Posts user info edit post |
yeah that works
[Edited on January 31, 2006 at 3:30 PM. Reason : *] 1/31/2006 3:30:09 PM |
Shadowrunner All American 18332 Posts user info edit post |
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 |
Stein All American 19842 Posts user info edit post |
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 |
Maugan All American 18178 Posts user info edit post |
^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 |
synapse play so hard 60940 Posts user info edit post |
^^ 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 |
qntmfred retired 40817 Posts user info edit post |
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 |
Breezer95 All American 6304 Posts user info edit post |
assuming all data formats match - that should work just fine 2/1/2006 3:33:14 PM |
Perlith All American 7620 Posts user info edit post |
^^ bwn? 2/1/2006 6:45:16 PM |
qntmfred retired 40817 Posts user info edit post |
first you want to see pics of me in a speedo, now this? 2/1/2006 8:51:24 PM |