Calculate Percentage difference-Derived tables? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Calculate Percentage difference-Derived tables?

Author

Topic

blinton25
Yak Posting Veteran

90 Posts

Posted - 2003-08-07 : 16:42:22

Hello,

How are you today?

I run the following SQL statement:

CREATE PROCEDURE TouristArrivalsCrosstab

SELECT Book1.COUNTRY, DatePart('yyyy',[ARRIVAL_DATE],1,0), Book1.ED_CARD
FROM Book1
ORDER BY 2 ASC, 1 ASC

which produces the following output:

Country 2002 2003
AUSTRALIA 45 50
AUSTRIA 2 4

I wish to calculate the percentage difference between years,

(2003figure-2002figure)/2002figure * 100

and display it as a third column.

I figure I can use a derived table but I am not sure how to proceed.

Can you assist?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-07 : 18:18:03

What does figure mean? Using your result set, what would the formula look like? I also don't understand your data. What is ED_CARD? You already have 3 columns in your result set, so you want the calculation to be the fourth?

Tara

nr
SQLTeam MVY

12543 Posts

Posted - 2003-08-07 : 18:29:02

And does that SP really produce that output?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

blinton25
Yak Posting Veteran

90 Posts

Posted - 2003-08-07 : 20:57:45

Hello,

When I said figure, I meant the value produced in the result set. So for example in the case of Australia, the percentage increase would be:

(50-45)/45*100= 11.1% increase from 2002 to 2003.

and my ideal result set would look like:

Country 2002 2003 % Increase
AUSTRALIA 45 50 11.1%

ED_CARD is a unique field in that table which I use to count the number of each country. The program that I am using allows me to generate a count using ED_CARD. (Although in retrospect since the program isn't allowing me to carry out the calculation I should modify
the query to generate a count. Hope this answers nr's comment)

Let me know if you require any additional information to clarify.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-07 : 21:28:04

That doesn't answer his question ... your stored procedure does NOT return the results you are showing us. How are you performing the cross-tab ?

Do you see that

SELECT Book1.COUNTRY, DatePart('yyyy',[ARRIVAL_DATE],1,0), Book1.ED_CARD
FROM Book1
ORDER BY 2 ASC, 1 ASC

returns data in the form of

a,2001,b
a,2002,b
a,2003,b

NOT in the form of

a,2001,2002,2003

???

- Jeff

blinton25
Yak Posting Veteran

90 Posts

Posted - 2003-08-07 : 22:27:13

Hello,

You are correct, the data is returned in the form:

COUNTRY Expr1001 ED_CARD
Austria 2002 D189106
Austria 2003 D40494

The application I am using allows me to specify that I want to crosstab on years, and count on ED_CARD producing the output which I posted.

However, since the application doesn't allow me to calculate the percentage difference, I decided to use a pure SQL instead.

So since I can't depend on the application to generate a crosstab, how do I generate a crosstab by year while still being able to calculate the percentage.

Let me know if you require any additional information to clarify.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources