Mathematical Expressions from Crosstab Query

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

Other Forums

MS Access

Mathematical Expressions from Crosstab Query

Author

Topic

arjunmt
Starting Member

2 Posts

Posted - 2005-02-23 : 03:37:33

Hi All,

Need some assistance here, I am using a crosstab query to generate this report ->

msisdn 10-FEB-05 11-FEB-05
60162040003 -10.16
60162040049 -18.26 -19.36
60162040069 -0.31
60162040156 -2.6
60162040237 -0.07
60162040396 -0.01
60162040424 -0.22
60162040464 -0.4
60162040474 -0.08
60162040499 -24.67
60162040507 -64.37 -67.24

The SQL statement i used to generate the report:

TRANSFORM Avg(Negative_bal.balace) AS AvgOfbalace
SELECT Negative_bal.msisdn
FROM Negative_bal
WHERE (((Negative_bal.date)="10-FEB-05" Or (Negative_bal.date)="11-FEB-05"))
GROUP BY Negative_bal.msisdn
PIVOT Negative_bal.date;

how can i create a 4th column? where the column would show the difference between PIVOT column "2" and "3" (ie 10-FEB-05, 11-FEB-05), i can use the sum, average etc etc but somehow all my attempts to use "-" (minus) have failed,

#NAME?

thanks

arjun

ps the source table is as follows:-
date msisdn balace
10-Feb-05 60162040003 -10.16
11-Feb-05 60162040049 -19.36
12-Feb-05 60162040049 -10.11
10-Feb-05 60162040049 -18.26
11-Feb-05 60162040069 -0.31
11-Feb-05 60162040156 -2.6
11-Feb-05 60162040237 -0.07
12-Feb-05 60162040362 -0.12
12-Feb-05 60162040396 -0.16

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-02-23 : 08:16:15

It can't be done in the same query. You'd have to create a 2nd query based on this crosstab and do the calculation there. If you're putting this onto a report, you can use the original crosstab and do the calculation in a report control.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-02-23 : 08:39:02

What is tricky is that the column name changes depending on what data you are displaying. if the report always compares two days, then you should perhaps name the columns "Day1" and "Day2" instead of using the actual dates:

PIVOT IIF(Negative_bal.Date = #2/10/20005#,"Day1","Day2")

On your report, you can display whatever you like for the heading, but for the query itself, it is much easier if your column names are consistent. (in fact, for a report, it is required, since you must bind the column names to the report's controls unless you want to write VB to dynamically change this at run-time)

- Jeff

arjunmt
Starting Member

2 Posts

Posted - 2005-02-25 : 06:32:30

thanks...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources