Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

select clients with two billing currencies Expand / Collapse
Author
Message
Posted Wednesday, May 6, 2009 8:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 27, 2010 2:54 PM
Points: 6, Visits: 11
I trying to determine which clients have more than one default currency from a table of sales data. It seems like a simple query but for some reason I can't wrap my head around it. any help would be greatly appreciate it
Post #711139
Posted Wednesday, May 6, 2009 8:54 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 8:48 AM
Points: 33,089, Visits: 15,198
We want to see you do some work, so make an attempt.

As a hint, group by the clients, count the currencies.

And you'll need a HAVING clause.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #711171
Posted Wednesday, May 6, 2009 9:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 27, 2010 2:54 PM
Points: 6, Visits: 11
thanks for the advice, i'll give it a go
Post #711209
Posted Wednesday, May 6, 2009 9:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 27, 2010 2:54 PM
Points: 6, Visits: 11
it gives me the number of transactions for each currency not the customers that have two different currencies
0000104691 USD 41
0000104698 USD 450
0000104707 USD 4
0000104708 USD 266
0000104710 USD 35
0000104717 CAD 440
0000104724 CAD 3

I need to know if 0000104724 has orders in CAD and in USD or EUR as an example
thanks
Post #711213
Posted Wednesday, May 6, 2009 9:25 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 8:48 AM
Points: 33,089, Visits: 15,198
Show some code here, and we'll help you out. Seeing results doesn't help me figure out what you're doing wrong.








Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #711217
Posted Wednesday, May 6, 2009 12:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 27, 2010 2:54 PM
Points: 6, Visits: 11
select distinct custno, currency, COUNT(currency) from tblSales
group by custno, currency having currency = 'USD' or currency='CAD'
order by custno
Post #711386
Posted Thursday, May 7, 2009 3:08 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 19, 2012 10:25 PM
Points: 1,231, Visits: 3,483
klanguedoc (5/6/2009)
select distinct custno, currency, COUNT(currency) from tblSales
group by custno, currency having currency = 'USD' or currency='CAD'
order by custno


Hi,

from your codeing result for count of currency alwayes one,

--only the customer having more than one currency
select custno,COUNT(currency) from tblSales
where currency in('USD','CAD')
group by custno
having COUNT(currency)>1

--for all the records
select custno,COUNT(currency) from tblSales
where currency in('USD','CAD')
group by custno

ARUN SAS
Post #711843
Posted Thursday, May 7, 2009 6:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 27, 2010 2:54 PM
Points: 6, Visits: 11
Thanks Old Hand...this is fabulous

kevin
Post #711952
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse