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

Planning SQL Upgrade

So you finally decided to upgrade to SQL Server 2008 but it's been awhile since you looked at options for your Raid configurations. Here's a summary of what your options are:

Raid 0 - data is spread in different disk (disk stripping) but lacks redundancy
PROS - performs well on read and write
CONS - Failure of disk means data is lost
Raid 1 - known as disk mirroring no disk stripping. Data is hosted on a single drive then copied to another drive for fault tolerance. Recommended for Transaction Logs.
PROS - Redundant so failure of a drive will not stop database access
CONS - Cost since you'll need to double the size of your disk.

Raid 5 - uses disk stripping with parity. Data is spread but also stores parity information so it can be used in case of disk failure. Best for read-based not many write operations. Also avoid for transaction log because it's primarily sequential write.
PROS - Fault Tolerance at a low price.
CONS - Write Performance. Additional time is required to compute parity info before it's written to disk.

Raid 10 - cross between Raid 0 and 1. Data is striped evenly accross all drives then mirrored.
PROS - Write performance is far superior than Raid 5 and read performance is similar to Raid 5. Offers Fault Tolerance.
CONS - Cost since you'll need twice the number of disk like Raid 1.

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]'

Proud Uncle

I'm so proud of my niece, Guia del Prado, that I wanted to congratulate her in my blog for being featured in her schools website - University of Nevada, Reno : www.unr.edu .

She's currently the news editor for the campus magazine - Insight.

Convergence 2009 update

Just came back from Convergence 2009 - New Orleans and found this year quite a disappointment as I didn't see a lot of value to the seminars, GP ver 11 and the future of products around Dynamics GP.

Here are my updates this year:

1) GP ver 11 - The presenter in the kiosk didn't show a lot of functionality as she said they're still finalizing the list but I like the option of printing a form to Microsoft Word which many of us had been requesting.
2) FRx - No goods new here as Microsoft announced last year Management Reporter will replace it. I found that Management Reporter lacks some functionality including XBRL (I hope they've resolve this already), etc. FRx support end on December 2010.
3) Forecaster - The big question - PerformancePoint or Dynamics?
4) CRM - There were a few seminars showing the new integration between this product and Dynamics GP. One presenter said it can now compete with vendors like Scribe.

I'll post more later....

FRx Error - Period not found

It's a new year and you just closed 2008 in Great Plains. You run FRx for the first period of the year and use /BB and @PERDESC header. The report will run without error but you're getting this - Period not found in Company Calendar, as the column header.

So what do you do? Download Microsoft FRx 6.7 Service Pack 10. Here are the links to download the file:

For GP Partners download here.

For GP Customers download here.