Select puzzle - 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

General SQL Server Forums

New to SQL Server Programming

Select puzzle

Author

Topic

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2008-07-07 : 05:49:42

Dear Experts,

I got another puzzle today.

I have a table named Invoice with the following fields:
InvoiceID GUID
Currency varchar(4) e.g. USD, EUR
AmountForeign money (all non-usd amount)
AmountInUSD money (usd equalivant amount if it is in foreign currency)
Taxable bool

Now I want to select the currency, total, total amount that is taxable, total amount is not-taxable in one table. How do I do that?

i.e. currency Total Non-Taxable Taxable
USD 200,000 10,000 190,000
EUR 50,000 4,000 46,000
CNY 400,000 0 400,000

Your guidance is very much appreciated.

Regards,

Eugene

P.S. Please note that some foreign currency columns are null if they are paid in USD. Also, note that if a foreign currency is not null, there is an equalivant amount in USD in the amountInUSD table. For the USD column in the report, I am only interested in the sum of the USD where foreignamount is null, or where the currency = USD.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-07-07 : 06:06:04

So, how far have you got? Look up CASE and SUM in BOL (Books Online [SQLs help file]).

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2008-07-07 : 06:10:22

This is where I got to: But I am struck when I consider the AmountInUSD

Select P.Currency, Sum(P.AmountForeign) as ForeignAmount,
(Select Sum(P1.AmountForeign) from Invoices P1 where P1.Currency = P.Currency And P1.TaxDeductable = 'true') as TaxFree,
(Select Sum(P1.AmountForeign) from Invoices P1 where P1.Currency = P.Currency And P1.TaxDeductable = 'false') as Taxable
from Invoice P
group by P.Currency

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-07-07 : 06:12:39

[code]
Select
P.Currency,
Sum(P.AmountForeign) as ForeignAmount,
Sum(case when AmountForeign is null and TaxDeductable = 'true' then AmountInUSD
when AmountForeign is not null and TaxDeductable = 'true' then AmountForeign else 0 end) as TaxFree,
Sum(case when AmountForeign is null and TaxDeductable = 'false' then AmountInUSD
when AmountForeign is not null and TaxDeductable = 'false' then AmountForeign else 0 end) as Taxable
from Invoice P
group by P.Currency
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-07 : 06:19:32

use group by and case. something like

SELECT Currency,
SUM(AmountInUSD) AS Total,
SUM(CASE WHEN Taxable=0 THEN AmountInUSD ELSE 0 END) AS NonTaxable,
SUM(CASE WHEN Taxable=1 THEN AmountInUSD ELSE 0 END) AS Taxable
GROUP BY Currency

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-07-07 : 06:25:36

quote:

Originally posted by visakh16

use group by and case. something like

SELECT Currency,
SUM(AmountInUSD) AS Total,
SUM(CASE WHEN Taxable=0 THEN AmountInUSD ELSE 0 END) AS NonTaxable,
SUM(CASE WHEN Taxable=1 THEN AmountInUSD ELSE 0 END) AS Taxable
from Invoice
GROUP BY Currency

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2008-07-07 : 06:48:15

Thank you. Rick and visakh and Athalye.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-07-07 : 06:54:22

No worries, mine will only take the values in their own currency. If you want all the values in USD regardless of the currency, use visakh and harsh_athalye's query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources