December 30, 2016 at 5:42 am
Hi all,
I currently have an SSIS package that completes this task, however, it seems that the requests have been doing up for transforming data we're receiving from outside sources into the format we need. Rather than having to therefore write out an SSIS package each time, modifying it a little to cater for the "uniqueness" of the file, I wondered if anyone could suggest how to do this in a set based format, rather than iterative (which is how I do it in SSIS).
Firstly, here is a simplified DLM and DLL for you:
CREATE TABLE #SamplePostcodes (Postcode VARCHAR(10), Rating VARCHAR(2)); --VARCHAR, as Ratings can something be an alphanumeric.
INSERT INTO #SamplePostcodes
SELECT 'AB10 6AA', 2
UNION
SELECT 'AB10 6AB', 2
UNION
SELECT 'AB10 6AD', 2
UNION
SELECT 'AB10 6AE', 2
UNION
SELECT 'AB10 6AG', 1
UNION
SELECT 'AB10 6AH', 1
UNION
SELECT 'AB10 6AJ', 1
UNION
SELECT 'AB10 6AL', 3
UNION
SELECT 'AB10 6AN', 3
UNION
SELECT 'AB10 6AP', 3
UNION
SELECT 'AB10 6AQ', 4
UNION
SELECT 'AB10 6AR', 3
UNION
SELECT 'AB10 6AS', 3
UNION
SELECT 'AB10 6AT', 3
UNION
SELECT 'AB10 6AU', 3
UNION
SELECT 'AB10 6AX', 4
UNION
SELECT 'AB10 6AY', 3
UNION
SELECT 'AB10 6BA', 2
UNION
SELECT 'AB10 6BB', 3
UNION
SELECT 'AB10 6BE', 3
UNION
SELECT 'AB10 6BF', 3
UNION
SELECT 'AB10 6BJ', 5
UNION
SELECT 'AB10 6BL', 2
UNION
SELECT 'AB10 6BN', 3
UNION
SELECT 'AB10 6BP', 3
UNION
SELECT 'AB10 6BQ', 3
UNION
SELECT 'AB10 6BR', 3
UNION
SELECT 'AB10 6BS', 3
UNION
SELECT 'AB10 6BT', 3
UNION
SELECT 'AB10 6BU', 3
UNION
SELECT 'AB10 6BW', 3
UNION
SELECT 'AB10 6BX', 3
UNION
SELECT 'AB10 6BY', 3
UNION
SELECT 'AB10 6BZ', 2
UNION
SELECT 'AB10 6DA', 3
UNION
SELECT 'AB10 6DB', 2
UNION
SELECT 'AB10 6DD', 2
UNION
SELECT 'AB10 6DE', 2
UNION
SELECT 'AB10 6DF', 3
UNION
SELECT 'AB10 6DG', 3
UNION
SELECT 'AB10 6DH', 3
UNION
SELECT 'AB10 6DJ', 2
UNION
SELECT 'AB10 6DL', 3
UNION
SELECT 'AB10 6DS', 2
UNION
SELECT 'AB10 6DT', 2
UNION
SELECT 'AB10 6DU', 3
UNION
SELECT 'AB10 6ED', 3
UNION
SELECT 'AB10 6EE', 4
UNION
SELECT 'AB10 6EG', 4
UNION
SELECT 'AB10 6EH', 4
UNION
SELECT 'AB10 6EJ', 4
UNION
SELECT 'AB10 6EL', 4
UNION
SELECT 'AB10 6EN', 4
UNION
SELECT 'AB10 6EP', 4
UNION
SELECT 'AB10 6EQ', 4
UNION
SELECT 'AB10 6ER', 4
UNION
SELECT 'AB10 6ES', 4
UNION
SELECT 'AB10 6ET', 4
UNION
SELECT 'AB10 6EU', 3
UNION
SELECT 'AB10 6EW', 1
UNION
SELECT 'AB10 6EX', 2
UNION
SELECT 'AB10 6EY', 3
UNION
SELECT 'AB10 6FA', 3
UNION
SELECT 'AB10 6FB', 3
UNION
SELECT 'AB10 6FL', 3
UNION
SELECT 'AB10 6FN', 2
UNION
SELECT 'AB10 6FP', 3
UNION
SELECT 'AB10 6GA', 1
UNION
SELECT 'AB10 6HA', 3
UNION
SELECT 'AB10 6HB', 4
UNION
SELECT 'AB10 6HD', 3
UNION
SELECT 'AB10 6HE', 4
UNION
SELECT 'AB10 6HF', 3
UNION
SELECT 'AB10 6HG', 3
UNION
SELECT 'AB10 6HH', 3
UNION
SELECT 'AB10 6HJ', 3
UNION
SELECT 'AB10 6HL', 3
UNION
SELECT 'AB10 6HN', 3
UNION
SELECT 'AB10 6HP', 3
UNION
SELECT 'AB10 6HQ', 3
UNION
SELECT 'AB10 6HR', 4
UNION
SELECT 'AB10 6HS', 3
UNION
SELECT 'AB10 6HT', 2
UNION
SELECT 'AB10 6HU', 3
UNION
SELECT 'AB10 6HW', 4
UNION
SELECT 'AB10 6HX', 4
UNION
SELECT 'AB10 6HY', 4
UNION
SELECT 'AB10 6JA', 3
UNION
SELECT 'AB10 6JB', 4
UNION
SELECT 'AB10 6JD', 4
UNION
SELECT 'AB10 6JE', 4
UNION
SELECT 'AB10 6JF', 4
UNION
SELECT 'AB10 6JG', 3
UNION
SELECT 'AB10 6JH', 4
UNION
SELECT 'AB10 6JJ', 4
UNION
SELECT 'AB10 6JL', 4
UNION
SELECT 'AB10 6JN', 3
UNION
SELECT 'AB10 6JP', 3
UNION
SELECT 'AB10 6JQ', 4
UNION
SELECT 'AB10 6JR', 4
UNION
SELECT 'AB10 6JU', 4
UNION
SELECT 'AB10 6JW', 4
UNION
SELECT 'AB10 6JY', 3
UNION
SELECT 'AB10 6JZ', 3
UNION
SELECT 'AB10 6LE', 4
UNION
SELECT 'AB10 6LF', 4
UNION
SELECT 'AB10 6LG', 4
UNION
SELECT 'AB10 6LH', 4
UNION
SELECT 'AB10 6LP', 3
UNION
SELECT 'AB10 6LQ', 3
UNION
SELECT 'AB10 6LR', 3
UNION
SELECT 'AB10 6LX', 4
UNION
SELECT 'AB10 6LZ', 3
UNION
SELECT 'AB10 6NA', 4
UNION
SELECT 'AB10 6NB', 4
UNION
SELECT 'AB10 6ND', 4
UNION
SELECT 'AB10 6NJ', 4
UNION
SELECT 'AB10 6NL', 4
UNION
SELECT 'AB10 6NN', 4
UNION
SELECT 'AB10 6NP', 4
UNION
SELECT 'AB10 6NQ', 4
UNION
SELECT 'AB10 6NR', 4
UNION
SELECT 'AB10 6NU', 4
UNION
SELECT 'AB10 6NW', 4
UNION
SELECT 'AB10 6NY', 4
UNION
SELECT 'AB10 6NZ', 4
UNION
SELECT 'AB10 6PA', 3
UNION
SELECT 'AB10 6PB', 4
UNION
SELECT 'AB10 6PD', 4
UNION
SELECT 'AB10 6PE', 3
UNION
SELECT 'AB10 6PF', 3
UNION
SELECT 'AB10 6PG', 3
UNION
SELECT 'AB10 6PH', 4
UNION
SELECT 'AB10 6PJ', 4
UNION
SELECT 'AB10 6PL', 4
UNION
SELECT 'AB10 6PN', 4
UNION
SELECT 'AB10 6PP', 4
UNION
SELECT 'AB10 6PQ', 2
UNION
SELECT 'AB10 6PR', 2
UNION
SELECT 'AB10 6PS', 4
UNION
SELECT 'AB10 6PT', 4
UNION
SELECT 'AB10 6PU', 3
UNION
SELECT 'AB10 6PW', 3
UNION
SELECT 'AB10 6PX', 3
UNION
SELECT 'AB10 6PY', 3
UNION
SELECT 'AB10 6PZ', 3
UNION
SELECT 'AB10 6QA', 3
UNION
SELECT 'AB10 6QB', 2
UNION
SELECT 'AB10 6QD', 3
UNION
SELECT 'AB10 6QE', 3
UNION
SELECT 'AB10 6QF', 3
UNION
SELECT 'AB10 6QG', 4
UNION
SELECT 'AB10 6QH', 4
UNION
SELECT 'AB10 6QJ', 3
UNION
SELECT 'AB10 6QL', 3
UNION
SELECT 'AB10 6QN', 3
UNION
SELECT 'AB10 6QP', 3
UNION
SELECT 'AB10 6QQ', 3
UNION
SELECT 'AB10 6QR', 3
UNION
SELECT 'AB10 6QS', 3
UNION
SELECT 'AB10 6QT', 2
UNION
SELECT 'AB10 6QU', 4
UNION
SELECT 'AB10 6QW', 4
UNION
SELECT 'AB10 6QX', 3
UNION
SELECT 'AB10 6QY', 4
UNION
SELECT 'AB10 6QZ', 4
UNION
SELECT 'AB10 6RA', 3
UNION
SELECT 'AB10 6RB', 3
UNION
SELECT 'AB10 6RD', 3
UNION
SELECT 'AB10 6RE', 3
UNION
SELECT 'AB10 6RF', 3
UNION
SELECT 'AB10 6RG', 3
UNION
SELECT 'AB10 6RH', 3
UNION
SELECT 'AB10 6RJ', 4
UNION
SELECT 'AB10 6RL', 4
UNION
SELECT 'AB10 6RN', 4
UNION
SELECT 'AB10 6RP', 3
UNION
SELECT 'AB10 6RQ', 4
UNION
SELECT 'AB10 6RR', 4
UNION
SELECT 'AB10 6RS', 4
UNION
SELECT 'AB10 6RT', 4
UNION
SELECT 'AB10 6RU', 4
UNION
SELECT 'AB10 6RW', 4
UNION
SELECT 'AB10 6RX', 3
UNION
SELECT 'AB10 6RY', 3
UNION
SELECT 'AB10 6RZ', 4
UNION
SELECT 'AB10 6SA', 3
UNION
SELECT 'AB10 6SB', 2
UNION
SELECT 'AB10 6SD', 3
UNION
SELECT 'AB10 6SE', 4
UNION
SELECT 'AB10 6SF', 3
UNION
SELECT 'AB10 6SG', 4
UNION
SELECT 'AB10 6SH', 4
UNION
SELECT 'AB10 6SJ', 2
UNION
SELECT 'AB10 6SL', 2
UNION
SELECT 'AB10 6SN', 2
UNION
SELECT 'AB10 6SP', 2
UNION
SELECT 'AB10 6SQ', 3
UNION
SELECT 'AB10 6SR', 3
UNION
SELECT 'AB10 6SS', 4
UNION
SELECT 'AB10 6ST', 2
UNION
SELECT 'AB10 6SU', 4
UNION
SELECT 'AB10 6SW', 4
UNION
SELECT 'AB10 6SX', 3
UNION
SELECT 'AB10 6SY', 3
UNION
SELECT 'AB10 6SZ', 3
UNION
SELECT 'AB10 6TA', 3
UNION
SELECT 'AB10 6TB', 3
UNION
SELECT 'AB10 6TD', 4
UNION
SELECT 'AB10 6TE', 3
UNION
SELECT 'AB10 6TF', 3
UNION
SELECT 'AB10 6TG', 3
UNION
SELECT 'AB10 7EQ', 1
UNION
SELECT 'AB10 7ER', 2
UNION
SELECT 'AB10 7ES', 2
UNION
SELECT 'AB10 7ET', 2
UNION
SELECT 'AB10 7EU', 2
UNION
SELECT 'AB10 7EW', 2
UNION
SELECT 'AB10 7EX', 2
UNION
SELECT 'AB10 7EY', 2
UNION
SELECT 'AB10 7EZ', 2
UNION
SELECT 'AB10 7FA', 1
UNION
SELECT 'AB10 7FB', 4
UNION
SELECT 'AB10 7FD', 4
UNION
SELECT 'AB10 7FE', 4
UNION
SELECT 'AB10 7FF', 4
UNION
SELECT 'AB10 7FG', 3
UNION
SELECT 'AB10 7FH', 1
UNION
SELECT 'AB10 7FJ', 4
UNION
SELECT 'AB10 7FL', 2
UNION
SELECT 'AB10 7FN', 2
UNION
SELECT 'AB10 7FP', 4
UNION
SELECT 'AB10 7FQ', 4
UNION
SELECT 'AB10 7FR', 4
UNION
SELECT 'AB10 7FS', 1
UNION
SELECT 'AB10 7FT', 1
UNION
SELECT 'AB10 7FW', 4
UNION
SELECT 'AB10 7FX', 4
UNION
SELECT 'AB10 7FY', 4
UNION
SELECT 'AB10 7FZ', 4
UNION
SELECT 'AB10 7GA', 2
UNION
SELECT 'AB10 7GB', 3
UNION
SELECT 'AB10 7GD', 2
UNION
SELECT 'AB10 7GE', 2
UNION
SELECT 'AB10 7GF', 1
UNION
SELECT 'AB10 7GG', 1
UNION
SELECT 'AB10 7GH', 4
UNION
SELECT 'AB10 7GJ', 2
UNION
SELECT 'AB10 7GR', 4
UNION
SELECT 'AB10 7GS', 2
UNION
SELECT 'AB10 7GT', 2
UNION
SELECT 'AB10 7GU', 2
UNION
SELECT 'AB10 7GW', 1
UNION
SELECT 'AB10 7GX', 3
UNION
SELECT 'AB10 7GY', 2
UNION
SELECT 'AB10 7GZ', 2
UNION
SELECT 'AB10 7HA', 2
UNION
SELECT 'AB10 7HB', 3
UNION
SELECT 'AB10 7HD', 2
UNION
SELECT 'AB10 7HE', 3
UNION
SELECT 'AB10 7HF', 2
UNION
SELECT 'AB10 7HG', 2
UNION
SELECT 'AB10 7HH', 2
UNION
SELECT 'AB10 7HJ', 2
UNION
SELECT 'AB10 7HL', 2
UNION
SELECT 'AB10 7HN', 2
UNION
SELECT 'AB10 7HP', 2
UNION
SELECT 'AB10 7HQ', 2
UNION
SELECT 'AB10 7HR', 3
UNION
SELECT 'AB10 7HS', 3
UNION
SELECT 'AB10 7HT', 2
UNION
SELECT 'AB10 7HU', 1
UNION
SELECT 'AB10 7HW', 2
UNION
SELECT 'AB10 7HX', 2
UNION
SELECT 'AB10 7HY', 3
UNION
SELECT 'AB10 7HZ', 2
UNION
SELECT 'AB10 7JA', 2
UNION
SELECT 'AB10 7JB', 2
UNION
SELECT 'AB10 7JD', 3
UNION
SELECT 'AB10 7JE', 2
UNION
SELECT 'AB10 7JF', 2
UNION
SELECT 'AB10 7JG', 2
UNION
SELECT 'AB10 7JH', 3
UNION
SELECT 'AB10 7JJ', 2
UNION
SELECT 'AB10 7JL', 2
UNION
SELECT 'AB10 7JN', 2
UNION
SELECT 'AB10 7JP', 2
UNION
SELECT 'AB10 7JQ', 3
UNION
SELECT 'AB10 7JR', 2
UNION
SELECT 'AB10 7JS', 1
UNION
SELECT 'AB10 7JT', 1
UNION
SELECT 'AB10 7JU', 3
UNION
SELECT 'AB10 7JW', 3
UNION
SELECT 'AB10 7JX', 2
UNION
SELECT 'AB10 7JY', 2
UNION
SELECT 'AB10 7JZ', 4
UNION
SELECT 'AB10 7LA', 1
UNION
SELECT 'AB10 7LB', 1
UNION
SELECT 'AB10 7LD', 2
UNION
SELECT 'AB10 7LE', 4
UNION
SELECT 'AB10 7LF', 4
UNION
SELECT 'AB10 7LG', 3
UNION
SELECT 'AB10 7LH', 4
UNION
SELECT 'AB10 7LJ', 2
UNION
SELECT 'AB10 7LL', 3
UNION
SELECT 'AB10 7LN', 3
UNION
SELECT 'AB10 7LP', 3
UNION
SELECT 'AB10 7LQ', 3
UNION
SELECT 'AB10 7LR', 4
UNION
SELECT 'AB10 7LS', 2
UNION
SELECT 'AB10 7LT', 4
UNION
SELECT 'AB10 7LU', 2
UNION
SELECT 'AB10 7LW', 2
UNION
SELECT 'AB10 7LX', 3
UNION
SELECT 'AB10 7LY', 3
UNION
SELECT 'AB10 7LZ', 3
UNION
SELECT 'AB10 7NA', 3
UNION
SELECT 'AB10 7NB', 4
UNION
SELECT 'AB10 6TJ', 4
UNION
SELECT 'AB10 6TP', 3
UNION
SELECT 'AB10 6TQ', 3
UNION
SELECT 'AB10 6TR', 3
UNION
SELECT 'AB10 6TS', 3
UNION
SELECT 'AB10 6UQ', 4
UNION
SELECT 'AB10 6UR', 4
UNION
SELECT 'AB10 6US', 4
UNION
SELECT 'AB10 6UT', 4
UNION
SELECT 'AB10 6UX', 4
UNION
SELECT 'AB10 6UZ', 3
UNION
SELECT 'AB10 6WD', 3
UNION
SELECT 'AB10 6WE', 3
UNION
SELECT 'AB10 6XA', 4
UNION
SELECT 'AB10 6XB', 4
UNION
SELECT 'AB10 6XD', 3
UNION
SELECT 'AB10 6XE', 3
UNION
SELECT 'AB10 6XF', 3
UNION
SELECT 'AB10 6XH', 4
UNION
SELECT 'AB10 6XJ', 3
UNION
SELECT 'AB10 6XL', 4
UNION
SELECT 'AB10 6XN', 4
UNION
SELECT 'AB10 6XP', 3
UNION
SELECT 'AB10 6XQ', 4
UNION
SELECT 'AB10 6XR', 4
UNION
SELECT 'AB10 6XS', 3
UNION
SELECT 'AB10 6XT', 3
UNION
SELECT 'AB10 6XU', 4
UNION
SELECT 'AB10 6XW', 4
UNION
SELECT 'AB10 6XX', 3
UNION
SELECT 'AB10 6YA', 4
UNION
SELECT 'AB10 7AA', 2
UNION
SELECT 'AB10 7AB', 2
UNION
SELECT 'AB10 7AD', 2
UNION
SELECT 'AB10 7AE', 2
UNION
SELECT 'AB10 7AF', 2
UNION
SELECT 'AB10 7AG', 2
UNION
SELECT 'AB10 7AH', 2
UNION
SELECT 'AB10 7AJ', 2
UNION
SELECT 'AB10 7AL', 1
UNION
SELECT 'AB10 7AN', 3
UNION
SELECT 'AB10 7AP', 2
UNION
SELECT 'AB10 7AQ', 4
UNION
SELECT 'AB10 7AR', 2
UNION
SELECT 'AB10 7AS', 2
UNION
SELECT 'AB10 7AT', 2
UNION
SELECT 'AB10 7AU', 2
UNION
SELECT 'AB10 7AW', 2
UNION
SELECT 'AB10 7AX', 2
UNION
SELECT 'AB10 7AY', 2
UNION
SELECT 'AB10 7AZ', 2
UNION
SELECT 'AB10 7BA', 1
UNION
SELECT 'AB10 7BB', 1
UNION
SELECT 'AB10 7BD', 2
UNION
SELECT 'AB10 7BE', 2
UNION
SELECT 'AB10 7BF', 3
UNION
SELECT 'AB10 7BG', 2
UNION
SELECT 'AB10 7BH', 2
UNION
SELECT 'AB10 7BJ', 2
UNION
SELECT 'AB10 7BL', 2
UNION
SELECT 'AB10 7BN', 2
UNION
SELECT 'AB10 7BP', 1
UNION
SELECT 'AB10 7BQ', 1
UNION
SELECT 'AB10 7BR', 2
UNION
SELECT 'AB10 7BS', 2
UNION
SELECT 'AB10 7BT', 2
UNION
SELECT 'AB10 7BU', 2
UNION
SELECT 'AB10 7BW', 2
UNION
SELECT 'AB10 7BX', 2
UNION
SELECT 'AB10 7BY', 2
UNION
SELECT 'AB10 7BZ', 2
UNION
SELECT 'AB10 7DA', 2
UNION
SELECT 'AB10 7DB', 1
UNION
SELECT 'AB10 7DD', 1
UNION
SELECT 'AB10 7DE', 2
UNION
SELECT 'AB10 7DF', 2
UNION
SELECT 'AB10 7DG', 2
UNION
SELECT 'AB10 7DH', 1
UNION
SELECT 'AB10 7DJ', 2
UNION
SELECT 'AB10 7DL', 1
UNION
SELECT 'AB10 7DN', 2
UNION
SELECT 'AB10 7DP', 2
UNION
SELECT 'AB10 7DQ', 2
UNION
SELECT 'AB10 7DR', 2
UNION
SELECT 'AB10 7DS', 2
UNION
SELECT 'AB10 7DT', 2
UNION
SELECT 'AB10 7DU', 2
UNION
SELECT 'AB10 7DX', 2
UNION
SELECT 'AB10 7DY', 2
UNION
SELECT 'AB10 7DZ', 1
UNION
SELECT 'AB10 7EA', 2
UNION
SELECT 'AB10 7EB', 2
UNION
SELECT 'AB10 7ED', 2
UNION
SELECT 'AB10 7EE', 2
UNION
SELECT 'AB10 7EF', 3
UNION
SELECT 'AB10 7EG', 2
UNION
SELECT 'AB10 7EH', 1
UNION
SELECT 'AB10 7EJ', 3
UNION
SELECT 'AB10 7EL', 1
UNION
SELECT 'AB10 7EN', 1
UNION
SELECT 'AB10 7EP', 2
UNION
SELECT 'AB10 7ND', 3
UNION
SELECT 'AB10 7NE', 4
UNION
SELECT 'AB10 7NF', 4
UNION
SELECT 'AB10 7NG', 3
UNION
SELECT 'AB10 7NH', 4
UNION
SELECT 'AB10 7NJ', 4
UNION
SELECT 'AB10 7NL', 3
UNION
SELECT 'AB10 7NN', 4
UNION
SELECT 'AB10 7NP', 4
UNION
SELECT 'AB10 7NQ', 4
UNION
SELECT 'AB10 7NR', 4
UNION
SELECT 'AB10 7NS', 3
UNION
SELECT 'AB10 7NT', 3
UNION
SELECT 'AB10 7NU', 4
UNION
SELECT 'AB10 7NW', 4
UNION
SELECT 'AB10 7NX', 3
UNION
SELECT 'AB10 7NY', 3
UNION
SELECT 'AB10 7NZ', 3
UNION
SELECT 'AB10 7PA', 2
UNION
SELECT 'AB10 7PB', 3
UNION
SELECT 'AB10 7PD', 4
UNION
SELECT 'AB10 7PE', 4
UNION
SELECT 'AB10 7PF', 4
UNION
SELECT 'AB10 7PG', 4
UNION
SELECT 'AB10 7PH', 4
UNION
SELECT 'AB10 7PL', 4
UNION
SELECT 'AB10 7PN', 3
UNION
SELECT 'AB10 7PP', 4
UNION
SELECT 'AB10 7PQ', 4
UNION
SELECT 'AB10 7PR', 4
UNION
SELECT 'AB10 7PS', 4
UNION
SELECT 'AB10 7PT', 4
UNION
SELECT 'AB10 7PU', 4
UNION
SELECT 'AB10 7PW', 4
UNION
SELECT 'AB10 7PX', 3
UNION
SELECT 'AB10 7PY', 3
UNION
SELECT 'AB10 7QA', 2
UNION
SELECT 'AB10 7QB', 4
UNION
SELECT 'AB10 7QD', 4
UNION
SELECT 'AB10 7QE', 2
UNION
SELECT 'AB10 7QF', 3
UNION
SELECT 'AB10 7QG', 2
UNION
SELECT 'AB10 7QQ', 2
UNION
SELECT 'AB10 7UG', 2;
SELECT *
FROM #SamplePostcodes
ORDER BY Postcode;
GO
DROP TABLE #SamplePostcodes
What I need to do in summarise these postcodes into Bandings. Each time the Rating changes, the line needs to be written out, OR anything other than the Unit (for those that aren't British, that's the last two letters of the Postcode, for example AA in: AB10 2AA)
I also, however, need to split the postcode out into it's separate parts in the banding. Postcodes are separated into 4 parts. I do, however, already have SQL for this, which you can see below:
CREATE TABLE #Postcode (Postcode VARCHAR(10));
INSERT INTO #Postcode
VALUES ('A1 1AA'),
('AA1 2AB'),
('B11 3AC'),
('AC11 4AD'),
('D1A 5AE');
SELECT Postcode,
CASE WHEN ISNUMERIC(SUBSTRING(Postcode,2,1)) = 1 THEN LEFT(Postcode, 1)
ELSE LEFT(Postcode, 2) END AS Area,
CASE WHEN ISNUMERIC(SUBSTRING(Postcode,2,1)) = 1 THEN SUBSTRING(Postcode, 2, CHARINDEX(' ', Postcode) -2)
ELSE SUBSTRING(Postcode, 3, CHARINDEX(' ', Postcode) -3) END AS District,
LEFT(RIGHT(Postcode, 3),1) AS Sector,
RIGHT(Postcode, 2) AS Unit
FROM #Postcode;
DROP TABLE #Postcode;
This gives the results:
Postcode Area District Sector Unit
---------- ---- ---------- ------ ----
A1 1AA A 1 1 AA
AA1 2AB AA 1 2 AB
B11 3AC B 11 3 AC
AC11 4AD AC 11 4 AD
D1A 5AE D 1A 5 AE
Finally, how I need to return my results. What I need to do is provide the first postcode for the banding, split into it's sections, followed by the last postcode in the banding (again split into sections), and then finally the Rating for that banding.
So, for example, using the sample data the first line would be:
Area District Sector Unit Area2 District2 Sector2 Unit2 Rating
---- ----------- ----------- ---- ----- ----------- ----------- ----- -----------
AB 10 6 AA AB 10 6 AE 2
The key part is though that is the Area, Sector, Unit or rating changes, a new banding starts.
I've included how the results should look from the start of AB10 6 to the end of AB10 7. Apologies, it is in CSV format (as it's the exported version)
Area,District,Sector,Unit,Area2,District2,Sector2,Unit2,Rating,
AB,10,6,AA,AB,10,6,AE,2,
AB,10,6,AG,AB,10,6,AJ,1,
AB,10,6,AL,AB,10,6,AP,3,
AB,10,6,AQ,AB,10,6,AQ,4,
AB,10,6,AR,AB,10,6,AU,3,
AB,10,6,AX,AB,10,6,AX,4,
AB,10,6,AY,AB,10,6,AY,3,
AB,10,6,BA,AB,10,6,BA,2,
AB,10,6,BB,AB,10,6,BF,3,
AB,10,6,BJ,AB,10,6,BJ,5,
AB,10,6,BL,AB,10,6,BL,2,
AB,10,6,BN,AB,10,6,BY,3,
AB,10,6,BZ,AB,10,6,BZ,2,
AB,10,6,DA,AB,10,6,DA,3,
AB,10,6,DB,AB,10,6,DE,2,
AB,10,6,DF,AB,10,6,DH,3,
AB,10,6,DJ,AB,10,6,DJ,2,
AB,10,6,DL,AB,10,6,DL,3,
AB,10,6,DS,AB,10,6,DT,2,
AB,10,6,DU,AB,10,6,ED,3,
AB,10,6,EE,AB,10,6,ET,4,
AB,10,6,EU,AB,10,6,EU,3,
AB,10,6,EW,AB,10,6,EW,1,
AB,10,6,EX,AB,10,6,EX,2,
AB,10,6,EY,AB,10,6,FL,3,
AB,10,6,FN,AB,10,6,FN,2,
AB,10,6,FP,AB,10,6,FP,3,
AB,10,6,GA,AB,10,6,GA,1,
AB,10,6,HA,AB,10,6,HA,3,
AB,10,6,HB,AB,10,6,HB,4,
AB,10,6,HD,AB,10,6,HD,3,
AB,10,6,HE,AB,10,6,HE,4,
AB,10,6,HF,AB,10,6,HQ,3,
AB,10,6,HR,AB,10,6,HR,4,
AB,10,6,HS,AB,10,6,HS,3,
AB,10,6,HT,AB,10,6,HT,2,
AB,10,6,HU,AB,10,6,HU,3,
AB,10,6,HW,AB,10,6,HY,4,
AB,10,6,JA,AB,10,6,JA,3,
AB,10,6,JB,AB,10,6,JF,4,
AB,10,6,JG,AB,10,6,JG,3,
AB,10,6,JH,AB,10,6,JL,4,
AB,10,6,JN,AB,10,6,JP,3,
AB,10,6,JQ,AB,10,6,JW,4,
AB,10,6,JY,AB,10,6,JZ,3,
AB,10,6,LE,AB,10,6,LH,4,
AB,10,6,LP,AB,10,6,LR,3,
AB,10,6,LX,AB,10,6,LX,4,
AB,10,6,LZ,AB,10,6,LZ,3,
AB,10,6,NA,AB,10,6,NZ,4,
AB,10,6,PA,AB,10,6,PA,3,
AB,10,6,PB,AB,10,6,PD,4,
AB,10,6,PE,AB,10,6,PG,3,
AB,10,6,PH,AB,10,6,PP,4,
AB,10,6,PQ,AB,10,6,PR,2,
AB,10,6,PS,AB,10,6,PT,4,
AB,10,6,PU,AB,10,6,QA,3,
AB,10,6,QB,AB,10,6,QB,2,
AB,10,6,QD,AB,10,6,QF,3,
AB,10,6,QG,AB,10,6,QH,4,
AB,10,6,QJ,AB,10,6,QS,3,
AB,10,6,QT,AB,10,6,QT,2,
AB,10,6,QU,AB,10,6,QW,4,
AB,10,6,QX,AB,10,6,QX,3,
AB,10,6,QY,AB,10,6,QZ,4,
AB,10,6,RA,AB,10,6,RH,3,
AB,10,6,RJ,AB,10,6,RN,4,
AB,10,6,RP,AB,10,6,RP,3,
AB,10,6,RQ,AB,10,6,RW,4,
AB,10,6,RX,AB,10,6,RY,3,
AB,10,6,RZ,AB,10,6,RZ,4,
AB,10,6,SA,AB,10,6,SA,3,
AB,10,6,SB,AB,10,6,SB,2,
AB,10,6,SD,AB,10,6,SD,3,
AB,10,6,SE,AB,10,6,SE,4,
AB,10,6,SF,AB,10,6,SF,3,
AB,10,6,SG,AB,10,6,SH,4,
AB,10,6,SJ,AB,10,6,SP,2,
AB,10,6,SQ,AB,10,6,SR,3,
AB,10,6,SS,AB,10,6,SS,4,
AB,10,6,ST,AB,10,6,ST,2,
AB,10,6,SU,AB,10,6,SW,4,
AB,10,6,SX,AB,10,6,TB,3,
AB,10,6,TD,AB,10,6,TD,4,
AB,10,6,TE,AB,10,6,TG,3,
AB,10,6,TJ,AB,10,6,TJ,4,
AB,10,6,TP,AB,10,6,TS,3,
AB,10,6,UQ,AB,10,6,UX,4,
AB,10,6,UZ,AB,10,6,WE,3,
AB,10,6,XA,AB,10,6,XB,4,
AB,10,6,XD,AB,10,6,XF,3,
AB,10,6,XH,AB,10,6,XH,4,
AB,10,6,XJ,AB,10,6,XJ,3,
AB,10,6,XL,AB,10,6,XN,4,
AB,10,6,XP,AB,10,6,XP,3,
AB,10,6,XQ,AB,10,6,XR,4,
AB,10,6,XS,AB,10,6,XT,3,
AB,10,6,XU,AB,10,6,XW,4,
AB,10,6,XX,AB,10,6,XX,3,
AB,10,6,YA,AB,10,6,YA,4,
AB,10,7,AA,AB,10,7,AJ,2,
AB,10,7,AL,AB,10,7,AL,1,
AB,10,7,AN,AB,10,7,AN,3,
AB,10,7,AP,AB,10,7,AP,2,
AB,10,7,AQ,AB,10,7,AQ,4,
AB,10,7,AR,AB,10,7,AZ,2,
AB,10,7,BA,AB,10,7,BB,1,
AB,10,7,BD,AB,10,7,BE,2,
AB,10,7,BF,AB,10,7,BF,3,
AB,10,7,BG,AB,10,7,BN,2,
AB,10,7,BP,AB,10,7,BQ,1,
AB,10,7,BR,AB,10,7,DA,2,
AB,10,7,DB,AB,10,7,DD,1,
AB,10,7,DE,AB,10,7,DG,2,
AB,10,7,DH,AB,10,7,DH,1,
AB,10,7,DJ,AB,10,7,DJ,2,
AB,10,7,DL,AB,10,7,DL,1,
AB,10,7,DN,AB,10,7,DY,2,
AB,10,7,DZ,AB,10,7,DZ,1,
AB,10,7,EA,AB,10,7,EE,2,
AB,10,7,EF,AB,10,7,EF,3,
AB,10,7,EG,AB,10,7,EG,2,
AB,10,7,EH,AB,10,7,EH,1,
AB,10,7,EJ,AB,10,7,EJ,3,
AB,10,7,EL,AB,10,7,EN,1,
AB,10,7,EP,AB,10,7,EP,2,
AB,10,7,EQ,AB,10,7,EQ,1,
AB,10,7,ER,AB,10,7,EZ,2,
AB,10,7,FA,AB,10,7,FA,1,
AB,10,7,FB,AB,10,7,FF,4,
AB,10,7,FG,AB,10,7,FG,3,
AB,10,7,FH,AB,10,7,FH,1,
AB,10,7,FJ,AB,10,7,FJ,4,
AB,10,7,FL,AB,10,7,FN,2,
AB,10,7,FP,AB,10,7,FR,4,
AB,10,7,FS,AB,10,7,FT,1,
AB,10,7,FW,AB,10,7,FZ,4,
AB,10,7,GA,AB,10,7,GA,2,
AB,10,7,GB,AB,10,7,GB,3,
AB,10,7,GD,AB,10,7,GE,2,
AB,10,7,GF,AB,10,7,GG,1,
AB,10,7,GH,AB,10,7,GH,4,
AB,10,7,GJ,AB,10,7,GJ,2,
AB,10,7,GR,AB,10,7,GR,4,
AB,10,7,GS,AB,10,7,GU,2,
AB,10,7,GW,AB,10,7,GW,1,
AB,10,7,GX,AB,10,7,GX,3,
AB,10,7,GY,AB,10,7,HA,2,
AB,10,7,HB,AB,10,7,HB,3,
AB,10,7,HD,AB,10,7,HD,2,
AB,10,7,HE,AB,10,7,HE,3,
AB,10,7,HF,AB,10,7,HQ,2,
AB,10,7,HR,AB,10,7,HS,3,
AB,10,7,HT,AB,10,7,HT,2,
AB,10,7,HU,AB,10,7,HU,1,
AB,10,7,HW,AB,10,7,HX,2,
AB,10,7,HY,AB,10,7,HY,3,
AB,10,7,HZ,AB,10,7,JB,2,
AB,10,7,JD,AB,10,7,JD,3,
AB,10,7,JE,AB,10,7,JG,2,
AB,10,7,JH,AB,10,7,JH,3,
AB,10,7,JJ,AB,10,7,JP,2,
AB,10,7,JQ,AB,10,7,JQ,3,
AB,10,7,JR,AB,10,7,JR,2,
AB,10,7,JS,AB,10,7,JT,1,
AB,10,7,JU,AB,10,7,JW,3,
AB,10,7,JX,AB,10,7,JY,2,
AB,10,7,JZ,AB,10,7,JZ,4,
AB,10,7,LA,AB,10,7,LB,1,
AB,10,7,LD,AB,10,7,LD,2,
AB,10,7,LE,AB,10,7,LF,4,
AB,10,7,LG,AB,10,7,LG,3,
AB,10,7,LH,AB,10,7,LH,4,
AB,10,7,LJ,AB,10,7,LJ,2,
AB,10,7,LL,AB,10,7,LQ,3,
AB,10,7,LR,AB,10,7,LR,4,
AB,10,7,LS,AB,10,7,LS,2,
AB,10,7,LT,AB,10,7,LT,4,
AB,10,7,LU,AB,10,7,LW,2,
AB,10,7,LX,AB,10,7,NA,3,
AB,10,7,NB,AB,10,7,NB,4,
AB,10,7,ND,AB,10,7,ND,3,
AB,10,7,NE,AB,10,7,NF,4,
AB,10,7,NG,AB,10,7,NG,3,
AB,10,7,NH,AB,10,7,NJ,4,
AB,10,7,NL,AB,10,7,NL,3,
AB,10,7,NN,AB,10,7,NR,4,
AB,10,7,NS,AB,10,7,NT,3,
AB,10,7,NU,AB,10,7,NW,4,
AB,10,7,NX,AB,10,7,NZ,3,
AB,10,7,PA,AB,10,7,PA,2,
AB,10,7,PB,AB,10,7,PB,3,
AB,10,7,PD,AB,10,7,PL,4,
AB,10,7,PN,AB,10,7,PN,3,
AB,10,7,PP,AB,10,7,PW,4,
AB,10,7,PX,AB,10,7,PY,3,
AB,10,7,QA,AB,10,7,QA,2,
AB,10,7,QB,AB,10,7,QD,4,
AB,10,7,QE,AB,10,7,QE,2,
AB,10,7,QF,AB,10,7,QF,3,
AB,10,7,QG,AB,10,7,UG,2
I've thought about trying to do this in SQL before, but my mind automatically goes into an iterative solution for it (which is how I do it in SSIS).
If anyone has any questions, or feel that I have been less than clear on my goals, please do let me know.
Many thanks for any and all help you can suggest.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 30, 2016 at 7:45 am
I expect there are far better ways of doing this, but here's a first pass:
WITH Postcodes AS
(
SELECT
Postcode ,
CASE
WHEN ISNUMERIC(SUBSTRING(Postcode, 2, 1)) = 1
THEN LEFT(Postcode, 1)
ELSE
LEFT(Postcode, 2)
END AS Area ,
CASE
WHEN ISNUMERIC(SUBSTRING(Postcode, 2, 1)) = 1
THEN SUBSTRING(Postcode, 2, CHARINDEX(' ', Postcode) - 2)
ELSE
SUBSTRING(Postcode, 3, CHARINDEX(' ', Postcode) - 3)
END AS District ,
LEFT(RIGHT(Postcode, 3), 1) AS Sector ,
RIGHT(Postcode, 2) AS Unit ,
Rating ,
ROW_NUMBER() OVER ( ORDER BY Postcode ) AS RowNum ,
LAG(Rating) OVER ( ORDER BY Postcode ) AS PrevRating ,
LEAD(Rating) OVER ( ORDER BY Postcode ) AS NextRating
FROM
#SamplePostcodes
) ,
Ratings AS
(
SELECT
* ,
CASE
WHEN PrevRating IS NULL
THEN 1
WHEN PrevRating <> Rating
THEN 1
ELSE
0
END AS IsFirst ,
CASE
WHEN NextRating IS NULL
THEN 1
WHEN NextRating <> Rating
THEN 1
ELSE
0
END AS IsLast
FROM
Postcodes
) ,
RatingGroups AS
(
SELECT
Postcode ,
Area ,
District ,
Sector ,
Unit ,
Rating ,
RowNum ,
PrevRating ,
NextRating ,
IsFirst ,
IsLast ,
SUM(IsFirst) OVER ( ORDER BY Postcode ROWS UNBOUNDED PRECEDING ) AS RatingGroup
FROM
Ratings
)
SELECT
rg1.Area ,
rg1.District ,
rg1.Sector ,
rg1.Unit ,
rg2.Area ,
rg2.District ,
rg2.Sector ,
rg2.Unit ,
rg2.Rating
FROM
RatingGroups rg1
INNER JOIN RatingGroups rg2
ON rg1.RatingGroup = rg2.RatingGroup
WHERE
rg1.IsFirst = 1
AND rg2.IsLast = 1 ;
GO
Thomas Rushton
blog: https://thelonedba.wordpress.com
December 30, 2016 at 8:56 am
This is just a gaps and islands problem.
;
WITH Postcode_groups AS (
SELECT Postcode, Rating, ROW_NUMBER() OVER(ORDER BY Postcode) - ROW_NUMBER() OVER(PARTITION BY Rating ORDER BY Postcode) AS Grp
FROM #SamplePostcodes
--ORDER BY Postcode;
)
SELECT MIN(Postcode), MAX(Postcode), Rating
FROM Postcode_groups
GROUP BY Rating, Grp
ORDER BY MIN(Postcode)
I didn't bother to break the postcodes into their components, but that should be fairly simple from the results.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 30, 2016 at 9:26 am
Much more concise; runs quicker / more efficiently; returns the same number of rows. I just can't quite see *how* correctness is guaranteed (given the nature of the data), but I suspect I just need to play with it some more.
Thomas Rushton
blog: https://thelonedba.wordpress.com
December 30, 2016 at 9:39 am
Thank both, unfortuantely neither, at present do not meet specification (however, are close).
Although, with the data I have are correct, would not work with others where the rating from one Sector to another are the same (apologies, my test data didn't include a sample of this). For example, if you have rows such as:
AB14 7YU, 2
AB14 7YX, 3
AB14 8AB, 3
AB14 8AD, 4
Then the folowing would be returned:
AB, 14, 7, YU, AB, 14, 7, YU, 2
AB, 14, 7, YX, AB,14, 8, AB, 3
AB, 14, 8, AD, AB, 14, 8, AD, 4
What should happen, here, is that a new banding is created for AB14 8, as the Sector changed.
I should be able to use Drew's and amend though. I will post back shortly if so 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 30, 2016 at 9:47 am
This is an interesting one. Please post your solution, Thom, when you manage it (I have confidence ...!)
I've already played around with various Row_Number() ideas to no avail.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 30, 2016 at 9:48 am
I'll need to test some more when I return to the office after the New Year break, I don't have the data at home, but here is my solution incorperating Drew's prior post:
WITH Postcode_split AS (
SELECT Postcode,
CASE WHEN ISNUMERIC(SUBSTRING(Postcode,2,1)) = 1 THEN LEFT(Postcode, 1)
ELSE LEFT(Postcode, 2) END AS Area,
CASE WHEN ISNUMERIC(SUBSTRING(Postcode,2,1)) = 1 THEN SUBSTRING(Postcode, 2, CHARINDEX(' ', Postcode) -2)
ELSE SUBSTRING(Postcode, 3, CHARINDEX(' ', Postcode) -3) END AS District,
LEFT(RIGHT(Postcode, 3),1) AS Sector,
RIGHT(Postcode, 2) AS Unit,
Rating
FROM #SamplePostcodes
),
Postcode_groups AS (
SELECT Area,
District,
Sector,
Unit,
Rating,
ROW_NUMBER() OVER(ORDER BY Area, District, Sector, Unit) - ROW_NUMBER()
OVER(PARTITION BY Rating,
Sector,
District,
Area
ORDER BY Area,
District,
Sector,
Unit) AS Grp
FROM Postcode_split
)
SELECT MIN(Area) AS Area,
MIN(District) AS District,
MIN(Sector) AS Sector,
MIN(Unit) AS Unit,
MAX(Area) AS Area2,
MAX(District) AS District2,
MAX(Sector) AS Sector2,
MAX(Unit) AS Unit2,
Rating
FROM Postcode_groups
GROUP BY Rating, Grp
ORDER BY MIN(Area),
MIN(District),
MIN(Sector),
MIN(Unit);
Edit: Slight amendment.
Edit2: I pasted the above twice! Whoops.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply