January 22, 2007 at 7:57 am
Hi,
After I failed to get my previous query sorted.. I'm starting again with a new query.
Ok,.. I have two tbls.. one contains the first "district" part of a postcode,.. the other tbl contains full postcodes.
I watn to see how many postcodes exist for each district in the UK..so I do a JOIn..
however to format the full postcode correctly, I am doing a CASE statement on the JOIN statement... I don't think this is the most efficient way of doing things.. how else could this be written?
SELECT ISNULL(COUNT(dbo.Customers_Trade.PostCode), 0) AS PCCount, P.Postcode, P.Town, P.County, P.Area, P.Country, P.Master_Account
FROM dbo.UkPostcodes P LEFT OUTER JOIN
dbo.Customers_Trade ON P.Postcode =
CASE LEN(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''))
WHEN 5 THEN LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 2)
WHEN 6 THEN LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 3)
ELSE LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 4)
END
GROUP BY P.Postcode, P.Town, P.County, P.Area, P.Country, P.Master_Account
January 22, 2007 at 9:28 am
Assumming UkPostcodes.Postcode contains the postcode district and Customers_Trade.Postcode
contains the full postcode, how about:
SELECT ISNULL(D.PCCount, 0) AS PCCount
,P.* -- or whatever columns you want
FROM dbo.UkPostcodes P
LEFT JOIN (
SELECT P1.Postcode
,COUNT(*) AS PCCount
FROM dbo.UkPostcodes P1
JOIN dbo.Customers_Trade T1
ON P1.Postcode = LEFT(T1.Postcode, CHARINDEX(' ', T1.Postcode) - 1)
GROUP BY P1.Postcode ) D ON P.Postcode = D.Postcode
January 22, 2007 at 9:30 am
Just to explain to those who aren't familiar with UK postcodes...
The first part of the postcode can have 2, 3 or 4 characters; the second part always has 3: a single digit followed by two letters. So a postcode in the Manchester area might be M1 4DB or M12 4DB; one in Leeds might be LS1 4DB or LS12 4DB. Some districts in London also have an extra letter in the first part, so a postcode in the south-west of the city might be SW1A 4DB or SW2 4DB or SW12 4DB.
Something that would make your query look neater would be the following. I don't know whether it would improve the performance: you'd have to test that. Here it is:
SELECT ISNULL(COUNT(dbo.Customers_Trade.PostCode), 0) AS PCCount, P.Postcode, P.Town, P.County, P.Area, P.Country, P.Master_Account
FROM dbo.UkPostcodes P LEFT OUTER JOIN dbo.Customers_Trade ct
ON P.Postcode = LEFT(ct.PostCode, LEN(REPLACE(ct.PostCode, ' ', '')) - 3)
GROUP BY P.Postcode, P.Town, P.County, P.Area, P.Country, P.Master_Account
If you are able to change the table structure, consider adding a computed column to Customers_Trade that strips off the second part of the postcode. Then you can join on that.
John
January 22, 2007 at 4:44 pm
Hi John.
I am able to alter the tbl structure as required.
The query I posted at the beginning is actually only have of it.. I also need to have a WHERE clause which works out the last time a customer ordered to see if they should be included in the postcode count.. therefore I think anything I can do to help the query might be an option..
So I'm gonna have a play with a new column in the Customers_Trade tbl and see how that affects things.
January 23, 2007 at 6:13 am
John thanks for taking the time to explain how UK postal codes work;
I've only being only exposed to US postal codes, which are all numeric but can have preceeding zeros, so it's good to see how to tackle other postal issues.
Lowell
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply