SELECT c.OrganisationV6 AS Company,
BK.MAX_DATE [Last Booking Entry],
c.contactV6 AS [Main Contact],
CASE
WHEN c.stop_credit = 0 THEN 'Allow Credit'
WHEN c.stop_credit = 1 THEN 'STOP CREDIT - DO NOT TRADE'
WHEN c.stop_credit = 2 THEN 'STOP CREDIT - Refer to Accounts'
ELSE 'Unknown'
END AS [Allow/Stop Credit],
c.sales_tax_no [Sales Tax #],
c.hire_tax_exempt [Hire Tax Exempt],
insurance_type as [Insurance Type],
T1.tax_name as [Tax1],
T2.tax_name as [Tax2],
CASE c.Price_customer_pays
WHEN 0 then 'COST'
WHEN 1 then 'WHOLESALE'
WHEN 2 then 'TRADE'
WHEN 3 then 'RETAIL'
END as [Price Customer Pays],
c.credit_limit as [Credit Limit],
c.discount_rate as [Customer Discount Rate],
P.PaytermName [Account Type],
c.Address_l1V6 as [Street Address 1],
c.Address_l2V6 as [Street Address 2],
c.Address_l3V6 as [Street Address 3],
c.post_code as [Street Post Code],
c.StreetState as [Street State],
c.StreetCountry as [Street Country],
c.PostalAddress1 AS [Postal Address 1],
c.PostalAddress2 AS [Postal Address 2],
c.PostalAddress3 AS [Postal City/Town],
c.postalPostCode AS [Postal Code],
c.PostalState AS [Postal State/Province],
c.PostalCountry AS [Postal Country],
c.emailAddress as [Email],
c.webAddress as [Web],
CONCAT(ISNULL(LTRIM(RTRIM(c.Phone1CountryCode)), ''), ' ', ISNULL(LTRIM(RTRIM(c.Phone1AreaCode)), ''), ' ', ISNULL(LTRIM(RTRIM(c.Phone1Digits)), ''), ' ',ISNULL(LTRIM(RTRIM(c.Phone1Ext)), '') ) AS [Cust Phone 1],
CONCAT(ISNULL(LTRIM(RTRIM(c.Phone2CountryCode)), ''), ' ', ISNULL(LTRIM(RTRIM(c.Phone2AreaCode)), ''), ' ', ISNULL(LTRIM(RTRIM(c.Phone2Digits)), ''), ' ',ISNULL(LTRIM(RTRIM(c.Phone2Ext)), '') ) AS [Cust Phone 2],
Isnull(dbo.Getcustnotes(c.customer_code), '') AS [NOTES]
FROM tblcust c
LEFT OUTER JOIN tblPayTermNames P ON c.Account_type = P.TermNo
left outer join tblTax T1 on c.TaxAuthority1 = t1.tax_auth_no
left outer join tblTax T2 on c.TaxAuthority2 = t2.tax_auth_no
INNER JOIN (select SUBSTRING(booking_no,1,6) as CUST,MAX(EntryDate) as MAX_DATE from tblbookings group by substring(booking_no,1,6)) BK
ON c.Customer_code = BK.CUST
WHERE Isnull(c.customer_code, ' ') <> ''
AND c.disabledcust = 'N'
ORDER BY c.organisationv6