Highest spend out of all locations

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

Highest spend out of all locations

Author

Topic

velvettiger
Posting Yak Master

115 Posts

Posted - 2009-12-29 : 15:53:14

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


[/code]

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-29 : 16:05:37

Just this should do ...no?

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

Also why did you leave out the other "03 1404" in your result...(without summing up its value).

divya.ce
Starting Member

16 Posts

Posted - 2009-12-30 : 05:10:05

Just do max instead of sum in the inner loop ..

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

velvettiger
Posting Yak Master

115 Posts

Posted - 2009-12-30 : 09:18:12

Hi everyone,

Thanks for you responses but this doesn't give me the desired results. In the example i gave in my initial explanation showed that some cardnumbers appear twice but the subids are different e.g


cardno subid   totalspend
51     1404    120.00
51     1432    110.00

In this example I would like only the row

 
cardno subid   totalspend
51     1404    120.00

to be outputted as this person has the highest spend at this store.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-30 : 09:30:56

Try this..

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

velvettiger
Posting Yak Master

115 Posts

Posted - 2009-12-30 : 10:06:53

Hi,

I am using sql server 2000. So row_number etc won't be recognized.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-30 : 10:27:26

Well..you should have mentioned it...Try this..

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)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources