Hi Guys,
I have written a Stored Procedure having 3 nested while loops in it .This is Working fine and I am getting desired result as I have tested this on small set of data ,but while running on large set of records it taking huge of amount of time, might be due to multiple nested while looping .
Could someone please help me out to optimize this SP or eliminate some while looping without affecting its business logic, or any other solution?
Here is my Business Logic:
Loop Calculates Number of times customer has visited that particular city in given time span (DateProvided to Till Date).
Below is the Pseudo code, Sample Data and Sample results for your reference.
Pseudo code:
1. Select Customer in a table.
Eg. There are 5 customers in a table with Id 1, 2,3,4,5
2. Select Each City for Particular customer.
Eg . Customer whose Id is 1 travelling to 3 different cities let's say Mumbai, Delhi and Bangalore.
3. Now I have to calculate the Visiting status of those Customers based on Date Provided column to till date for each city.
a. If Customer visited particular city in one year from date provided to till date then M1 for each city.
b. If Customer visited particular city in two year from date provided to till date then M2 for each city.
C. If Customer visited particular city more than 3 year from date provided to till date then M3 for each city .
Sample Date:
Customer City DateProvided
Eg. 1 Mumbai 12/02/2011
Delhi 07/30/2008
Delhi 05/18/2009
Bangalore 04/13/2012
Expected Result:
Customer
City
Status
1
Mumbai
M2
Delhi
M3
Bangalore
M1