Hi guys,

Out of 2 stores(1432,1404) I would like to output, the store each customer spent the most money at. Below is what I have so far. Right now the query produces the following results. The query can be seen below the results.

cardno subid   totalspend
84	1432	624.32
03	1404	1070.85
03	1404	50.00
40	1432	149.66
52	1432	117.59
36	1404	40.00
51	1404	120.00
51	1432	110.00
27	1404	369.60

SELECt distinct cardnumber,subsidiaryid,t.spend
from 
(
select distinct cardnumber,subsidiaryid,sum(DailyTransactionValue)spend
from SubsidiaryByCustomerByClassTransactionDetail
where TransactionDate >= '20090101' and TransactionDate < '20100101'
and subsidiaryid in (1404,1432)
group by SubsidiaryId,cardnumber
)t
group by cardnumber,t.spend,subsidiaryid
order by cardnumber

cardno subid   totalspend
84	1432	624.32
03	1404	1070.85
40	1432	149.66
52	1432	117.59
36	1404	40.00
51	1404	120.00
27	1404	369.60

select cardnumber,subsidiaryid,sum(DailyTransactionValue) as spend
from SubsidiaryByCustomerByClassTransactionDetail
where TransactionDate >= '20090101' and TransactionDate < '20100101'
and subsidiaryid in (1404,1432)
group by cardnumber,SubsidiaryId

cardno subid   totalspend
51     1404    120.00
51     1432    110.00

 
cardno subid   totalspend
51     1404    120.00

select cardnumber,subsidiaryid,DailyTransactionValue from 
(
select row_number() over(partition by cardnumber order by DailyTransactionValue desc) as seq
, *
from SubsidiaryByCustomerByClassTransactionDetail
where TransactionDate >= '20090101' and TransactionDate < '20100101'
and subsidiaryid in (1404,1432)
) t
where t.seq = 1

select a.cardnumber,a.subsidiaryid,b.DailyTransactionValue
from SubsidiaryByCustomerByClassTransactionDetail a
inner join (select cardnumber,max(DailyTransactionValue) as DailyTransactionValue from SubsidiaryByCustomerByClassTransactionDetail group by cardnumber) b on a.cardnumber = b.cardnumber and a.DailyTransactionValue = b.DailyTransactionValue
where a.TransactionDate >= '20090101' and a.TransactionDate < '20100101'
and a.subsidiaryid in (1404,1432)