Saturday, October 10, 2009

Get Customers with more than one address

Here's a simple SQL statement that I've used to help me resolve some of our issues be it customer, vendor or item master files:

Select custnmbr, Count(ADRSCODE) As AddressCount
From RM00102
Group By custnmbr
Having Count(ADRSCODE) > 1

or if you want detail use this

Select *
From RM00102
Inner Join (
Select CUSTNMBR, Count(ADRSCODE) As AddressCount
From RM00102
Group By CUSTNMBR
Having Count(ADRSCODE) > 1
) As A
On RM00102.CUSTNMBR = A.CUSTNMBR

1 comment:

Locus Academy said...

Locus Academy is an exclusive training centre for Microsoft Products. We have huge number of enquiries from Corporate and individuals on Microsoft Dynamics CRM 4.0 (Technical/Functional),Microsoft Dynamics Axapta 2009 (Technical/Functional) & Microsoft Business Intelligence (MSBI). Our class room (100% practical) training will make you industry experts and can challenge any implementation @ any level. Courses are designed to give students the best combination of skills, experience and training to gain employment with IT giants.
We are proud to deliver right-talents in IT industry [ http://academy.locusit.com ]
Our Features:
♦ Hands on Training
♦ Real-time industry experts from MNCs
♦ Case studies and real-world examples
♦ Customizable
♦ Lab exercises
♦ Mock Projects
♦ Interview Preparation
♦ Experts advice

Other Courses Training from Locus Academy
*Asp.Net *C# *VB.Net *SQL Server 2005 *VBA *MSBI(Microsoft Business Intelligence) *Share Point and all other Microsoft products.