select clients with two billing currencies

Question

Post reply

select clients with two billing currencies

klanguedoc

SSC Enthusiast

Points: 112
More actions
May 6, 2009 at 8:33 am

#202968

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 1

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.

klanguedoc SSC Enthusiast Points: 112 More actions · Answer 2

klanguedoc

SSC Enthusiast

Points: 112

May 6, 2009 at 9:19 am

#989647

thanks for the advice, i'll give it a go

klanguedoc SSC Enthusiast Points: 112 More actions · Answer 3

it gives me the number of transactions for each currency not the customers that have two different currencies

0000104691USD41

0000104698USD450

0000104707USD4

0000104708USD266

0000104710USD35

0000104717CAD440

0000104724CAD3

I need to know if 0000104724 has orders in CAD and in USD or EUR as an example

thanks

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 4

Show some code here, and we'll help you out. Seeing results doesn't help me figure out what you're doing wrong.

klanguedoc SSC Enthusiast Points: 112 More actions · Answer 5

select distinct custno, currency, COUNT(currency) from tblSales

group by custno, currency having currency = 'USD' or currency='CAD'

order by custno

arun.sas SSChampion Points: 11831 More actions · Answer 6

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

klanguedoc SSC Enthusiast Points: 112 More actions · Answer 7

klanguedoc

SSC Enthusiast

Points: 112

May 7, 2009 at 6:24 am

#990277

Thanks Old Hand...this is fabulous

kevin