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 |
maunishq
Yak Posting Veteran
71 Posts |
Posted - 2014-01-23 : 23:24:51
|
Hi, I am using MS SQL Server 2012. I want to use this query in SSRS. I have the following table: Year Month AccNo Balance 2011 2 45 900 2011 3 45 700 2011 4 45 800 2011 1 50 20 2011 2 50 15 2011 3 50 40
I want to the following Division: Balance of AccNo 45 / Balance of AccNo50 Where AccNo45.Year = AccNo50.Year AND AccNo45.Month = AccNo50.Month
The Answer should be something like this: Year Month Ratio 2011 2 900/15 (=60) 2011 3 800/40 (=20)
Thanks. |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-24 : 01:02:39
|
[code] SELECT [Year],[Month], MAX(CASE WHEN AccNo = 45 THEN Balance END)/MAX(CASE WHEN AccNo = 50 THEN Balance END) FROM Table GROUP BY [Year],[Month] [/code]
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs |
 |
|
maunishq
Yak Posting Veteran
71 Posts |
Posted - 2014-01-26 : 13:18:06
|
Thanks Visakh. I'll check if it retrieves me the correct result. Good Day. |
 |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-27 : 07:38:25
|
welcome let me know how you got on!
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs |
 |
|
maunishq
Yak Posting Veteran
71 Posts |
Posted - 2014-01-27 : 10:41:01
|
Hi, Thanks it worked. I am learning so could you please tell me what's the purpose behind using MAX in this query? I did it using join. It's pretty nice learning different methods for same result. |
 |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-27 : 14:04:32
|
quote: Originally posted by maunishq
Hi, Thanks it worked. I am learning so could you please tell me what's the purpose behind using MAX in this query? I did it using join. It's pretty nice learning different methods for same result.
Effectively you're aggregating when you merge multiple rows and bring row values onto different columns (crosstab) what i did was to crosstab out values forAccNo 45 and 50 and then applied your formula over it. for crosstabbing purpose we need group by on the common fields
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs |
 |
|
|
|
|