Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Knives85
Starting Member
6 Posts |
 Posted - 2011-12-05 : 18:24:02
|
| HelloI've got a real problem here, I'm workng in SQL Server 2008 and I have to create a script that will take a customers postcode and find all the centres that can help them, based on the postcode areas the various centres cover.I have a table that contains all the centres, their centres ID and the postcode areas that they cover. There are a total of 1000 postcodes areas :( some centres will cover a massive area like all DN postcodes while others may cover very specific postcodes like DN14 4, or even DN14 7GH. I have to return all matching postcodes areas; some if a customers lives In DN14 7GH then all 3 centres would have to be displayed.You see the problem I'm having, I have a script that has all the customer data on it but I don't no how to create the joint, or the where clause that can pull back all possible postcodes matches from this table. Then there are 8 specific centres that have criteria, should as taking only customers between the ages of 25 to 30I have no script to post, I'm unsure how to process. I have a query that pull all m customer information that contains all the information that I need (name, address postcode, age etc! But I don't no how to possibly do the multiple matching.I was thinking a cursor but I've been told not to use one as theere very labour intensive and this query need to run quick, or possibly breaking the postcode down in its individual sections and matching or various combinations. I don't no and could reall use some helpThanks alot and sorry for the grammar.RegardGThanks Boys(and Girls) |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2011-12-05 : 19:48:16
|
| Everyone here speaks SQL. Help us to help you by showing us the tables that you have, some sample data and the desired results.=======================================Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-05 : 23:54:28
|
quote:
Originally posted by Knives85
HelloI've got a real problem here, I'm workng in SQL Server 2008 and I have to create a script that will take a customers postcode and find all the centres that can help them, based on the postcode areas the various centres cover.I have a table that contains all the centres, their centres ID and the postcode areas that they cover. There are a total of 1000 postcodes areas :( some centres will cover a massive area like all DN postcodes while others may cover very specific postcodes like DN14 4, or even DN14 7GH. I have to return all matching postcodes areas; some if a customers lives In DN14 7GH then all 3 centres would have to be displayed.You see the problem I'm having, I have a script that has all the customer data on it but I don't no how to create the joint, or the where clause that can pull back all possible postcodes matches from this table. Then there are 8 specific centres that have criteria, should as taking only customers between the ages of 25 to 30I have no script to post, I'm unsure how to process. I have a query that pull all m customer information that contains all the information that I need (name, address postcode, age etc! But I don't no how to possibly do the multiple matching.I was thinking a cursor but I've been told not to use one as theere very labour intensive and this query need to run quick, or possibly breaking the postcode down in its individual sections and matching or various combinations. I don't no and could reall use some helpThanks alot and sorry for the grammar.RegardGThanks Boys(and Girls)
I think what you require is a wildcard search query using LIKE. Can you tell what will be input from user? will it be centre or will it be based on postcode pattern?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Knives85
Starting Member
6 Posts |
Posted - 2011-12-06 : 03:08:38
|
| HelloI will put some example tables together a d post them. I don't think LIKE will be useable, I was thinking a combination of LIKE and % but that brings back everything. I was thinking of breaking the postcode into individual variables and then doing a massive case statement of all the possible variations in it, but unsure if this will work or exactly what the function to do this would be like, functions unfortunately are an area I have some problems Thanks for the help guys :).Thanks Boys(and Girls) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-06 : 03:56:32
|
| ok..that would make scenario more clear for us.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
matching multiple postcodes, please help