SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


select clients with two billing currencies


select clients with two billing currencies

Author
Message
klanguedoc
klanguedoc
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
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
Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62466 Visits: 19102
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
My Blog: www.voiceofthedba.com
klanguedoc
klanguedoc
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 11
thanks for the advice, i'll give it a go
klanguedoc
klanguedoc
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
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
Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62466 Visits: 19102
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
My Blog: www.voiceofthedba.com
klanguedoc
klanguedoc
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
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
arun.sas
arun.sas
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1657 Visits: 3493
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
klanguedoc
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 11
Thanks Old Hand...this is fabulous

kevin
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search