Done! A slight variation of your suggestion...
SELECT c.CUSTOMER_NAME,
a.customerID,
a.total,
b.total_previous,
CASE
WHEN b.total_previous = 0 THEN 0
WHEN b.total_previous != 0 THEN ROUND((CAST(a.total as numeric) -
CAST(b.total_previous as numeric)) / (b.total_previous) * 100, 2)
END as total_pct_change,
a.amount,
b.amount_previous,
CASE
WHEN b.amount_previous = 0 THEN 0
WHEN b.amount_previous != 0 THEN (a.amount - b.amount_previous) /
b.amount_previous * 100
END as amount_pct_change
FROM
(SELECT SUM(v1.amount) as amount,SUM(v1.total) as total,v1.customerID
FROM view_customers v1
WHERE v1.txnDate >= {ts '2001-01-01 00:00:00'} AND v1.txnDate <= {ts
'2001-03-01 00:00:00'}
GROUP BY v1.customerID) a,
(SELECT SUM(v2.amount) as amount_previous,SUM(v2.total) as
total_previous,v2.customerID
FROM view_customers v2
WHERE v2.txnDate >= {ts '2000-01-01 00:00:00'} AND v2.txnDate <= {ts
'2000-03-01 00:00:00'}
GROUP BY v2.customerID) b,
CustomerMaster c
WHERE a.customerID=b.customerID AND
a.customerID=c.CUSTOMER
ORDER BY c.CUSTOMER_NAME asc
Works great and performance is excellent!
Thanks again.
-----Original Message-----
From: Stacy Young [mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 27, 2001 1:09 PM
To: SQL
Subject: RE: Date Range Comparison
Excellent, thanks!
-----Original Message-----
From: Dean H. Saxe [mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 27, 2001 12:39 PM
To: SQL
Subject: RE: Date Range Comparison
At 11:08 AM 9/27/01 -0400, you wrote:
>Wow, thanks...and I WILL pick up that book!
>
>What if v1.total_value somehow were zero? How does SQL handle these
>types of errors? "Can not divide by zero" would be a typical error code
>from my app server...
I didn't consider that. First, you could specify that the first date must
have a value other than 0, though I don't see why you would necessarily
have 0 based values. In Oracle you could use the decode() function as an
"if..then.." block to return 100% increase year-over-year if the first year
has a zero value, SQL server should have a similar function. You could
also do it as an outer join, since the method which I have chosen to do
this will only return value for which there are the same month across 2
different years, in which case you would have to look for 0 or NULL values
and use decode() to handle them properly.
-dhs
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Get the mailserver that powers this list at http://www.coolfusion.com
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
