Active on a given day??? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Active on a given day???

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources