Issue:
We are seeing some customers show up more than once in the search.
Resolution:
This will occur when the customer has more than one active system on their account.
In this example the customer 8107 has three different systems on their account. Since the search is also displaying the system and site detail the search returns one line for each active system.
Below is the query that is used in the search.
SELECT TOP 100 'ID' = cust.Customer_Id , 'Customer #' = cust.Customer_Number
, 'Name' = cust.Customer_Name, 'Additional Name' = cust.Customer_Name_2 , 'System ID' = sySys.Customer_System_Id
, 'System Account' = sySys.Alarm_Account , 'System Description' = sys.Description , 'Site ID' = sSite.Customer_Site_Id
, 'Site #' = sSite.Site_Number , 'Site Name' = sSite.Business_Name , 'Site Address' = sSite.Address_1, 'Site Address 2' = sSite.Address_2
, 'Site City' = sSite.GE1_Description , 'Branch' = cbran.Branch_Code , 'Bill Address' = cbill.Address_1, 'Bill Address 2' = cbill.Address_2
, 'City' = cbill.GE1_Description , 'State' = cbill.GE2_Description, 'Telephone' = cbill.Phone_1 , 'Status' = cstat.Description
, 'Type' = ctype.Type_Code
FROM AR_Customer cust
INNER JOIN AR_Branch cbran ON cbran.Branch_Id = cust.Branch_Id
INNER JOIN AR_Customer_Bill cbill ON cbill.Customer_Id = cust.Customer_Id AND cbill.Inactive <> 'Y'
INNER JOIN SS_Customer_Status cstat ON cstat.Customer_Status_Id = cust.Customer_Status_Id
INNER JOIN AR_Type_Of_Customer ctype ON ctype.Type_Id = cust.Customer_Type_Id
INNER JOIN AR_Customer_Site sSite ON sSite.Customer_Id = cust.Customer_Id
INNER JOIN AR_Customer_System sySys ON sySys.Customer_Site_Id = sSite.Customer_Site_Id
INNER JOIN SY_System sys ON sys.System_Id = sySys.System_Id
WHERE (cust.Customer_Name <> 'N/A' AND cbill.Is_Primary = 'Y')
AND (cust.Customer_Name LIKE '%8107%'
OR cust.Customer_Name_2 LIKE '%8107%'
OR cust.Customer_Number LIKE '%8107%'
OR cust.Old_Customer_Number LIKE '%8107%' )
AND (sSite.Inactive <> 'Y')
AND (sySys.Inactive <> 'Y')
AND (cstat.Customer_Status_Id != 3)
AND (cust.Customer_Id > 0)
ORDER BY 'ID'