Solved

Microsoft SQL query

Posted on 2016-11-02
7
43 Views
Last Modified: 2016-11-07
Hello, I am have trouble with this query,

Account number, balance, sum of transaction amounts, and balance - transaction sum for accounts in the New York branch that have at least ten transactions, order by account number.

Customer = {customerID, firstName, lastName, income, birthDate }
Account = {accNumber, type, balance, branchNumber(FK-Branch)}
Owns = {customerID(FK-Customer), accNumber(FK-Account)}
Transactions = {transNumber, accNumber(FK-Account), amount}
Employee = {sin, firstName, lastName, salary, branchNumber(FK-Branch)}
Branch = {branchNumber, branchName, managerSIN(FK-Employee), budget}

I am confused on how to check if they have at least 10 transactions. I have done an inner  join Account, Branch and Transactions but am unsure how to deal with the number of transactions done per account.
0
Comment
Question by:Bob Tian
  • 5
  • 2
7 Comments
 
LVL 25

Expert Comment

by:Pawan Kumar
ID: 41871385
Do you have sample data and the expected output ?
1
 
LVL 25

Expert Comment

by:Pawan Kumar
ID: 41871396
Try.. this

SELECT    
      a.accNumber          [Account]
	, a.balance            [balance]       
	, SUM(T.TransactionAmount) [Sum of Transactions]
	, a.balance - SUM(CASE WHEN branchNumber = 'New York' THEN T.TransactionAmount ELSE 0 END) Extracol
FROM Account a
INNER JOIN Branch b ON a.branchNumber = b.branchNumber
INNER JOIN Transactions T ON T.accNumber = a.accNumber
GROUP BY
      b.branchName
    , a.type
    , a.accNumber    
HAVING COUNT(T.transNumber) > 10
ORDER BY a.accNumber

Open in new window

1
 

Author Comment

by:Bob Tian
ID: 41873147
I think its b.branchName not b.branchNumber = 'New York'

Also the output would be something like:
AccNum  balance         Sum of Transactions     balance-Sum of Transactions
1               118231.13      12222                               106009.13
2               100808.03      2333                               98475.03

The problem is, I forgot to mention, is that the {transNumber} in Transaction does NOT indicate the amount of transactions done, it only represents a transaction number, combined with account number it uniquely identify a transaction. I am unsure how to find out how many transactions is done per account
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 25

Expert Comment

by:Pawan Kumar
ID: 41873269
Yes it is branchName <<Typo>> :) Let me rewrite it for you ! Wait for sometime.
1
 
LVL 25

Accepted Solution

by:
Pawan Kumar earned 500 total points
ID: 41873346
Updated...

This is how we can count transactions for each account.
, COUNT(*) OVER (PARTITION BY a.accNumber) TransactionPerAccount

SELECT    
      a.accNumber          [Account]
	, a.balance            [balance]       
	, SUM(T.TransactionAmount) [Sum of Transactions]
	, a.balance - SUM(CASE WHEN branchName = 'New York' THEN T.TransactionAmount ELSE 0 END) Extracol
	, COUNT(*) OVER (PARTITION BY a.accNumber) TransactionPerAccount
FROM Account a
INNER JOIN Branch b ON a.branchNumber = b.branchNumber
INNER JOIN Transactions T ON T.accNumber = a.accNumber
GROUP BY
      b.branchName
    , a.type
    , a.accNumber    
HAVING COUNT(*) > 10
ORDER BY a.accNumber

Open in new window


You want only those records where each account has more than 10 transaction ? If Yes then use below -

SELECT * FROM 
(
	SELECT    
		  a.accNumber          [Account]
		, a.balance            [balance]       
		, SUM(T.TransactionAmount) [Sum of Transactions]
		, a.balance - SUM(CASE WHEN branchName = 'New York' THEN T.TransactionAmount ELSE 0 END) Extracol
		, COUNT(*) OVER (PARTITION BY a.accNumber) TransactionPerAccount
	FROM Account a
	INNER JOIN Branch b ON a.branchNumber = b.branchNumber
	INNER JOIN Transactions T ON T.accNumber = a.accNumber
	GROUP BY
		  b.branchName
		, a.type
		, a.accNumber  
)x
WHERE x.TransactionPerAccount > 10 	  
ORDER BY x.Account

--

Open in new window



Hope it helps !!
1
 
LVL 25

Expert Comment

by:Pawan Kumar
ID: 41876682
Hi Bob,
Is this done?

Regards,
Pawan
0
 

Author Closing Comment

by:Bob Tian
ID: 41877539
thanks
0

Featured Post

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

863 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now