SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Highest spend out of all locations
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

velvettiger
Posting Yak Master

115 Posts

Posted - 12/29/2009 :  15:53:14  Show Profile  Reply with Quote
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


vijayisonly
Flowing Fount of Yak Knowledge

USA
1836 Posts

Posted - 12/29/2009 :  16:05:37  Show Profile  Reply with Quote
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).
Go to Top of Page

divya.ce
Starting Member

India
16 Posts

Posted - 12/30/2009 :  05:10:05  Show Profile  Reply with Quote
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
Go to Top of Page

velvettiger
Posting Yak Master

115 Posts

Posted - 12/30/2009 :  09:18:12  Show Profile  Reply with Quote
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.
Go to Top of Page

vijayisonly
Flowing Fount of Yak Knowledge

USA
1836 Posts

Posted - 12/30/2009 :  09:30:56  Show Profile  Reply with Quote
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
Go to Top of Page

velvettiger
Posting Yak Master

115 Posts

Posted - 12/30/2009 :  10:06:53  Show Profile  Reply with Quote
Hi,

I am using sql server 2000. So row_number etc won't be recognized.
Go to Top of Page

vijayisonly
Flowing Fount of Yak Knowledge

USA
1836 Posts

Posted - 12/30/2009 :  10:27:26  Show Profile  Reply with Quote
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)


Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000