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
So basically the end result should look like this.Any ideas on how to accomplish this?
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