Last Two Rows

I have a company table that lists the company name,
fiscal year, and fees. I am trying to compare the last
two years without specifically setting the criteria
because I want the data to drive the comparison between
the two.
Adding to the complexity is the fact that a company can
have two rows with the same year and different fees so I
would need to sum the fees for the year first and then do
the computation to display the percent change from year
to year.
An example of the data would be:
Microsoft 2005 2000000
Microsoft 2004 1750000
Dell 2004 1200000
Dell 2003 1100000
HP 2004 500000
HP 2004 250000
HP 2003 600000
I would need the output to look like this:
Microsoft 14.29%
Dell 9.09%
HP 25.00%
Thanks.How does the percentage calcuates from ?
"daniel" <anonymous@.discussions.microsoft.com> schrieb im Newsbeitrag
news:0c3001c54100$6fa29aa0$a401280a@.phx.gbl...
>I have a company table that lists the company name,
> fiscal year, and fees. I am trying to compare the last
> two years without specifically setting the criteria
> because I want the data to drive the comparison between
> the two.
> Adding to the complexity is the fact that a company can
> have two rows with the same year and different fees so I
> would need to sum the fees for the year first and then do
> the computation to display the percent change from year
> to year.
> An example of the data would be:
> Microsoft 2005 2000000
> Microsoft 2004 1750000
> Dell 2004 1200000
> Dell 2003 1100000
> HP 2004 500000
> HP 2004 250000
> HP 2003 600000
> I would need the output to look like this:
> Microsoft 14.29%
> Dell 9.09%
> HP 25.00%
> Thanks.|||the % change is calculated by subtracting the latest date
fee from last years fee. Then divide the difference by
last years fee.
So in MSFT's case subtract 2000000-1750000 = 250000 and
then divide 250000/1750000.
>--Original Message--
>How does the percentage calcuates from ?
>
>"daniel" <anonymous@.discussions.microsoft.com> schrieb
im Newsbeitrag
>news:0c3001c54100$6fa29aa0$a401280a@.phx.gbl...
I
do
>
>.
>|||Using the following table structure and data:
CREATE TABLE CompanyFees (
Company varchar(50) NOT NULL,
FiscalYear smallint NOT NULL,
Fees money NOT NULL
)
INSERT INTO CompanyFees VALUES ('Microsoft', 2005, 2000000)
INSERT INTO CompanyFees VALUES ('Microsoft', 2004, 1750000)
INSERT INTO CompanyFees VALUES ('Dell', 2004, 1200000)
INSERT INTO CompanyFees VALUES ('Dell', 2003, 1100000)
INSERT INTO CompanyFees VALUES ('HP', 2004, 500000)
INSERT INTO CompanyFees VALUES ('HP', 2004, 250000)
INSERT INTO CompanyFees VALUES ('HP', 2003, 600000)
Use this query:
SELECT a.Company, a.FiscalYear, a.SumOfFees as ThisYearFees,
b.SumOfFees as PreviousYearFees,
(1.*a.SumOfFees/b.SumOfFees-1)*100 as Variation
FROM (
SELECT Company, FiscalYear, SUM(Fees) as SumOfFees
FROM CompanyFees GROUP BY Company, FiscalYear
) a INNER JOIN (
SELECT Company, FiscalYear, SUM(Fees) as SumOfFees
FROM CompanyFees GROUP BY Company, FiscalYear
) b ON a.Company=b.Company AND a.FiscalYear=b.FiscalYear+1
Razvan|||What is the primary key? Please help us to help you by including DDL
with future posts.
Try:
SELECT company, yr,
(SELECT (SUM(T.fee)-SUM(fee))
/CAST(SUM(fee) AS REAL)*100
FROM YourTable
WHERE company = T.company
AND yr = T.yr - 1) AS pct
FROM YourTable AS T
WHERE yr =
(SELECT MAX(yr)
FROM YourTable
WHERE company = T.company)
GROUP BY company, yr
David Portas
SQL Server MVP
--|||Try,
use northwind
go
create table t (
company varchar(25),
fiscal_year int,
fee int
)
go
insert into t values('Microsoft', 2005, 2000000)
insert into t values('Microsoft', 2004, 1750000)
insert into t values('Dell', 2004, 1200000)
insert into t values('Dell', 2003, 1100000)
insert into t values('HP', 2004, 500000)
insert into t values('HP', 2004, 250000)
insert into t values('HP', 2003, 600000)
insert into t values('YAHOO', 2005, 1600000)
go
select
a.company,
cast(max(((a.sum_fee * 100.00) / b.sum_fee) - 100.00) as decimal(5, 2)) as
percent_change
from
(
select
company,
fiscal_year,
sum(fee) as sum_fee
from
t
where
(select count(distinct c.fiscal_year) from t as c where c.company =
t.company and c.fiscal_year >= t.fiscal_year) <= 2
group by
company,
fiscal_year
) as a
inner join
(
select
company,
fiscal_year,
sum(fee) as sum_fee
from
t
where
(select count(distinct c.fiscal_year) from t as c where c.company =
t.company and c.fiscal_year >= t.fiscal_year) <= 2
group by
company,
fiscal_year
) as b
on a.company = b.company
and a.fiscal_year = b.fiscal_year + 1
group by
a.company
go
drop table t
go
AMB
"daniel" wrote:

> I have a company table that lists the company name,
> fiscal year, and fees. I am trying to compare the last
> two years without specifically setting the criteria
> because I want the data to drive the comparison between
> the two.
> Adding to the complexity is the fact that a company can
> have two rows with the same year and different fees so I
> would need to sum the fees for the year first and then do
> the computation to display the percent change from year
> to year.
> An example of the data would be:
> Microsoft 2005 2000000
> Microsoft 2004 1750000
> Dell 2004 1200000
> Dell 2003 1100000
> HP 2004 500000
> HP 2004 250000
> HP 2003 600000
> I would need the output to look like this:
> Microsoft 14.29%
> Dell 9.09%
> HP 25.00%
> Thanks.
>|||Thanks Razvan. I had to check for divide by zero but
other than that it works perfecty!
>--Original Message--
>Using the following table structure and data:
>CREATE TABLE CompanyFees (
> Company varchar(50) NOT NULL,
> FiscalYear smallint NOT NULL,
> Fees money NOT NULL
> )
>INSERT INTO CompanyFees VALUES ('Microsoft', 2005,
2000000)
>INSERT INTO CompanyFees VALUES ('Microsoft', 2004,
1750000)
>INSERT INTO CompanyFees VALUES ('Dell', 2004,
1200000)
>INSERT INTO CompanyFees VALUES ('Dell', 2003,
1100000)
>INSERT INTO CompanyFees VALUES ('HP', 2004,
500000)
>INSERT INTO CompanyFees VALUES ('HP', 2004,
250000)
>INSERT INTO CompanyFees VALUES ('HP', 2003,
600000)
>Use this query:
>SELECT a.Company, a.FiscalYear, a.SumOfFees as
ThisYearFees,
> b.SumOfFees as PreviousYearFees,
> (1.*a.SumOfFees/b.SumOfFees-1)*100 as Variation
>FROM (
> SELECT Company, FiscalYear, SUM(Fees) as SumOfFees
> FROM CompanyFees GROUP BY Company, FiscalYear
> ) a INNER JOIN (
> SELECT Company, FiscalYear, SUM(Fees) as SumOfFees
> FROM CompanyFees GROUP BY Company, FiscalYear
> ) b ON a.Company=b.Company AND
a.FiscalYear=b.FiscalYear+1
>Razvan
>.
>|||one question though. what does the 1. mean in the
Column: (1.*a.SumOfFees/b.SumOfFees-1)*100 as Variation
>--Original Message--
>Using the following table structure and data:
>CREATE TABLE CompanyFees (
> Company varchar(50) NOT NULL,
> FiscalYear smallint NOT NULL,
> Fees money NOT NULL
> )
>INSERT INTO CompanyFees VALUES ('Microsoft', 2005,
2000000)
>INSERT INTO CompanyFees VALUES ('Microsoft', 2004,
1750000)
>INSERT INTO CompanyFees VALUES ('Dell', 2004,
1200000)
>INSERT INTO CompanyFees VALUES ('Dell', 2003,
1100000)
>INSERT INTO CompanyFees VALUES ('HP', 2004,
500000)
>INSERT INTO CompanyFees VALUES ('HP', 2004,
250000)
>INSERT INTO CompanyFees VALUES ('HP', 2003,
600000)
>Use this query:
>SELECT a.Company, a.FiscalYear, a.SumOfFees as
ThisYearFees,
> b.SumOfFees as PreviousYearFees,
> (1.*a.SumOfFees/b.SumOfFees-1)*100 as Variation
>FROM (
> SELECT Company, FiscalYear, SUM(Fees) as SumOfFees
> FROM CompanyFees GROUP BY Company, FiscalYear
> ) a INNER JOIN (
> SELECT Company, FiscalYear, SUM(Fees) as SumOfFees
> FROM CompanyFees GROUP BY Company, FiscalYear
> ) b ON a.Company=b.Company AND
a.FiscalYear=b.FiscalYear+1
>Razvan
>.
>|||Making sure it is converted to a decimal instead of doing integer division
(which yields an integer, e.g. 45/100 = 0).
http://www.aspfaq.com/2483
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"daniel" <anonymous@.discussions.microsoft.com> wrote in message
news:025501c5410a$07c226d0$a501280a@.phx.gbl...
> one question though. what does the 1. mean in the
> Column: (1.*a.SumOfFees/b.SumOfFees-1)*100 as Variation
> 2000000)
> 1750000)
> 1200000)
> 1100000)
> 500000)
> 250000)
> 600000)
> ThisYearFees,
> a.FiscalYear=b.FiscalYear+1|||Another approach:
SELECT Company,
100 * ( MAX( CASE seq WHEN 0 THEN Fees END ) /
MAX( CASE seq WHEN 1 THEN Fees END ) - 1 )
FROM ( SELECT c1.Company, c1.FiscalYear, SUM( c1.Fees ),
( SELECT COUNT( DISTINCT c2.FiscalYear )
FROM CompanyFees c2
WHERE c2.Company = c1.Company
AND c2.FiscalYear > c1.FiscalYear )
FROM CompanyFees c1
GROUP BY c1.Company, c1.FiscalYear
) D ( Company, FiscalYear, Fees, seq )
GROUP BY Company ;
Anith