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

SQL script to show last invoice detail of every customer

Copy and paste this to SQL Server Enterprise Manager:

select distinct a.custnmbr,a.LSTTRXDT,a.lsttrxam,b.sopnumbe
from rm00103 a
join sop30200 b
on a.custnmbr = b.custnmbr and
a.LSTTRXDT = b.docdate
/* show in ascending order */
order by custnmbr asc

SQL Wildcards

We implemented Multicurrency module and part of this implementation is changing the naming convention of our customers in Great Plains. We want to easily identify what kind of customer are they. Currently, our customer id ends in 001 so we decided to change it to whatever the contract currency is. So let's say ABC has a EURO contract with us then their customer id will then be ABCEUR instead of ABC001.

Our database is shared to other systems like our BI team to create relational DBs. A question came to my lap on the easiest way to segregate the customers based on their contract currency. We don't want to give them the Currency ID field as it's not consistent with other system. So I provided them with this Select statement:

Select * from RM00101
where custnmbr like '%[EUR]'

Select Statements Part I

A favorite sql script of mine is the 'Select Count(*)' statement. You can use it to get the number of items, customers or vendors in Great Plains by simply running:

Select Count(*) from IV00101 (item master)
Select Count(*) from RM00101 (customer master)
Select Count(*) from PM00200 (vendor master)

You can also use it to check documents in sales or purchasing and include a where clause.

Select Count(*) from SOP10100
where soptype = '2' (to check count for open sales orders)

Select Count(*) from SOP30200
where soptype = '3' and
sopnumbe LIKE 'ORD%' (to check for posted sales invoices)