Calculate difference between columns w. cross tab

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 2005 Forums

Transact-SQL (2005)

Calculate difference between columns w. cross tab

Author

Topic

Sphinx No. 4
Starting Member

4 Posts

Posted - 2009-08-08 : 10:33:15

Hi!

I am still fairly new to SQL even though I have been using simpler SELECT-statements to get data into Excel for som years now, but the time has now come to learn more about the SQL-language. I have managed to solve most of my problems, searching the web during long and dark nights...

Here is my question.
I want, in this specific case, to calculate the difference between two columns. That would not be so hard if the columns came from a table, yes, but I am using cross tab to create the columns in my script. How do I do that? What do I call them?

This is what I got now:

SELECT table1.client, sum(Case dim1 when '1980' then amount else 0 end) as 'Total 1980', sum(Case dim1 when '2440' then amount else 0 end)*-1 as 'Total 2440'
FROM test.dbo.table1 table1
WHERE (table1.client='2231') AND (table1.period>=200800) AND (table1.dim1 Between '1980' And '2440')
GROUP BY client --with rollup
--HAVING (Sum(table1.amount)<>0)
ORDER BY table1.client

The result should be something like, and the 4th column is my problem:

Client 1980 2440 Difference
2000 750.000 200.000 550.000

It can't be that hard I imagine, but I can't seem to find the solution when I search.

Thanks in advance!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-08-08 : 14:33:07

[code]
Select Client,[Total 1980],[Total 2440],ISNULL([Total 1980],0)-ISNULL([Total 2440],0) as Difference
from
(
SELECT table1.client, sum(Case dim1 when '1980' then amount else 0 end) as 'Total 1980', sum(Case dim1 when '2440' then amount else 0 end)*-1 as 'Total 2440'
FROM test.dbo.table1 table1
WHERE (table1.client='2231') AND (table1.period>=200800) AND (table1.dim1 Between '1980' And '2440')
GROUP BY client --with rollup
--HAVING (Sum(table1.amount)<>0)
ORDER BY table1.client)Z [/code]

Sphinx No. 4
Starting Member

4 Posts

Posted - 2009-08-09 : 06:39:20

Thanks a lot, that worked fine!

I can't, however, get the clients in rigth (asc) order.

Here is what I have right now:

Select Client,[Total 1980],[Total 2440],ISNULL([Total 1980],0)-ISNULL([Total 2440],0) as Difference
from
(
SELECT top 100 percent aaghsb1.client, sum(Case dim1 when '1980' then amount else 0 end) as 'Total 1980', sum(Case dim1 when '2440' then amount else 0 end)*-1 as 'Total 2440'
FROM agrprod.dbo.aaghsb1 aaghsb1
WHERE (aaghsb1.client>='2000') AND (aaghsb1.period>=200800) AND (aaghsb1.dim1 Between '1980' AND '2440')
GROUP BY aaghsb1.client
ORDER BY aaghsb1.client)z

I have tried to move GROUP and/or ORDER outside the SELECT-statement with no luck.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-09 : 06:44:28

Move the ORDER BY outside the derived table --> The column then is only client or z.client.
If client column is varchar and the value is always numeric then you can do ORDER BY convert(int,z.client)

No, you're never too old to Yak'n'Roll if you're too young to die.

Sphinx No. 4
Starting Member

4 Posts

Posted - 2009-08-09 : 13:56:36

quote:
Originally posted by webfred

Move the ORDER BY outside the derived table --> The column then is only client or z.client.

Thanks, that worked just fine!

My final problem here is that I want to include just those rows where the difference is negative. Something like:

SELECT Client,[Total 1980],[Total 2440],ISNULL([Total 1980],0)-ISNULL([Total 2440],0) as Difference
FROM
(
SELECT top 100 percent aaghsb1.client, sum(Case dim1 when '1980' then amount else 0 end) as 'Total 1980', sum(Case dim1 when '2440' then amount else 0 end)*-1 as 'Total 2440'
FROM agrprod.dbo.aaghsb1 aaghsb1
WHERE (aaghsb1.client>='2000') AND (aaghsb1.period>=200800) AND (aaghsb1.dim1 In ('1980','2440'))
GROUP BY aaghsb1.client)z
HAVING Difference <0
ORDER BY client

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-09 : 14:22:30

So you want to see only those rows with negative Difference?
Add a WHERE before the ORDER BY

WHERE ISNULL([Total 1980],0)-ISNULL([Total 2440],0) < 0

No, you're never too old to Yak'n'Roll if you're too young to die.

Sphinx No. 4
Starting Member

4 Posts

Posted - 2009-08-09 : 14:59:19

Yes, in this case anyway.

I tried something similar before with no succes, but this extra WHERE-clause worked just fine.

Thanks for all the help with this. I have learned very much this weekend.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-09 : 15:25:17

My part was just to do some cosmetics around...
The real helper was sodeep.
But anyway - you're welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources