Sub select based on the value of the sub select

Transact-SQL
1

Hi there,
I have a table of customers and a table of orders. I will clearly have 1 or many orders per customer. I need my select to return 1 row per customer so I planned to sum from orders table rather than join.
However, the field I will sum is dependant on a field on the order, namely the currency, due to the structure of the orders table.
So, if Field X in orders table = field x in customers table. Its the correct customer which will be my sub select joiner. So from the orders table, if field currency = GBP then I need to sum(fieldA) else sum(fieldB) * (Another sub select to obtain exchange rate based on the currency field)

Any idea's how I do this?

I had the following select but get this error Not a single-group group function
(SELECT
CASE
WHEN (orders."currency" = 'GBP'')
THEN
round(COALESCE(SUM(orders."gbp"),0),2)

ELSE	      
	  ROUND(COALESCE(SUM(orders."amount"),0)  * 
	  (Select   exchange."rate"
	  From  "curr_exch" exchange 
	  WHERE exchange."currency" = orders."currency"
	  and exchange."date" = 
	  (SELECT MAX(exchange."date") as "Date" From "curr_exch" exchange Where exchange."currency" = customer."currency" and exchange."date" <= customer."orderDate"))
    
	/  
	  (Select   exchange."rate"
	  From  "curr_exch" exchange 
	  WHERE exchange."valuta" = ''GBP''
	  and exchange."date" = 
	  (SELECT MAX(exchange."date") as "Date" From "curr_exch" exchange  Where exchange."currency" = '"GBP' and exchange."date" <= customer."orderDate")),2)
        
 
END

FROM orders WHERE orders."ID" = Customer."OrderID" AND

) AS "IncomeGBP"

3

I can't follow the code. You're referencing a "customer" table but I don't see one in the query.

4

hi

i tried to work on this
i created sample data

when you want people to help you
you have to post readily consumable sample data
it becomes much much easier if you can make other people understand what you are saying ?
whether its diagrams simple one or two words or excel
Please dont mis understrand me or take this the wrong way
what i am talking about is another PHd subject by itself

anyhow

create sample data

drop table #exchange
drop table #Customer
drop table #orders
go

create table #exchange(rate decimal(5,2) , [date] date , currency varchar(4) )
create table #Customer(customerid int , orderid int , currency varchar(4),OrderDate date )
create table #orders(orderid int , customerid int , currency varchar(4),amount int)

insert into #exchange select 2.45 , '2010-01-09', 'GBP'
insert into #exchange select 5.09 , '2010-01-10', 'GBP'
insert into #exchange select 4.89 , '2010-01-11', 'GBP'

insert into #exchange select 20.3, '2010-01-02', 'GBP'
insert into #exchange select 11.2, '2010-01-13', 'GBP'
insert into #exchange select 15.6, '2010-01-14', 'GBP'

insert into #Orders select 1,1,'GBP',10000
insert into #Orders select 2,1,'GBP',20000
insert into #Orders select 3,1,'GBP',15000

insert into #Customer select 1, 1,'GBP','2010-01-10'
insert into #Customer select 1, 2,'GBP','2010-01-11'
insert into #Customer select 1, 3,'GBP','2010-01-12'

; with 
    cte_max as (select max(orderdate) maxordate  , customerid from #customer group by customerid )
,   cte_exc as (select ROW_NUMBER() over(partition by customerid order by date desc) as rn , * from #exchange a  , cte_max b  where a.[date] <= b.maxordate )
,   cte_amt as (select sum(b.amount) as tot_amt, a.customerid from #customer a join #orders b  on a.orderid = b.orderid and a.customerid = b.customerid group by a.customerid )
select 
     tot_amt/rate 
from 
    cte_amt a , cte_exc b 
where 
     b.rn = 1

image
image849×733 22.9 KB

5

Thanks for the effort you put in and assistance, this works great.

Regards your question, I wouldnt know how to post creatable sample data but thanks for the guidance.