| Author |
Topic |
|
jhoop2002
Starting Member
1 Post |
 Posted - 2014-10-09 : 15:02:37
|
I have a need to determine whether a client's policy was active or not on a given date. Unfortunately, I'm not sure how to do this.
Here is my data:
CustNo, CustomerName, PolEffDate, PolExpDate, LineOfBus, EffDate, Description
274, Smart, Susan, 2/6/14 12:00:00 AM, 2/6/15 12:00:00 AM, 11/15/14 12:00:00 AM, Reinstatement
274, Smart, Susan, 2/6/14 12:00:00 AM, 2/6/15 12:00:00 AM, 10/31/14 12:00:00 AM, Cancellation
274, Smart, Susan, 2/6/14 12:00:00 AM, 2/6/15 12:00:00 AM, 8/8/14 12:00:02 AM, Reinstatement
274, Smart, Susan, 2/6/14 12:00:00 AM, 2/6/15 12:00:00 AM, 8/8/14 12:00:01 AM, Cancellation
274, Smart, Susan, 2/6/14 12:00:00 AM, 2/6/15 12:00:00 AM, 2/6/14 12:00:00 AM, New
Here is the break down on when this policy is active.
from 02/06/12 -> 08/08/14 == Active
from 08/08/14 -> 10/31/14 == Active
from 10/31/14 -> 11/15/14 == Inactive
from 11/15/14 -> 02/06/15 == Active
If you notice the time increments on 08/08/14, that is to signify the order of the transaction - not the actual effective time. Effetive time is always 12:00 am.
Any help would be appreciated. |
|
|
Milira
Starting Member
1 Post |
Posted - 2014-10-09 : 15:47:40
|
select
'274' as CustNo, 'Smart, Susan' as CustomerName, '2/6/14 12:00:00 AM' as PolEffDate, '2/6/15 12:00:00 AM' as PolExpDate, '11/15/14 12:00:00 AM' as EffDate, 'Reinstatement' as Description
into #temp
insert #temp
select '274', 'Smart, Susan', '2/6/14 12:00:00 AM', '2/6/15 12:00:00 AM', '10/31/14 12:00:00 AM', 'Cancellation'
insert #temp
select '274', 'Smart, Susan', '2/6/14 12:00:00 AM', '2/6/15 12:00:00 AM', '8/8/14 12:00:02 AM', 'Reinstatement'
insert #temp
select '274', 'Smart, Susan', '2/6/14 12:00:00 AM', '2/6/15 12:00:00 AM', '8/8/14 12:00:01 AM', 'Cancellation'
insert #temp
select '274', 'Smart, Susan', '2/6/14 12:00:00 AM', '2/6/15 12:00:00 AM', '2/6/14 12:00:00 AM', 'New'
select case t.description
when 'New' then 'Active'
when 'Reinstatement' then 'Active'
when 'Cancellation' then 'Inactive'
end,
convert(date,t.EffDate) as PeriodStart, convert(date,isnull(t1.effdate,t.PolExpDate)) as PeriodEnd
from #temp t
inner join (
select CustNo, max(convert(datetime, EffDate)) as EffDate,CustomerName,PolEffDate,PolExpDate
from #temp
group by CustNo, convert(date, EffDate),CustomerName,PolEffDate,PolExpDate) a on a.CustNo = t.CustNo and convert(datetime, t.EffDate) = a.EffDate
left join #temp t1 on t1.CustNo = t.CustNo and t1.EffDate = (select MIN(convert(datetime,effDate)) from #temp where convert(datetime, EffDate) > convert(datetime, t.EffDate))
order by convert(datetime, t.EffDate) |
 |
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2014-10-24 : 16:15:12
|
This solution works with multiple customers, handles duplicates, is a lot faster (esp on larger resultsets) and should be easier to follow/maintain:
USE TempDB;
GO
IF OBJECT_ID('tempdb.dbo.#temp') IS NOT NULL DROP TABLE #temp;
CREATE TABLE #temp
(
CustNo INT,
CustomerName VARCHAR(50),
PolEffDate DATETIME,
PolExpDate DATETIME,
EffDate DATETIME,
Description VARCHAR(50)
);
INSERT #temp
VALUES ('274', 'Smart, Susan', '2/6/14 12:00:00 AM', '2/6/15 12:00:00 AM', '11/15/14 12:00:00 AM', 'Reinstatement')
, ('274', 'Smart, Susan', '2/6/14 12:00:00 AM', '2/6/15 12:00:00 AM', '10/31/14 12:00:00 AM', 'Cancellation')
, ('274', 'Smart, Susan', '2/6/14 12:00:00 AM', '2/6/15 12:00:00 AM', '8/8/14 12:00:02 AM', 'Reinstatement')
, ('274', 'Smart, Susan', '2/6/14 12:00:00 AM', '2/6/15 12:00:00 AM', '8/8/14 12:00:01 AM', 'Cancellation')
, ('274', 'Smart, Susan', '2/6/14 12:00:00 AM', '2/6/15 12:00:00 AM', '2/6/14 12:00:00 AM', 'New');
-- We only care about the last entry on each day
WITH cteCollapseToDate
AS
(
select t.CustNo,
t.CustomerName,
CONVERT(DATE,t.PolExpDate) AS PolExpDate,
CONVERT(DATE,t.EffDate) AS EffDateOnly,
CASE WHEN t.Description IN ('Reinstatement','New') THEN 1 ELSE 0 END AS isActive,
ROW_NUMBER() OVER(PARTITION BY t.CustNo,CONVERT(DATE,t.EffDate) ORDER BY t.EffDate DESC) AS RN
FROM #temp AS t
)
-- Filter out invalid rows and sequence the rest
, cteSequence
AS
(
SELECT t.CustNo,
t.CustomerName,
t.PolExpDate,
t.EffDateOnly,
t.isActive,
ROW_NUMBER() OVER(PARTITION BY t.CustNo ORDER BY t.EffDateOnly) AS Sequence
FROM cteCollapseToDate AS t
WHERE t.RN = 1
)
SELECT t1.CustNo,
t1.CustomerName,
t1.EffDateOnly AS PeriodStart,
COALESCE(t2.EffDateOnly,t1.PolExpDate) AS PeriodEnd,
t1.isActive
FROM cteSequence AS t1
LEFT JOIN cteSequence AS t2
ON t1.CustNo = t2.CustNo
AND t1.Sequence = t2.Sequence-1
ORDER BY t1.Sequence;
/*
CustNo CustomerName PeriodStart PeriodEnd isActive
274 Smart, Susan 2014-02-06 2014-08-08 1
274 Smart, Susan 2014-08-08 2014-10-31 1
274 Smart, Susan 2014-10-31 2014-11-15 0
274 Smart, Susan 2014-11-15 2015-02-06 1
*/
The reason it is faster is because it reduces the reads significantly. |
|
|
|
|
|
|
Active on a given day???