11

I'm currently working on a report that shows me all postcodes covered by our sales team.

Each team covers over 100 postcodes. What I would like to do is create a report that brings back the clients within the postcode. Currently, my code looks like this.

SELECT * FROM tbl_ClientFile
 WHERE CLNTPOST1 LIKE ('B79%')
  OR CLNTPOST1 LIKE ('BB1%')
  OR CLNTPOST1 LIKE ('BB10%')
  OR CLNTPOST1 LIKE ('BB11%')
  OR CLNTPOST1 LIKE ('BB12%')
  OR CLNTPOST1 LIKE ('BB18%')
  OR CLNTPOST1 LIKE ('BB2%')
  OR CLNTPOST1 LIKE ('BB3%')
  OR CLNTPOST1 LIKE ('BB4%')
  OR CLNTPOST1 LIKE ('BB5%')
  OR CLNTPOST1 LIKE ('BB6%')
  OR CLNTPOST1 LIKE ('BB8%')
  OR CLNTPOST1 LIKE ('BB9%')
  OR CLNTPOST1 LIKE ('BB94%')
  OR CLNTPOST1 LIKE ('BD1%')
  OR CLNTPOST1 LIKE ('BD10%')
  OR CLNTPOST1 LIKE ('BD11%')
  OR CLNTPOST1 LIKE ('BD12%')
  OR CLNTPOST1 LIKE ('BD13%')
  OR CLNTPOST1 LIKE ('BD14%')
  OR CLNTPOST1 LIKE ('BD15%')
  OR CLNTPOST1 LIKE ('BD16%')
  OR CLNTPOST1 LIKE ('BD17%')
  OR CLNTPOST1 LIKE ('BD18%')
  OR CLNTPOST1 LIKE ('BD19%')
  OR CLNTPOST1 LIKE ('BD2%')
  OR CLNTPOST1 LIKE ('BD20%')
  OR CLNTPOST1 LIKE ('BD21%')
  OR CLNTPOST1 LIKE ('BD22%')
  OR CLNTPOST1 LIKE ('BD3%')
  OR CLNTPOST1 LIKE ('BD4%')
  OR CLNTPOST1 LIKE ('BD5%')
  OR CLNTPOST1 LIKE ('BD6%')

What I was hoping for is that there is a faster and easier way of doing this. Any suggestions would be greatly appreciated. Is there a way to create a variable for each sales Team like @SalesTeam1 = SELECT * FROM tbl_ClientFile WHERE POSTCODE1 like '' or like ''

Just fishing for ideas really. Cheers

islam nabil
  • 15
  • 3
RustyHamster
  • 359
  • 1
  • 5
  • 19
  • 3
    Why not just have a table that relates a team and their post codes? You could then solve this with a simple inner join. – Luaan Jun 22 '15 at 08:59
  • @Luaan +1, But as it looks, I would go for a `Town` table AND a `Team_Town` relationship table : as it looks, there's even no `Town` table. – Raphaël Althaus Jun 22 '15 at 09:01
  • I did think about creating a table for this and then doing an inner join. The problem is that the team is dynamic they are constantly changing the areas that they cover. So for one week they might cover Devon then the next week they cover all the post codes in Swindon. – RustyHamster Jun 22 '15 at 09:07
  • 1
    Note that you have a lot of redundancy in your conditions... Eg. BD10% to BD19% are covered by the condition BD1% – Captain Jun 22 '15 at 09:25
  • @RustyHamster: Nonetheless I think a table would be appropriate. It shouldn't be that complicated to keep the table up-to-date. If they are able to give you such a list, they should also be able to use a program to check the postal codes they cover. – Thorsten Kettner Jun 22 '15 at 09:28
  • 1
    Then you should build a table with 2 columns `Town`, `PostCode`, or may add the `Country` if applicable. – Eric Jun 22 '15 at 09:31
  • Thanks Eric this makes sense. I could create a table with town postcode and advisor. This way if an advisor changes his area i just run an update on the table. – RustyHamster Jun 22 '15 at 09:35

3 Answers3

12

WITH CTE AS
(
SELECT VALUE
FROM (
        VALUES ('B79'), ('BB1'), ('BB10'), ('BB11'), ('BB12'), ('BB18'), ('BB2'), ('BB3'), ('BB4'), ('BB5'), ('BB6'), ('BB8'), ('BB9'), ('BB94'), ('BD1'), ('BD10'), ('BD11'), ('BD12'), ('BD13'), ('BD14'),
                ('BD15'), ('BD16'), ('BD17'), ('BD18'), ('BD19'), ('BD2'), ('BD20'), ('BD21'), ('BD22'), ('BD3'), ('BD4'), ('BD5'), ('BD6')
     ) V(VALUE)
)   


SELECT * 
FROM tbl_ClientFile T
WHERE EXISTS ( SELECT TOP 1 1 FROM CTE WHERE T.CLNTPOST1 LIKE CTE.VALUE + '%')
Ananda Kumar Jayaraman
  • 380
  • 1
  • 6
  • Confirmed this worked. I used this for a set of 4,000 values, all was well. I'll leave another tip for something like that; I like to use the multiple cursor shortcut, alt+shift+ click somewhere on any line above or below (also done by using middle click and drag). This allows you to type the same character on many lines in the same position. For instance, all the above parantheses and quotes and commas (''), Paste in your values in rows, multiple cursor select, type once and all are edited. – Michael Wegter Jan 13 '23 at 16:34
6

One of possible solutions. Create a table Prefix(v varchar(4)) where you insert those values. Then a solution would be:

SELECT * 
FROM tbl_ClientFile cf
JOIN Prefix p on cf.CLNTPOST1 LIKE p.v + '%'

To exclude duplicates if some prefix includes some another prefix like BB1, BB10, BB15...:

SELECT DISTINCT cf.* 
FROM tbl_ClientFile cf
JOIN Prefix p on cf.CLNTPOST1 LIKE p.v + '%'
Giorgi Nakeuri
  • 35,155
  • 8
  • 47
  • 75
  • 1
    The join will create a product of 2 tables, which will return the unwanted duplicate rows. Should have use `EXISTS()` in where condition – Eric Jun 22 '15 at 09:18
  • @eric, do you really think that one `CLNTPOST1` can begin simultaneously from `BD14` and `BB10`? It would be true if I use `%` from both side of prefix.... – Giorgi Nakeuri Jun 22 '15 at 09:22
  • 1
    Let say `BB10` it can match to `BB1%` and `BB10%`, where `BB10` is a valid post code. – Eric Jun 22 '15 at 09:29
  • I didnt even see that thanks for spotting it. So i would be getting duplicates based on OR CLNTPOST1 LIKE ('BD1%') OR CLNTPOST1 LIKE ('BD10%') It will bring back BD1% and anything after and BD10% – RustyHamster Jun 22 '15 at 09:30
  • @Eric, yes this is correct. But I think it should match those prefixes if they match and show with appropriate prefixes. – Giorgi Nakeuri Jun 22 '15 at 09:35
  • @RustyHamster You would only getting duplicates for table join, not your original query in the question – Eric Jun 22 '15 at 09:35
4

Most of your likes are already covered by other likes. You can use likes with multiple values like this and get the same result:

SELECT * FROM tbl_ClientFile
WHERE 
  CLNTPOST1 LIKE ('B79%')
  OR CLNTPOST1 LIKE ('BB[12345689]%')
  OR CLNTPOST1 LIKE ('BD[1-6]%')

If you check for CLNTPOST1 is like BB1%, then you don't have to check for BB11% OR BB12%

t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92