At 10:27 PM 9/26/01 -0400, you wrote:
>I have a table containing sales data. I need to get a months worth of data
>for a particular year and that same month the previous year and calculate
>percentage difference.
Interesting question, Stacy.
Joe Celko's _SQL For Smarties_ comes to the rescue again:
First, create a view from the table which holds your data, the query behind
the view should look something like the following pseudo-SQL:
SELECT
sum(sale_value) as total_value,
DATEPART(sale_date, month) || '/' || DATEPART(sale_date, year) as
new_sale_date
FROM
sales
GROUP BY
new_sale_date
This will give you a recordset that looks like:
total_value new_sale_date
$1099 1/2000
$2000 2/2000
$1583 1/2001
etc.
Then you can write one of two different queries (test for performance) to
get the desired data:
SELECT
v1.total_value as period1_total,
v2.total_value as period2_total,
(v2.total_value - v1.total_value) / v1.total_value * 100 || '%' as
year_over_year_percent_change,
v1.new_sale_date as period_1,
v2.new_sale_date as period_2
FROM
view v1,
view v2
WHERE
v1.new_sale_date = v2.new_sale_date - 1 year;
The self join like this is expensive because it causes a full table
scan. The alternative suquery method is:
SELECT
v1.total_value as period1_total,
(
SELECT
v2.total_value
FROM
view v2
WHERE
v1.new_sale_date = v2.new_sale_date - 1 year) AS
period2_total,
(
SELECT
(v2.total_value - v1.total_value) / v1.total_value
* 100 || '%' as year_over_year_percent_change,
FROM
v2
WHERE
v1.new_sale_date = v2.new_sale_date - 1 year) AS
year_over_year_percent_change
FROM
view v1;
That should give you what you need. Please note, it has not been tested, I
cannot vouch for its accuracy, I'll leave that up to you.
I cannot recommend the Celko book highly enough, its a great resource for
problems like these.
-dhs
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
