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.
Go to Top of Page

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
Go to Top of Page

arjunmt
Starting Member

2 Posts

Posted - 2005-02-25 : 06:32:30
thanks...
Go to Top of Page
   

- Advertisement -