Stored Procedure to find emails
-
Priority ways email finding I have following tables in project Customer => Customer details => Coloums are Customer Name, Customer Type (Gold, Silver, Platinum) Contact => Can have multiple contacts to a customer => Coloums are Customer Id, Name, email, Designation (D1 to D10). we need to create an SP (MS SQL 2005 ) which returns priority 1 and priority 2 emails of customer (Customer Id will be passed as input) based on following criteria . When Customer Type is "Gold" Customer, choose email based on following Designation 1. D1, D3, 2 D4, D6 3, D5, D7, D8
Logic :
a). if D1 exist select D1 and move to set 2,
D1 NOT exist select D3 and Move to set 2
D3 NOT exist move to set 2
b) if D4 exist select D4 and move to set 3,
D4 NOT exist select D6 and Move to set 3
D6 NOT exist move to set 3
Note : if we got an email in first step and this step retun two emails. No need to iterate step 3
c) reapeat above logic with step 3 designations sequentiallyContinue searching until we get 2 mails.
For "Silver" Customer choose email based on following designation 1. D1, D2, 2 D6, D7 3, D8, D9, D10 For "Platinum" Customer choose email based on following designation 1. D1, D2, 2 D8, D9 3, D3, D4, D10 I am not sure i explained logic very well. Is there any algorith for this type of logic.
-
Priority ways email finding I have following tables in project Customer => Customer details => Coloums are Customer Name, Customer Type (Gold, Silver, Platinum) Contact => Can have multiple contacts to a customer => Coloums are Customer Id, Name, email, Designation (D1 to D10). we need to create an SP (MS SQL 2005 ) which returns priority 1 and priority 2 emails of customer (Customer Id will be passed as input) based on following criteria . When Customer Type is "Gold" Customer, choose email based on following Designation 1. D1, D3, 2 D4, D6 3, D5, D7, D8
Logic :
a). if D1 exist select D1 and move to set 2,
D1 NOT exist select D3 and Move to set 2
D3 NOT exist move to set 2
b) if D4 exist select D4 and move to set 3,
D4 NOT exist select D6 and Move to set 3
D6 NOT exist move to set 3
Note : if we got an email in first step and this step retun two emails. No need to iterate step 3
c) reapeat above logic with step 3 designations sequentiallyContinue searching until we get 2 mails.
For "Silver" Customer choose email based on following designation 1. D1, D2, 2 D6, D7 3, D8, D9, D10 For "Platinum" Customer choose email based on following designation 1. D1, D2, 2 D8, D9 3, D3, D4, D10 I am not sure i explained logic very well. Is there any algorith for this type of logic.
Create a bridge table between CustomerType and EmailDesignation and fill it with data that represents the relationship you specified above. You can then create a query that joins these three tables to get the data that you require. You also need to create a new table with email designations and their priority.
SELECT TOP 2 C.CustomerName, Co.CustomerType, E.Email, E.Designation
FROM Customer C
INNER JOIN CustomerEmailBridge B ON C.CustomerType = B.CustomerType
INNER JOIN Email E ON B.EmailDesignation = E.EmailDesignation
INNER JOIN EmailPriority P ON E.EmailDesignation = P.EmailDesignation AND B.CustomerType = P.CustomerType
WHERE (C.CustomerID = @CustomerID)
ORDER BY P.Prioritymodified on Tuesday, August 2, 2011 2:48 PM
-
Create a bridge table between CustomerType and EmailDesignation and fill it with data that represents the relationship you specified above. You can then create a query that joins these three tables to get the data that you require. You also need to create a new table with email designations and their priority.
SELECT TOP 2 C.CustomerName, Co.CustomerType, E.Email, E.Designation
FROM Customer C
INNER JOIN CustomerEmailBridge B ON C.CustomerType = B.CustomerType
INNER JOIN Email E ON B.EmailDesignation = E.EmailDesignation
INNER JOIN EmailPriority P ON E.EmailDesignation = P.EmailDesignation AND B.CustomerType = P.CustomerType
WHERE (C.CustomerID = @CustomerID)
ORDER BY P.Prioritymodified on Tuesday, August 2, 2011 2:48 PM
-
Shameel, Simple join will not work here. I need the output based on logic mentioned. It a priority based search. I am looking for best and optimized way to do it.
-
Hi shameel, this will not work . priority for same designation is different for different customer type
-
Hi shameel, this will not work . priority for same designation is different for different customer type