?
Solved

SQL query question

Posted on 2014-08-20
10
Medium Priority
?
123 Views
Last Modified: 2014-08-24
I need to write a SQL query on SQL Server 2008 to make a summary report, which consists of [Company, Type, Amount].  
but there are multiple columns for amount, and depending on Company and Type, I need to select different columns to get the amount.

For a simplified example, my source data looks like this:

Data(Bank, TranType, Col1, Col2, Col3)

'ANZ', 'A', 1000, 1005, 980
'ANZ', 'B', 500, 502, 496
'ANZ', 'C', 1000, 1000, 1000
'HSBC', 'A', 1000, 1005, 980
'HSBC', 'B', 500, 502, 496
'CITI', 'C', 1000, 1000, 1000


And I have a table like this for rules:

Rule(Bank, TranType, Field)

'ANZ', 'A', 'Col1'
'ANZ', 'B', 'Col2'
'ANZ', 'C', 'Col3'
'HSBC', 'A', 'Col2'
'HSBC', 'B', 'Col3'
'CITI', 'A', 'Col1'

Any simple query to solve this ? Experts, your help is really appreciated...
0
Comment
Question by:Takamasa
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 12

Expert Comment

by:Habib Pourfard
ID: 40271999
You can use Case When like the following code:

SELECT Bank, TranType,
SUM (
	CASE WHEN Bank = 'ANZ' THEN Col1 
	WHEN Bank = 'BNZ' OR Bank = 'HSBC' Then Col2
	ELSE 0 END
	)
FROM data
GROUP BY Bank, TranType

Open in new window

0
 

Author Comment

by:Takamasa
ID: 40272025
Hi Habib,
Thank you for your suggestion.

The rule table actually is a very long list and it can change quite often, so I do not want to hard-code the rule...
0
 
LVL 12

Expert Comment

by:Habib Pourfard
ID: 40272223
You could create a View and use it as a source for different reports, I don't have any other idea.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 32

Expert Comment

by:awking00
ID: 40272409
You show trantype of 'C' for bank CITI in data and trantype of 'A' for bank CITI in rule. Is that a typo? Also, can you post what your desired output should be given this sample data?
0
 

Author Comment

by:Takamasa
ID: 40275221
Hi awking00

Thank you for your reply.
If the combinaton of Bank-TranType do not match, then the report will not pick up.
The output would look like this:

ANZ, A, 1000
ANZ, B, 502
ANZ, C, 1000
HSBC, A, 1005
HSBC, B, 496
0
 

Author Comment

by:Takamasa
ID: 40275256
Hi Habib,

Thank you for your suggestion.

Below query returns the output I am looking for, but in my real case the CASE statement would get very long (40+ when clauses). I was hoping there might be a smarter way than that but maybe some hard-coding cannot be avoided...

select 
	a.bank, 
	a.trantype, 
	case 
		when b.colname = 'col1' then a.col1
		when b.colname = 'col2' then a.col2
		when b.colname = 'col3' then a.col3
	end as amount
from 
	data a
	inner join 
	rule b
on 
	a.bank = b.bank 
	and a.trantype = b.trantype

Open in new window

0
 
LVL 15

Accepted Solution

by:
Vikas Garg earned 2000 total points
ID: 40275617
Hi,

You can try the following code which will meet your requirements

SELECT TB.Bank,TB.TranType,TB.Banks VALUE FROM 
(
select *
from Bank a
unpivot
(
  Banks
  for BankRulecol in ([Col1],[Col2],[Col3])
) unpiv
) TB
INNER JOIN BankRule B ON TB.Bank = B.Bank AND TB.TranType = B.TranType AND TB.BankRulecol = LTRIM(B.Field)

Open in new window

0
 
LVL 32

Expert Comment

by:awking00
ID: 40276131
What dbms (version and release) are you using (e.g. Oracle 11gR2)?
0
 

Author Closing Comment

by:Takamasa
ID: 40282314
Hello Vikas

This is great. Thank you for your help !
0
 

Author Comment

by:Takamasa
ID: 40282315
Hi awking00

I am using SQL Server 2008 R2.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

839 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