| Author |
 Topic  |
|
|
slimt_slimt
Aged Yak Warrior
Switzerland
746 Posts |
 Posted - 06/04/2012 : 16:00:56
|
hi,
i have a insurance data, where i need to calculate prolonging dates. Prolonging dates are calculated based on overlapping. if date overlaps previous insurance date, recalulate it and prolonge it, else just add to starting day 1 year (1 year is a length of insurance premium).
here is a sample of data (wit DDL) and desired output which I want to query it.
DDL:
create table insurance
(id int identity(1,1)
,customerID int
,InsuranceID int
,InsuranceDate datetime
,CancelInsurance tinyint default(0) --(0 - No, 1 - Yes)
,CalculatedValidFrom datetime
,CalculatedValidTo datetime
)
insert into insurance (customerID, InsuranceID, InsuranceDate, CancelInsurance)
select 52,22312,'2010/10/19',0 union all
select 52,22313,'2011/10/21',1 union all
select 52,52369,'2012/02/14',0 union all
select 52,52353,'2012/02/16',0 union all
select 52,52380,'2012/02/17',0 union all
select 79,65322,'2012/05/05',0 union all
select 79,65323,'2012/05/06',0 union all
select 42,23523,'2012/05/05',0 union all
select 49,23532,'2010/02/25',0 union all
select 49,23533,'2012/02/26',0 union all
select 49,23534,'2012/02/27',0
-- desired output:
CustomerID | InsuranceID | InsuranceDate | CalculaterValidFrom | CalculatedValidTo
----------------------------------------------------------------------------------
52 | 22312 | 2010/10/19 | 2010/10/19 | 2011/10/19
52 | 52369 | 2012/02/14 | 2012/02/14 | 2013/02/14
52 | 52353 | 2012/02/16 | 2013/02/14 | 2014/02/14
52 | 52380 | 2012/02/17 | 2014/02/14 | 2015/02/14
79 | 65322 | 2012/05/05 | 2012/05/05 | 2013/05/05
79 | 65323 | 2012/05/06 | 2013/05/05 | 2014/05/05
42 | 23523 | 2012/05/05 | 2012/05/05 | 2013/05/05
49 | 23532 | 2010/02/25 | 2010/02/25 | 2011/02/25
49 | 23533 | 2012/02/26 | 2012/02/26 | 2013/02/26
49 | 23534 | 2012/02/27 | 2013/02/26 | 2014/02/26
cheers! |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 06/04/2012 : 16:36:35
|
;With Temp
AS
(
SELECT CustomerID , InsuranceID , InsuranceDate ,InsuranceDate AS CalculaterValidFrom,DATEADD(yy,1,InsuranceDate) AS CalculatedValidTo,ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY InsuranceID ) AS Rn
FROM table
)
SELECT t.CustomerID,
t.InsuranceID,
t.InsuranceDate,
CASE WHEN t1.Cnt > 0 THEN MaxTo ELSE CalculaterValidFrom END AS CalculaterValidFrom,
CASE WHEN t1.Cnt > 0 THEN DATEADD(yy,1,MaxTo) ELSE CalculaterValidTo END AS CalculaterValidTo
FROM Temp t
OUTER APPLY (SELECT COUNT(1) AS Cnt,MAX(CalculatedValidTo) AS MaxTo
FROM Temp
WHERE CustomerID =t.CustomerID
AND InsuranceID < t.InsuranceID
AND t.InsuranceDate BETWEEN CalculaterValidFrom AND CalculatedValidTo
)t1
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
slimt_slimt
Aged Yak Warrior
Switzerland
746 Posts |
Posted - 06/04/2012 : 16:54:38
|
Visakhm,
big thanks for solution.
runnint the query, it returns a false premium dates for: CustomerID: 52 on InsuranceID: 52380
it should be as (i hope i'm clear enough):
[Code]
CustomerID | InsuranceID | InsuranceDate | CalculaterValidFrom | CalculatedValidTo
----------------------------------------------------------------------------------
52 | 22312 | 2010/10/19 | 2010/10/19 | 2011/10/19
52 | 52369 | 2012/02/14 | 2012/02/14 | 2013/02/14
52 | 52353 | 2012/02/16 | 2013/02/14 | 2014/02/14
52 | 52380 | 2012/02/17 | 2014/02/14 | 2015/02/14
and i've added a where clause in CTE part (i forgot to mention it, cancellation are excluded):
[Code]
;With Temp
AS
(
SELECT CustomerID , InsuranceID , InsuranceDate ,InsuranceDate AS CalculaterValidFrom,DATEADD(yy,1,InsuranceDate) AS CalculatedValidTo,ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY InsuranceID ) AS Rn
FROM table
WHERE
CancelInsurance = 0
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 06/04/2012 : 21:39:58
|
this?
;With Temp
AS
(
SELECT CustomerID , InsuranceID , InsuranceDate ,InsuranceDate AS CalculaterValidFrom,DATEADD(yy,1,InsuranceDate) AS CalculatedValidTo,ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY InsuranceID ) AS Rn
FROM table
WHERE CancelInsurance = 0
)
SELECT t.CustomerID,
t.InsuranceID,
t.InsuranceDate,
CASE WHEN t.InsuranceDate BETWEEN t1.CalculaterValidFrom AND t1.CalculatedValidTo THEN t1.CalculatedValidTo ELSE t.CalculaterValidFrom END AS CalculaterValidFrom,
CASE WHEN t.InsuranceDate BETWEEN t1.CalculaterValidFrom AND t1.CalculatedValidTo THEN DATEADD(yy,1,t1.CalculatedValidTo) ELSE t.CalculaterValidTo END AS CalculaterValidTo
FROM Temp t
OUTER APPLY (SELECT TOP 1 CalculatedValidFrom,CalculatedValidTo
FROM Temp
WHERE CustomerID =t.CustomerID
AND InsuranceID < t.InsuranceID
ORDER BY InsuranceID DESC
)t1
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
slimt_slimt
Aged Yak Warrior
Switzerland
746 Posts |
Posted - 06/05/2012 : 04:18:25
|
Visakh,
still query does not return result as expected. for customerID 52, all insurance premium should be calculated cumulatively as shown in desired output. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 06/05/2012 : 10:01:21
|
Where have you shown insurance premium in output?
I can see only insurance details and valid from and To dates
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
slimt_slimt
Aged Yak Warrior
Switzerland
746 Posts |
Posted - 06/05/2012 : 14:29:48
|
Visakh,
sorry for any misunderstanding. only fields CalculaterValidFrom and CalculatedValidTo are being calculated. And these dates are being calculated in sense of prolongation of Insurance premium. If you buy three premiums in one week, i want to have for each premium calculated ValidFrom-ValidTo time frames.
In case of CustomerID = 52 only one date is coming out wrong in query.
instead of:
CustomerID InsuranceID CalculatedValidFrom CalculatedValidTo
52 52369 2012-02-14 2013-02-14
52 52353 2012-02-16 2013-02-16
52 52380 2013-02-14 2014-02-14
one should get:
CustomerID | InsuranceID |CalculatedValidFrom | CalculatedValidTo
-------------------------------------------------------------------
52 | 52369 | 2012/02/14 | 2013/02/14
52 | 52353 | 2013/02/14 | 2014/02/14
52 | 52380 | 2014/02/14 | 2015/02/14
i hope i made my self somehow more clear :)
thanks again. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 06/05/2012 : 14:42:43
|
sorry but doesnt my suggestion on 06/04/2012 : 16:36:35 give you that?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
Edited by - visakh16 on 06/05/2012 14:42:58 |
|
|
|
slimt_slimt
Aged Yak Warrior
Switzerland
746 Posts |
Posted - 06/05/2012 : 18:10:32
|
hi,
no it doesn't. it returns:
CustomerID InsuranceID InsuranceDate CalculatedValidFrom CalculatedValidTo
52 52369 2012-02-14 2012-02-14 2013-02-14
52 52353 2012-02-16 2012-02-16 2013-02-16
52 52380 2012-02-17 2013-02-16 2014-02-16
where you can see that two InsuranceID (52353,52380) are overlapping but they should be calculated in the future. so you have all the way to year 2015 not only to 2014. both insuranceID are for same Car, it's just that special offer was created and customer bought two one-year insurance premium in two days. :) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 06/05/2012 : 18:55:48
|
create table insurance
(id int identity(1,1)
,customerID int
,InsuranceID int
,InsuranceDate datetime
,CancelInsurance tinyint default(0) --(0 - No, 1 - Yes)
,CalculatedValidFrom datetime
,CalculatedValidTo datetime
)
insert into insurance (customerID, InsuranceID, InsuranceDate, CancelInsurance)
select 52,22312,'2010/10/19',0 union all
select 52,22313,'2011/10/21',1 union all
select 52,52369,'2012/02/14',0 union all
select 52,52353,'2012/02/16',0 union all
select 52,52380,'2012/02/17',0 union all
select 79,65322,'2012/05/05',0 union all
select 79,65323,'2012/05/06',0 union all
select 42,23523,'2012/05/05',0 union all
select 49,23532,'2010/02/25',0 union all
select 49,23533,'2012/02/26',0 union all
select 49,23534,'2012/02/27',0
;With Temp
AS
(
SELECT CustomerID , InsuranceID , InsuranceDate ,InsuranceDate AS CalculatedValidFrom,DATEADD(yy,1,InsuranceDate) AS CalculatedValidTo,ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY InsuranceDate ) AS Rn
FROM insurance
WHERE CancelInsurance = 0
),
Recur
AS
(
SELECT CustomerID , InsuranceID , InsuranceDate ,CalculatedValidFrom,CalculatedValidTo,Rn
FROM Temp
WHERE Rn=1
UNION ALL
SELECT t.CustomerID , t.InsuranceID , t.InsuranceDate ,
CASE WHEN t.CalculatedValidFrom < r.CalculatedValidTo THEN r.CalculatedValidTo ELSE t.CalculatedValidFrom END,
CASE WHEN t.CalculatedValidFrom < r.CalculatedValidTo THEN DATEADD(yy,1,r.CalculatedValidTo) ELSE t.CalculatedValidTo END,t.Rn
FROM Recur r
INNER JOIN Temp t
ON t.CustomerID = r.CustomerID
AND t.Rn = r.Rn + 1
)
SELECT CustomerID , InsuranceID , InsuranceDate ,CalculatedValidFrom,CalculatedValidTo
FROM Recur
ORDER BY CustomerID,Rn
CustomerID InsuranceID InsuranceDate CalculatedValidFrom CalculatedValidTo
42 23523 2012-05-05 00:00:00.000 2012-05-05 00:00:00.000 2013-05-05 00:00:00.000
49 23532 2010-02-25 00:00:00.000 2010-02-25 00:00:00.000 2011-02-25 00:00:00.000
49 23533 2012-02-26 00:00:00.000 2012-02-26 00:00:00.000 2013-02-26 00:00:00.000
49 23534 2012-02-27 00:00:00.000 2013-02-26 00:00:00.000 2014-02-26 00:00:00.000
52 22312 2010-10-19 00:00:00.000 2010-10-19 00:00:00.000 2011-10-19 00:00:00.000
52 52369 2012-02-14 00:00:00.000 2012-02-14 00:00:00.000 2013-02-14 00:00:00.000
52 52353 2012-02-16 00:00:00.000 2013-02-14 00:00:00.000 2014-02-14 00:00:00.000
52 52380 2012-02-17 00:00:00.000 2014-02-14 00:00:00.000 2015-02-14 00:00:00.000
79 65322 2012-05-05 00:00:00.000 2012-05-05 00:00:00.000 2013-05-05 00:00:00.000
79 65323 2012-05-06 00:00:00.000 2013-05-05 00:00:00.000 2014-05-05 00:00:00.000
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
slimt_slimt
Aged Yak Warrior
Switzerland
746 Posts |
Posted - 06/06/2012 : 04:11:12
|
Visakh,
yes. this is it. it calculates now okey.
thank oyu very much |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 06/07/2012 : 22:30:31
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
prolonging dates
