add grouping in report using lookup table 1

[lorca]

I have the code below which works fine and outputs in report format using sql reporting.

I also have a lookup table of postcodes (CTS_POSTCODES) which has a column Postcode with hunderds of rows such as
M1 2AL
M1 2BZ
M12 2RT

etc etc

what i'd like to do is group the report by postcode (corpostcode column from the report) that match to an entry in the CTS_POSTCODES. and have those that don't find a match group those also.

any ideas ?
thanks
chris

                          SELECT     a.customerid, LEFT(c.titledesc, 8) AS Title, LEFT(a.firstname, 16) AS Firstname, LEFT(a.surname, 20) AS Surname, CONVERT(char(15), 
                                                  b.sdate, 102) AS Start_date, CASE LEFT(b.coraddress1 + ',' + b.coraddress2 + ',' + b.coraddress3 + ',' + b.coraddress4 + ',' + corpostcode,
                                                 60) WHEN ',,,,' THEN ' ' ELSE LEFT(b.coraddress1 + ',' + b.coraddress2 + ',' + b.coraddress3 + ',' + b.coraddress4 + ',' + corpostcode, 60) 
END AS Manual_Address, corpostcode
FROM         cts_customers a, cts_cust_addr b, cts_cust_titles c
WHERE     a.customerid = b.customer AND a.CREATING_AUTHORITY = 'CH' AND a.title = c.titleid AND b.edate IS NULL AND b.UPRN IS NULL
ORDER BY start_date

 

[philhege]

What table does the corpostcode column come from?

My first stab, without knowing the relationship (if any) between corpostcode and the lookup table:

SELECT a.customerid, LEFT(c.titledesc, 8) AS Title, LEFT(a.firstname, 16) AS Firstname, LEFT(a.surname, 20) AS Surname, CONVERT(char(15),
b.sdate, 102) AS Start_date, CASE LEFT(b.coraddress1 + ',' + b.coraddress2 + ',' + b.coraddress3 + ',' + b.coraddress4 + ',' + corpostcode,
60) WHEN ',,,,' THEN ' ' ELSE LEFT(b.coraddress1 + ',' + b.coraddress2 + ',' + b.coraddress3 + ',' + b.coraddress4 + ',' + corpostcode, 60)
END AS Manual_Address, corpostcode, isnull(d.postcode,'1 - NOT FOUND') as joinpostcode
FROM cts_customers a
JOIN cts_cust_addr b on b.customer = a.customerid
JOIN cts_cust_titles c on c.titleid = a.title
LEFT OUTER JOIN cts_postcodes d on d.postcode = corpostcode
WHERE a.CREATING_AUTHORITY = 'CH' AND b.edate IS NULL AND b.UPRN IS NULL
GROUP BY d.postcode, --and the rest of the columns
ORDER BY start_date

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Eschewing obfuscation diurnally.