August 3, 2010 at 9:42 am
Hello Experts !
I know a little bit of sql and Im by no means an expert on this. I have a big problem at hand that I cant seem get a grip on.
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.
Ban PlanStartDate CoverageTimePeriod PlanType BilledAmt Lapse
1 200501 200501 A 120 0
1 200502 200501 A 120 0
1 200503 200501 A 120 0
1 … 200501 A 120 0
1 200512 200501 A 120 0
1 200601 200501 A 120 0
1 .. 200501 A 120 0
1 200701 200701 A 105 0
2 200501 200501 A 120 0
2 200502 200501 A 120 0
2 200503 200501 A 120 0
2 200504 200501 A 120 0
2 200505 200505 B 160 0
3 200501 200501 A 120 0
3 200502 200501 A 120 0
3 200503 200501 A 120 0
3 … 200501 A 120 0
3 200512 200501 A 120 0
3 200601 200501 A 120 0
3 .. 200501 A 120 0
3 200701 200701 A 120 0
3 200702 200701 A 105 0
3 200707 200707 A 120 5
------------------------------------------------------------------
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 that
Second 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. I have no clue as to go about this and when I asked people around, they told me that this cant be done as it needs to operate at row level.
Any help is greatly appreciated.
Thanks a bunch,
Lallitha Palanivel.
August 3, 2010 at 10:44 am
It seems to me that the issue is the result of a strange design. The only way to determine how long a customer has continuously been a customer is to track records back month to month?
That doesnt really make sense to me. Neither does writing a record every month whether things change in a customer's plan or not. Life would be way easier on you if there were datestamps on the main customer record for when they first signed up, and the last time they signed up.
Anyway, there's probably a query or two that can get this done (a calendar table could handle your loyalty thing, and the plan change one should be fairly simple), but people will likely need to see create table scripts for your tables, and have easy to work with test data and expected output to give you a workable answer.
Pseudo-code and pseudo-data is tough to understand as it is, but is also forces people to make assumptions about how you've built things, meaning that even if they do post an answer based on what you've given it may not work.
August 3, 2010 at 11:57 am
Nevyn,
I cant commend enough on your eagerness to help here.
Yes, it is a bit odd enough for me & when I tried to explain to my other fellow workers, they cant seem to get it at all. They think that this cant be achieved through Sql at all and I would let you guys decide on that.
Now, here are my replies to your question
It seems to me that the issue is the result of a strange design. The only way to determine how long a customer has continuously been a customer is to track records back month to month? -- That is correct and you have to check them every month to make sure that there is slipping in their coverage.
That doesnt really make sense to me. Neither does writing a record every month whether things change in a customer's plan or not -- I agree and we dont have to write anything to the third column as long as there are no plan changes nor any kind of slippage in their coverage as such. This can be left blank.
Life would be way easier on you if there were datestamps on the main customer record for when they first signed up, and the last time they signed up -- Yes I agree with you on this but I just dont have that luxury now.
Here is the sample data as well as updated column value for "CoverageTimePeriod".
Thanks a bunch,
Lallitha Palanivel.
DECLARE @History TABLE (BAN INT, PlanStartDate Datetime, CoverageTimePeriod Datetime, PlanType varchar(10), BilledAmt Money, Lapse Int)
Insert @History
Select 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 ALL
Select 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
August 3, 2010 at 12:16 pm
Thanks for the extra detail Lallitha. I have a few follow-up questions though:
-What are the inputs and expected output of the query? It looks like in your samples your last line for each customer is the expected output. So is this meant to be a stored procedure that takes the BAN , PlanStartDate, and PlanType as input, calculates the rate and plan date (and lapse) and then inserts the record? Or is the record already in there and you are updating those fields?
-Is there a table somewhere that holds the different plans that can be offered and their price? Same question for the 15 dollar loyalty discount. Or would you like the query to hard code the dollar values.
August 3, 2010 at 12:33 pm
Sure !
Thanks again for follow up.
Yes, you are right in that all the data is still there & we just need to update the CoverageTimePeriod Column. Im looking for the Update Statement to update the CoverageTimePeriod, BilledAmt($15 discount) and Lapse(Update to 1) columns and as such, initially, these Columns are empty / left as null.
There are no other tables in the schema this only deals with the History table.
August 3, 2010 at 12:59 pm
Is lapse 1 or 0 or the number of months lapsed? (Your two examples had two different things). Is lapse only set to a value the month the lapse is ended, or is it set to 1 for any person with a lapse in any of their previous x months?
August 3, 2010 at 1:11 pm
Nevyn,
Lapse is a column that is acting as a flag here & it need to be updated to 1 whenever the under the following conditions
- Update this flag to 1 when any customer changes their Plan or completed their initial 24 months
- Update this flag to 1 when customer has not completed 24 months and has slipped in their contract period of 24 months or when they rejoined their service with the Telecom company.
Hope I answered your question right.
Lallitha Palanivel.
August 3, 2010 at 3:03 pm
One more quick question:
When I am finding the new records to be updated, which of the following should be done:
1) Find all records where CoverageTimePeriod is null
OR
2) Find the most recent record for each customer.
OR
3) 1 and 2 (find the most recent with CoverageTimePeriod null for each customer)
OR
4) Find each customer record for the current month the query is being run for, and update it.
I'm really hoping your answer will be #4, but I wanted to check as your 3 sample cases had completely different dates.
August 3, 2010 at 3:44 pm
More or less, you can take the answer as 4.
What I need to tell you also is this that when ever this query runs, it is expected to update the CoverageTimePeriod , BilledAmt & Lapse.
August 3, 2010 at 4:16 pm
pommguest4 (8/3/2010)
when I tried to explain to my other fellow workers, they cant seem to get it at all. They think that this cant be achieved through Sql at all and I would let you guys decide on that.
You work with people with very little imagination. Anything having to do with data can be done in T-SQL.
I don't know exactly what you're trying to do (there seems to be a lot of business logic that affect conditions of when you do what) but it seems to me that the consecutive months of un-lapsed coverage is significant. Below is an example that would reduce the BilledAmt by $15 for anyone who has 25 months or more of un-lapsed coverage (no plan changes and no gaps in service.) Hopefully you can modify this to do whatever based on whatever conditions you want.
CREATE TABLE #Work
(RN INT PRIMARY KEY CLUSTERED
,BAN INT
,PlanStartDate Datetime
,PlanType varchar(10)
,Lapse Bit
,Consecutive Int)
;WITH cteHistory AS
(select BAN,
PlanStartDate,
PlanType,
RN = ROW_NUMBER() OVER(ORDER BY BAN, PlanStartDate)
from @History)
INSERT #Work (RN, BAN, PlanStartDate, PlanType, Lapse, Consecutive)
select cc.RN,
cc.BAN,
cc.PlanStartDate,
cc.PlanType,
Lapse =
CASE WHEN DATEDIFF(mm, ISNULL(cp.PlanStartDate,0), cc.PlanStartDate) > 1 THEN 1
ELSE CASE WHEN ISNULL(cp.PlanType, '') <> cc.PlanType THEN 1 END
END,
Null
from cteHistory cc
left join cteHistory cp
on cp.BAN = cc.BAN
and cp.RN = cc.RN - 1
order by cc.BAN, cc.RN
DECLARE @n_consec int = 0
UPDATE #Work
SET @n_consec = Consecutive = CASE Lapse
WHEN 1 THEN 1
ELSE @n_consec + 1 END
OPTION (MAXDOP 1)
UPDATE @History
SET BilledAmt = BilledAmt - 15
FROM @History h
JOIN #Work w
on w.BAN = h.BAN
and w.PlanStartDate = h.PlanStartDate
WHERE w.Consecutive >= 25
SELECT * FROM @History
ORDER BY BAN, PlanStartDate
DROP TABLE #Work
To read more about this technique (advised), read Jeff Moden's excellent article[/url].
August 3, 2010 at 6:16 pm
Actually on closer inspection Im not sure this needs much fancy consecutive counting, but there is a lot of business rules handling.
Thought I just about had this then something tripped me up:
Can a BAN have more than one record for a month?
You tripped me up with :
Select 3,'20070101','20070101','A',105,1 UNION ALL
Select 3,'20070101','20070101','A',105,0 UNION ALL
Was that a typo or does that guy have 2 records for Jan 2007?
Assuming it was a typo I THINK this works (but this business rules are complex so test carefully):
DECLARE @History TABLE (BAN INT, PlanStartDate Datetime, CoverageTimePeriod Datetime, PlanType varchar(10), BilledAmt Money, Lapse Int)
Insert @History
Select 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',NULL,'A',NULL,NULL UNION ALL
Select 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',NULL,'B',NULL,NULL 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,'20070201','20070101','A',105,0 UNION ALL
Select 3,'20070701',NULL,'A',NULL,NULL
Declare @testdate DATETIME
SELECT @testdate = '20070701'
UPDATE @History SET BilledAmt=CASE WHEN h.PlanType='A'
THEN
CASE WHEN Monthsin24 < 24
THEN 120
ELSE 105
END
ELSE
CASE WHEN Monthsin24 < 24
THEN 160
ELSE 145
END
END,
CoverageTimePeriod= CASE WHEN h.PlanType = h3.PlanType AND (Monthsin24 < 24 OR Monthsin25 =25) AND h3.BAN IS NOT NULL
THEN h3.CoverageTimePeriod
ELSE @testdate
END,
Lapse=CASE WHEN h3.BAN IS NULL
THEN 1
WHEN h.PlanType <> h3.PlanType
THEN 1
WHEN Monthsin24 = 24 AND Monthsin25 <> 25
THEN 1
ELSE 0
END
FROM @History h
INNER JOIN (SELECT BAN, Monthsin24=SUM(CASE WHEN PlanStartDate >= DATEADD(mm,-24,@testdate) AND PlanStartDate < @testdate
THEN 1
ELSE 0 END),
Monthsin25=SUM(CASE WHEN PlanStartDate >= DATEADD(mm,-25,@testdate) AND PlanStartDate < @testdate
THEN 1
ELSE 0 END)
FROM @History h2
GROUP BY BAN) AS derived1
ON h.BAN = derived1.BAN
LEFT OUTER JOIN (SELECT BAN, PlanStartDate, CoverageTimePeriod,PlanType,BilledAmt FROM @History WHERE PlanStartDate= DATEADD(mm,-1,@testdate)) AS h3 ON h.BAN = h3.BAN
WHERE h.PlanStartDate = @testdate
SELECT * FROM @History
I changed the date and tested for all 3 scenarios and it seems to work ok. But if you can have 2 records for 1 BAN for a month this method won't work (well, it can be done but would look even uglier)
August 3, 2010 at 7:04 pm
Nevyn,
At the outset, thanks a bunch !
Yes, it was a typo btw and I didnt mean to give you more trouble than I already have.
Let me test it out & get back to you on this.
regards,
Lallitha Palanivel.
August 4, 2010 at 11:14 am
Nevyn (8/3/2010)
Actually on closer inspection Im not sure this needs much fancy consecutive counting
In the very first post the OP said
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.
and
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 that
To meet these requirements consecutive counting would be necessary.
August 4, 2010 at 11:33 pm
Hi experts,
Actually I have some problems to recover existing database that I had lost during formatting the system so please help me how I can recovers my files and folders by using queries.
Regards,
August 4, 2010 at 11:43 pm
The easiest way to recover a lost database is to RESTORE it from previous backups.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply