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 |
|
anishdotnet
Starting Member
1 Post |
 Posted - 2011-08-03 : 03:13:52
|
I need to write a store procedure(MS SQL 2005 ) which returns 2 contacts (Name and email) based on certain conditions. Customer Id will be passed as SP input. Project has following tablesTables 1. CUSTOMER Stores customer details Coloums • CustomerID • CustomerName • CustomerType ( possible values are Gold, Silver, Platinum)2. CONTACT Stores customer contact details Coloums • ContactID • CustomerID (FK) • ContactName • Email • Designation (say possible values are from D1 to D10)Business RuleWhen Customer Type is "Gold" Customer. Choose Name and email based on following Designation priority 1. D1, D3, 2. D4, D6 3. D5, D7, D8Logic :a)if D1 exist select D1 and move to desination priority set 2, D1 NOT exist select D3 and Move to desination priority 2 D3 NOT exist move to desination priority set 2b) if D4 exist select D4 and move to desination priority set 3, D4 NOT exist select D6 and Move to desination priority set 3 D6 NOT exist move to desination priority set 3 Note : if we got an Name and email in first step (a) and this step (b) retun two contacts (name & email) . No need to iterate priority set 3c) if D5 exist select D5 , D5 NOT exist select D7 D7 NOT exist select D8 D8 NOT exist stop here .. Example:Suppose “ABC” is a “Gold” customer having contacts for following designation (There will be only one contact person for a particular designation) 1. D1 2. D3 3. D6 4. D7Then we will return email and name for peorson with follwing desinations 1. D1 2. D6When Customer Type is "Silver" Customer. Choose Name and email based on following Designation priority 1. D1, D9, 2. D6, D7 3. D8, D2, D10When Customer Type is "Platinum" Customer. Choose Name and email based on following Designation priority 1. D1, D10, 2. D8, D9 3. D3, D4, D2 Which would be the best and efficient way to solve this rule. All your thoughts are welcome.Thnaks in advance |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-03 : 04:53:46
|
| isnt it best to store this rules also in a table so that its easier to map based on customertype?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
|
|
|
|
|
Priority Contact finding