|
Kopp
Starting Member
3 Posts |
 Posted - 2010-08-03 : 03:29:30
|
| Hi Folks,Need some help out here as I couldnt wrap my head around this issue.The issue at hand is more on the lines of finding out coverage GAPS and Plan Changes and Update a Date column based on that, more specifically, the thirdcolumn CoverageTimePeriod.We have 6 Columns in here and here are the different scenarios that are off the top of my head now. ------------------------------------------------------------------DECLARE @History TABLE (BAN INT, PlanStartDate Datetime, CoverageTimePeriod Datetime, PlanType varchar(10), BilledAmt Money, Lapse Int)Insert @HistorySelect 1,'20050101','20050101','A',120,0 UNION ALL Select 1,'20050201','20050101','A',120,0 UNION ALL Select 1,'20050301','20050101','A',120,0 UNION ALL Select 1,'20050401','20050101','A',120,0 UNION ALL Select 1,'20050501','20050101','A',120,0 UNION ALL Select 1,'20050601','20050101','A',120,0 UNION ALL Select 1,'20050701','20050101','A',120,0 UNION ALL Select 1,'20050801','20050101','A',120,0 UNION ALL Select 1,'20050901','20050101','A',120,0 UNION ALL Select 1,'20051001','20050101','A',120,0 UNION ALL Select 1,'20051101','20050101','A',120,0 UNION ALL Select 1,'20051201','20050101','A',120,0 UNION ALL Select 1,'20060101','20050101','A',120,0 UNION ALL Select 1,'20060201','20050101','A',120,0 UNION ALL Select 1,'20060301','20050101','A',120,0 UNION ALL Select 1,'20060401','20050101','A',120,0 UNION ALL Select 1,'20060501','20050101','A',120,0 UNION ALL Select 1,'20060601','20050101','A',120,0 UNION ALL Select 1,'20060701','20050101','A',120,0 UNION ALL Select 1,'20060801','20050101','A',120,0 UNION ALL Select 1,'20060901','20050101','A',120,0 UNION ALL Select 1,'20061001','20050101','A',120,0 UNION ALL Select 1,'20061101','20050101','A',120,0 UNION ALL Select 1,'20061201','20050101','A',120,0 UNION ALL Select 1,'20070101','20070101','A',105,1 UNION ALLSelect 2,'20050101','20050101','A',120,0 UNION ALL Select 2,'20050201','20050101','A',120,0 UNION ALL Select 2,'20050301','20050101','A',120,0 UNION ALL Select 2,'20050401','20050101','A',120,0 UNION ALL Select 2,'20050501','20050501','B',160,1 UNION ALL Select 3,'20050101','20050101','A',120,0 UNION ALL Select 3,'20050201','20050101','A',120,0 UNION ALL Select 3,'20050301','20050101','A',120,0 UNION ALL Select 3,'20050401','20050101','A',120,0 UNION ALL Select 3,'20050501','20050101','A',120,0 UNION ALL Select 3,'20050601','20050101','A',120,0 UNION ALL Select 3,'20050701','20050101','A',120,0 UNION ALL Select 3,'20050801','20050101','A',120,0 UNION ALL Select 3,'20050901','20050101','A',120,0 UNION ALL Select 3,'20051001','20050101','A',120,0 UNION ALL Select 3,'20051101','20050101','A',120,0 UNION ALL Select 3,'20051201','20050101','A',120,0 UNION ALL Select 3,'20060101','20050101','A',120,0 UNION ALL Select 3,'20060201','20050101','A',120,0 UNION ALL Select 3,'20060301','20050101','A',120,0 UNION ALL Select 3,'20060401','20050101','A',120,0 UNION ALL Select 3,'20060501','20050101','A',120,0 UNION ALL Select 3,'20060601','20050101','A',120,0 UNION ALL Select 3,'20060701','20050101','A',120,0 UNION ALL Select 3,'20060801','20050101','A',120,0 UNION ALL Select 3,'20060901','20050101','A',120,0 UNION ALL Select 3,'20061001','20050101','A',120,0 UNION ALL Select 3,'20061101','20050101','A',120,0 UNION ALL Select 3,'20061201','20050101','A',120,0 UNION ALL Select 3,'20070101','20070101','A',105,1 UNION ALL Select 3,'20070101','20070101','A',105,0 UNION ALL Select 3,'20070201','20070101','A',105,0 UNION ALL Select 3,'20070701','20070701','A',120,1 select * from @History------------------------------------------------------------------First Scenario, BAN = 1, here there is no Change in the SOC or Rate Plan and there is no slippage in the Coverage and they been with the Company for the full 24 months now.Here CustomerID, 1 have been with the Telecom Company for full 24 months now and hence to reward their loyalty, the company extends this loyalty program where in they get $15 discount on their previously paid plan rate. So their new plan rate is 105 and also more importantly, their CoverageTimePeriod is updated to 200701 and this cycle continues for another 1 year and it remains at that lowest level after thatSecond Scenario, BAN = 2, here there is change in the SOC or Rate Plan but there is no slippage in their coverage.Specifically, this customer, was with the Telecom Company for 4 months and in their fourth month they decided to change their plan from Plan A to Plan B and their new plan rate is $160. So here there is a Change of Plan and hence the CoverageTimePeriod is updated to 200505.Third Scenario, BAN = 3, here they were with the Telecom Company from 200501 to 200701 with no slippage in between this 2 years and hence their cost is going to drop to $105 and their CoverageTimePeriod is going to be reset to 200701. Now, for whatever reason, they decided to drop out of this service and after 5 months they choose to rejoin as well.Now, as there is a 5 month gap from the last change of coverage date, they do not get any discount and on top of it their CoverageTimePeriod is updated to 200707 and they have to wait for a whole year to get the discount.--------------------------------------------------------------------Now how do I write a sql for this is simply beyond me. Im just a BA and I have no clue as to go about this. Any help in this matter would be greatly honored.Also please feel free to ask me any questions and I tried to attach a spreadsheet for all reasons of clarity but it isnt letting me do that.Sincerely,Lal |
|
Update a column with Date