Takamasa
asked on
SQL query question
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...
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...
ASKER
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...
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...
You could create a View and use it as a source for different reports, I don't have any other idea.
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?
ASKER
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
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
ASKER
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...
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What dbms (version and release) are you using (e.g. Oracle 11gR2)?
ASKER
Hello Vikas
This is great. Thank you for your help !
This is great. Thank you for your help !
ASKER
Hi awking00
I am using SQL Server 2008 R2.
I am using SQL Server 2008 R2.
Open in new window