0

quite new to SQL so please bear with me I'll explain as best I can.

I have property data, containing a huge list of properties, full UK postcode and all sorts of other data.

I am simply doing a number of aggregations, for example, how many properties are Leasehold, and how many have 3 bedrooms etc. This is easy enough, but I need to be able to specify properties based in London as per the first letters of the postcode (I have a list I am asked to reference against for example 'NW', 'EN', 'S' etc).

As I cannot create a new table in the database to specifically reference from (I do not have clearance), so I create a Temp Table as per below. What I am trying to work out, is if I only have full UK postcode in the database, is there a way I can cross-reference against the table below so I can count how many properties in our Database are in London?

CREATE TABLE #LondonPostcode

(
Postcode VARCHAR(2) NOT NULL
,Region VARCHAR (10) NOT NULL
)

INSERT INTO #LondonPostcode

VALUES ('BR','London') ,('CR','London') ,('DA','London') ,('E','London') ,('EC','London')
,('EN','London') ,('HA','London') ,('IG','London') ,('KT','London') ,('N','London') ,('NW','London')
,('RM','London') ,('SE','London') ,('SM','London') ,('SW','London') ,('TW','London') ,('UB','London')
,('W','London') ,('WC','London'),('WD','London')
Acroyear
  • 1,354
  • 3
  • 22
  • 37
Steve B
  • 1
  • Take a look at an open dataset I publish [Open Postcode Geo](https://www.getthedata.com/open-postcode-geo). If you load this into your temp table and join on postcode you will have the postcode area available to you. You can then select postcode areas in ('BR', 'CR') etc. Does that work? – Dan Winchester Mar 02 '20 at 21:18
  • Thank you for that, I will actually see if I can pass this on to my superiors who may allow a reference table like that to be added to the database itself so it can be queried upon much more simply. Very helpful much appreciated. – Steve B Mar 03 '20 at 10:10

0 Answers0