Off the top of my head, I'd do something like this (you'll have to write in proper SQL...):
SELECT Sales_Guy, sum(CASE WHEN date is 2006 then sales_made else 0 end) as dec_06, sum(case when date is 2005 then sales_made else 0 end) as dec_05, ABS(sum(CASE WHEN date is 2006 then sales_made else 0 end)-sum(case when date is 2005 then sales_made else 0 end)) as DIFF group by sales_guy ----- Original Message ----- From: David Mark Weiss To: [email protected] Sent: Tuesday, January 09, 2007 11:35 AM Subject: Witango-Talk: Challenging query Hi all, Happy New Year! I have an app I need to write that I have written before, but thought I would see if someone has a snazzy way of doing this that I have never thought of The goal is to create a report, that for a salesman, grabs all of his customers and for each one, sales for this preceding month and sales for the same month a year previous. Then compare. So Sales guy Name Dec 06 Dec 05 Diff Cust. 1 40 30 +10 Cust. 1 60 40 +20 Etc. I normally do this with two queries, forming and sorting an array and then break processing in a @rows loop. If there was a way to get the two monthly values in a single SQL statement, I guess that is what I would be looking for. Witango 5.5 Primebase ODBC thank you for always being there for feedback and help. Mark Weiss http://trustthechildren.blogspot.com ________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf ________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
