?
Solved

Dividing Rows using conditions

Posted on 2014-01-23
6
Medium Priority
?
398 Views
Last Modified: 2014-01-24
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.
0
Comment
Question by:fireblues
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 25

Accepted Solution

by:
chaau earned 1400 total points
ID: 39805591
You need to self-join the same table:
SELECT a.Year, a.Month,  a.Balance/b.Balance AS Ratio
FROM AccountBalance a INNER JOIN AccountBalance b
ON a.Year = b.Year AND a.Month = b.Month
AND a.AccNo = 45 and b.AccNo = 50

Open in new window

0
 
LVL 13

Expert Comment

by:Koen Van Wielink
ID: 39805594
ARe 45 and 50 the only 2 accounts you're interested in? I assume this is only a small set of the total data? If there are more accounts, how do you identify which account should be divided by which? If you want to standardize this in a query you need to know why you need to divide the 45 balance by the 50 balance and not the other way around.
0
 

Author Comment

by:fireblues
ID: 39805605
@Chaau - I'll check the result and will let you know.
@Chaau and @Kvwielink: Thanks for the comments.
Yes, at the moment I am interested in division of only two values. Actually AccNo 50 is Quantity and AccNo45 is Sales. So I want to know the Ratio or say price.
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 25

Expert Comment

by:chaau
ID: 39805692
Just wanted to add: add " and b.Balance > 0" at the end of my statement to protect against a potential division by zero situation.
0
 
LVL 32

Expert Comment

by:awking00
ID: 39806877
>>The Answer should be something like this:
Year     Month     Ratio
2011     2            900/15 (=60)
2011     3            800/40 (=20)<<
Why is month 3 800/40 and not 700/40?
0
 

Author Comment

by:fireblues
ID: 39807259
Yup, it would be 700/40 instead of 800/40.
Thanks Everyone :)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question