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.
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-0560162040003 -10.1660162040049 -18.26 -19.3660162040069 -0.3160162040156 -2.660162040237 -0.0760162040396 -0.0160162040424 -0.2260162040464 -0.460162040474 -0.0860162040499 -24.6760162040507 -64.37 -67.24The SQL statement i used to generate the report:TRANSFORM Avg(Negative_bal.balace) AS AvgOfbalaceSELECT Negative_bal.msisdnFROM Negative_balWHERE (((Negative_bal.date)="10-FEB-05" Or (Negative_bal.date)="11-FEB-05"))GROUP BY Negative_bal.msisdnPIVOT 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?thanksarjun ps the source table is as follows:- date msisdn balace10-Feb-05 60162040003 -10.1611-Feb-05 60162040049 -19.3612-Feb-05 60162040049 -10.1110-Feb-05 60162040049 -18.2611-Feb-05 60162040069 -0.3111-Feb-05 60162040156 -2.611-Feb-05 60162040237 -0.0712-Feb-05 60162040362 -0.1212-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... |
 |
|
|
|
|
|
|