DIVISION in sql - SQL Server Forums

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

SQL Server 2012 Forums

Transact-SQL (2012)

DIVISION in sql

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources