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 |
|
glove
Starting Member
2 Posts |
 Posted - 2009-02-24 : 09:22:20
|
| I posted a thread a while back but didn’t get a response. I will try to be more clear in this postI have one table with a field called sales and one called closed_date. The dates range from 2008 to current.I have two Datepart functions in my select statement which is for the year and month. I then group by year and month. The result is Column1, column2, column 3Year Month Sales2008 Jan. 120,0002008 Feb. 120,000…2009 Jan. 124,000Now in a report using MS reporting services I have a matrix with year in a column and months as a row with sales as the data. Which looks like 2008 2009Jan. 120,000 140,000Feb. 120,000 132,000…. I want to be able to add another column which will represent the difference from 2008 to 2009: 10%, (5%).. ect.I have no idea how I can perform arithmetic math on the two years since they are from the same column and only grouped by using the datepart function.Please, Please help me with this. It is driving me crazy. I have messed around with temp tables, CTEs, derived tables and have had no luck.Your help is very appreciated! |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2009-02-24 : 09:46:05
|
| how about?select datea, max(fielda), min(fielda), max(fielda) - min(fielda)from mytablegroup by datea |
 |
|
|
glove
Starting Member
2 Posts |
Posted - 2009-02-24 : 09:52:30
|
| right but I need to subtract 2008 sales from 2009 sales and then divide by 2008 salesHow am I supposed to distinguish 2008 and 2009???Datepart(yy, close_date)?? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-24 : 17:09:33
|
| You can use Outer apply,Left outer join? Do you have PK key? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-25 : 10:19:58
|
| just use report expression for third column in matrix and give expressionas (ReportItems!2009textbox.value-ReportItems!2008textbox.value)/ReportItems!2008textbox.value |
|
|
|
|
|
|
|
|
How to perform arithmetic math on group by columns