hbars-alternative.sql

select
    Country
    , 'Real' as Currency
    , 3.13 as ExchangeRate
    , sum(Total*3.13) as LocalCurrencyTotal
from
    Customer c
inner join
    Invoice i
on
    i.CustomerId = c.CustomerId
where
   Country = 'Brazil'
   and Total > 5
group by
    Country

UNION ALL
select
    Country
    , 'Canadian Dollar' as Currency
    , 1.27 as ExchangeRate
    , sum(Total*1.27) as LocalCurrencyTotal
from
    Customer c
inner join
    Invoice i
on
    i.CustomerId = c.CustomerId
where
   Country = 'Canada'
   and Total > 1.5
group by
    Country

UNION ALL
select
    Country
    , 'Kourna' as Currency
    , 25.45 as ExchangeRate
    , sum(Total*25.45) as LocalCurrencyTotal
from
    Customer c
inner join
    Invoice i
on
    i.CustomerId = c.CustomerId
where
   Country = 'Czech Republic'
   and Total > 10
group by
    Country

UNION ALL
select
    Country
    , 'Euro' as Currency
    , 0.93 as ExchangeRate
    , sum(Total*0.93) as LocalCurrencyTotal
from
    Customer c
inner join
    Invoice i
on
    i.CustomerId = c.CustomerId
where
   Country = 'Austria'
   and Total > 1
group by
    Country

UNION ALL
select
    Country
    , 'Euro' as Currency
    , 0.93 as ExchangeRate
    , sum(Total*0.93) as LocalCurrencyTotal
from
    Customer c
inner join
    Invoice i
on
    i.CustomerId = c.CustomerId
where
   Country = 'Belgium'
   and Total > 2
group by
    Country

UNION ALL
select
    Country
    , 'Krone' as Currency
    , 6.95 as ExchangeRate
    , sum(Total*6.95) as LocalCurrencyTotal
from
    Customer c
inner join
    Invoice i
on
    i.CustomerId = c.CustomerId
where
   Country = 'Denmark'
   and Total > 3
group by
    Country

UNION ALL
select
    Country
    , 'US Dollar' as Currency
    , 1.0 as ExchangeRate
    , sum(Total*1.0) as LocalCurrencyTotal
from
    Customer c
inner join
    Invoice i
on
    i.CustomerId = c.CustomerId
where
   Country = 'USA'
   and Total > 1.2
group by
    Country

UNION ALL
select
    Country
    , 'Euro' as Currency
    , 0.93 as ExchangeRate
    , sum(Total*0.93) as LocalCurrencyTotal
from
    Customer c
inner join
    Invoice i
on
    i.CustomerId = c.CustomerId
where
   Country = 'France'
   and Total > 2.3
group by
    Country

UNION ALL
select
    Country
    , 'Euro' as Currency
    , 0.93 as ExchangeRate
    , sum(Total*0.93) as LocalCurrencyTotal
from
    Customer c
inner join
    Invoice i
on
    i.CustomerId = c.CustomerId
where
   Country = 'Germany'
   and Total > 4.1
group by
    Country