Date Overlap question 1

[boflexson]

Ok, here's the situation:

Table:
Class Seq EffDate RmvDate
2 1 1/1/2003 10/31/2003
3 1 1/1/2003 6/30/2003
3 2 7/1/2003 10/31/2003

If this account has class 2, they must also have class 3.
In the example above, the case is valid, they have class 3 for the entire time the have class 2.
In the example below, they dont have class 3 the entire time. I need to return records, when the case is like the one below? I am having a difficult time puting this query together.

Class Seq EffDate RmvDate
2 1 1/1/2003 10/31/2003
3 1 1/1/2003 6/15/2003
3 2 7/1/2003 10/31/2003

Any help would be appreciated.

-Adam T. Courtney
Stratagem, Inc.

http://www.Stratagemnet.com

 

[swampBoogie]

select * from t tq
where class = 2
and exists(
select * from t
where seq = tq.seq
and class = 3
and EffDate >= tq.EffDate
and RmvDate < tq.rmvDate)

May be a staring point at least

 

[boflexson]

nope, that would return me the record in the top case, which I don't want.

The case I want is when class 3 does not cover every day that class 2 is effective
-Adam T. Courtney

 

[sunila7]

Hi,

Does this help.... Idea is to check the no of days for class 2 and 3 and iam assuming that there is another fld accountno which is to displayed for criteria u specified.

select * from tbl t
where datediff(dd,effdate,rmvdate) <>
(select sum(datediff(dd,effdate,rmvdate))+1
from tbl t1 where t1.accountno=t.accountno and class = 3) and class =2


Hope it helps....

Sunil

 

[boflexson]

hhmmm, not really,

Here is another valid case

Class Seq EffDate RmvDate
2 1 1/1/2003 10/31/2003
3 1 8/1/2002 7/1/2003
3 2 7/1/2003 7/5/2003
3 3 7/5/2003 8/5/2003
3 4 8/5/2003 9/10/2003
3 5 9/11/2003 11/15/2003

Class 3 can be more days than class 2. The requirement is that class 3 be effective every day that class 2 is effective.




-Adam T. Courtney

 

[swampBoogie]

I understand your data now. How do you know that the row belongs to same account? (Lets' asume some account number.)

I see three cases,

the first class 3 starts after class 2
the last class 3 ends before class 2
there is a gap where class 3 is not covering

I haven't (obviously) tested this.

select * from t tq
where class = 2
and exists(
select * from t tq2
where accountNo = tq.accountNo
and class = 3
and Datediff(day,effDate,coalesce((select max(rmvDate)
from t where effDate < tq2.effDate
and accountNo = tq2.accountNo),effDate)) > 1)
or effDate < ( select min(effDate) from t
where accountNo = tq.accountNo
and class = 3 )
or rmvDate > ( select max(rmvDate) from t
where accountNo = tq.accountNo
and class = 3 )

 

[boflexson]

Yes there is an account number. I made a sample table to use for testing.


CREATE TABLE [dbo].[t] (
[AccountNo] [int] NOT NULL ,
[Class] [int] NOT NULL ,
[SeqNum] [int] NOT NULL ,
[EffDate] [datetime] NULL ,
[RmvDate] [datetime] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[t] WITH NOCHECK ADD
CONSTRAINT [PK_t] PRIMARY KEY CLUSTERED
(
[AccountNo],
[Class],
[SeqNum]
) ON [PRIMARY]
GO


When I put in the first case I gave ( first post) your script returns a record, it should not because that case is valid.


-Adam T. Courtney

 

[topher0303]

The requirements are not clearly defined. You did not mention if class 2 will only be one record per account and if so if class 2 could have gaps between sequences. However, here is some SQL that may do the trick. It does assume there is only one class 2 record for each account since that was the way your examples were. A stored procedure might be better suited for this situation. I do like a challenge, so here is a stab at it:

SELECT Class3.AccountNo
FROM (SELECT t1.AccountNo,
MIN(t1.effdate) AS MinEffDate,
MAX(t1.RmvDate) AS MaxRmvDate,
MIN(CASE WHEN t2.AccountNo IS NULL THEN NULL
WHEN DateDiff(d, t1.RmvDate, t2.EffDate) > 1 THEN t1.RmvDate
ELSE NULL
END) AS Gap
FROM t AS t1 LEFT JOIN t AS t2 ON t1.Class = t2.Class
AND t1.SeqNum = (t2.SeqNum - 1)
WHERE t1.Class = 3
GROUP BY t1.AccountNo) AS Class3
JOIN t AS t3 ON Class3.AccountNo = t3.AccountNo
WHERE t3.Class = 2
AND (Class3.MinEffDate > t3.EffDate
OR Class3.MaxRmvDate < t3.RmvDate
OR (Class3.Gap IS NOT NULL AND Class3.Gap < t3.RmvDate))


Chris.

 

[topher0303]

I left out a column in one of the joins. It should be:

SELECT Class3.AccountNo
FROM (SELECT t1.AccountNo,
MIN(t1.effdate) AS MinEffDate,
MAX(t1.RmvDate) AS MaxRmvDate,
MIN(CASE WHEN t2.AccountNo IS NULL THEN NULL
WHEN DateDiff(d, t1.RmvDate, t2.EffDate) > 1 THEN t1.RmvDate
ELSE NULL
END) AS Gap
FROM t AS t1 LEFT JOIN t AS t2 ON t1.AccountNo = t2.AccountNo
AND t1.Class = t2.Class
AND t1.SeqNum = (t2.SeqNum - 1)
WHERE t1.Class = 3
GROUP BY t1.AccountNo) AS Class3
JOIN t AS t3 ON Class3.AccountNo = t3.AccountNo
WHERE t3.Class = 2
AND (Class3.MinEffDate > t3.EffDate
OR Class3.MaxRmvDate < t3.RmvDate
OR (Class3.Gap IS NOT NULL AND Class3.Gap < t3.RmvDate))

 

[topher0303]

OK, I tweaked it a bit and removed the nested select statements. There was one scenario that my first SQL did not report on correctly. That is if a gap between class 3 seq numbers occurred before the Class 2 EffDate. This sql corrects that.

This SQL does assume that there is only one Class 2 record for each AccountNo, the Class 3 records start at SeqNum 1 and do not skip any seq numbers, and that the EffDate and RmvDate columns are not null.


SELECT t1.AccountNo
FROM t AS t1 LEFT JOIN t AS t2 ON t1.AccountNo = t2.AccountNo
AND t1.Class = t2.Class
AND t1.SeqNum = (t2.SeqNum - 1)
JOIN t AS t3 ON t1.AccountNo = t3.AccountNo
WHERE t1.Class = 3
AND t3.Class = 2
AND (CASE WHEN t1.SeqNum = 1 AND t1.EffDate > t3.EffDate THEN 1
WHEN t2.AccountNo IS NULL AND t1.RmvDate < t3.RmvDate THEN 1
WHEN t2.AccountNo IS NOT NULL
AND DateDiff(d, t1.RmvDate, t2.EffDate) > 1
AND t1.RmvDate >= t3.EffDate
AND t1.RmvDate < t3.RmvDate THEN 1
ELSE 0
END) = 1
GROUP BY t1.AccountNo

 

[boflexson]

topher0303,

Good work, however there is still a problem,
The SeqNum field doesn't really mean anything.
and the records may not be in date order so neatly corresponding to the seqnum.

The script so far works great, but here's a new case that doesn't work:
Acct class seqnum effdate rmvdate
1 2 5 1/1/2003 10/15/2003
1 3 1 1/1/2003 6/15/2003
1 3 3 6/18/2003 6/25/2003
1 3 4 6/25/2003 10/20/2003
1 3 8 6/16/2003 6/18/2003

Thanks for your help so far.
-Adam T. Courtney

 

[boflexson]

bump -Adam T. Courtney

 

[topher0303]

That new requirement just bumped this problem to a stored procedure in my opinion. I'm not saying it cannot be done with a SQL statement, but that SQL statement may not be very efficient.

 

[topher0303]

OK, I feel like I am on a goose chase, but try this one:

SELECT t1.AccountNo
FROM t AS t1 JOIN t AS t3 ON t1.AccountNo = t3.AccountNo
WHERE t1.Class = 3
AND t3.Class = 2
AND (CASE WHEN t1.EffDate = (SELECT MIN(t.EffDate)
FROM t
WHERE t.AccountNo = t1.AccountNo
AND t.Class = t1.Class)
AND t1.EffDate > t3.EffDate THEN 1
WHEN t1.RmvDate < t3.RmvDate
AND NOT EXISTS(SELECT 'x'
FROM t
WHERE t.EffDate >= t1.RmvDate) THEN 1
WHEN EXISTS(SELECT 'x'
FROM t
WHERE t.EffDate >= t1.RmvDate)
AND DateDiff(d, t1.RmvDate, (SELECT MIN(t.EffDate)
FROM t
WHERE t.AccountNo = t1.AccountNo
AND t.Class = t1.Class
AND t.EffDate >= t1.RmvDate)) > 1
AND t1.RmvDate >= t3.EffDate
AND t1.RmvDate < t3.RmvDate THEN 1
ELSE 0
END) = 1
GROUP BY t1.AccountNo

 

[topher0303]

Correction to the previous:

SELECT t1.AccountNo
FROM t AS t1 JOIN t AS t3 ON t1.AccountNo = t3.AccountNo
WHERE t1.Class = 3
AND t3.Class = 2
AND (CASE WHEN t1.EffDate = (SELECT MIN(t.EffDate)
FROM t
WHERE t.AccountNo = t1.AccountNo
AND t.Class = t1.Class)
AND t1.EffDate > t3.EffDate THEN 1
WHEN t1.RmvDate < t3.RmvDate
AND NOT EXISTS(SELECT 'x'
FROM t
WHERE t.AccountNo = t1.AccountNo
AND t.Class = t1.Class
AND t.EffDate >= t1.RmvDate) THEN 1
WHEN EXISTS(SELECT 'x'
FROM t
WHERE t.EffDate >= t1.RmvDate)
AND DateDiff(d, t1.RmvDate, (SELECT MIN(t.EffDate)
FROM t
WHERE t.AccountNo = t1.AccountNo
AND t.Class = t1.Class
AND t.EffDate >= t1.RmvDate)) > 1
AND t1.RmvDate >= t3.EffDate
AND t1.RmvDate < t3.RmvDate THEN 1
ELSE 0
END) = 1
GROUP BY t1.AccountNo

 

[topher0303]

OK, one last correction (I hope). I keep leaving out joins on the accountno and class. Ignore all previous posts:

SELECT t1.AccountNo
FROM t AS t1 JOIN t AS t3 ON t1.AccountNo = t3.AccountNo
WHERE t1.Class = 3
AND t3.Class = 2
AND (CASE WHEN t1.EffDate = (SELECT MIN(t.EffDate)
FROM t
WHERE t.AccountNo = t1.AccountNo
AND t.Class = t1.Class)
AND t1.EffDate > t3.EffDate THEN 1
WHEN t1.RmvDate < t3.RmvDate
AND NOT EXISTS(SELECT 'x'
FROM t
WHERE t.AccountNo = t1.AccountNo
AND t.Class = t1.Class
AND t.EffDate >= t1.RmvDate) THEN 1
WHEN EXISTS(SELECT 'x'
FROM t
WHERE t.AccountNo = t1.AccountNo
AND t.Class = t1.Class
AND t.EffDate >= t1.RmvDate)
AND DateDiff(d, t1.RmvDate, (SELECT MIN(t.EffDate)
FROM t
WHERE t.AccountNo = t1.AccountNo
AND t.Class = t1.Class
AND t.EffDate >= t1.RmvDate)) > 1
AND t1.RmvDate >= t3.EffDate
AND t1.RmvDate < t3.RmvDate THEN 1
ELSE 0
END) = 1
GROUP BY t1.AccountNo

 

[boflexson]

topher0303,

Getting better,
Works on all cases so far,
but here's a new case that is valid:

Acct Class SeqNum Eff Rmv
1 2 5 1/1/2003 10/15/2003
1 3 1 1/1/2003 6/15/2003
1 3 4 6/16/2003 6/18/2003
1 3 5 6/18/2003 6/25/2003
1 3 8 6/10/2003 10/20/2003

This is going to end up in a stored proc, no question. I think I might need a cursor and/or a table variable.

-Adam T. Courtney

 

[boflexson]

ok,
I have a solution, as much as I hate using cursors (speed)
here is a solution. and another requirement
(for one account I may be checking class 2,3 and also 5,6 So you'll see some of that built in.
topher0303, You get a star for all your good hard work.
Anyways here it is:

DECLARE @Work TABLE(AccountNo Int, Class Int, SeqNum Int, EffDate DateTime, RmvDate DateTime, MinEffDate DateTime Null, MaxRmvDate DateTime Null)

DECLARE @AccountNo Int, @Class Int, @SeqNum Int, @EffDate DateTime, @RmvDate DateTime,
@MinEffDate DateTime, @MaxRmvDate DateTime,
@TempEffDate DateTime, @TempRmvDate DateTime

DECLARE Left_Cursor CURSOR FOR
SELECT AccountNo, Class, SeqNum, EffDate, RmvDate, Null, Null FROM t WHERE Class = 2

OPEN Left_Cursor

FETCH NEXT FROM Left_Cursor
INTO @AccountNo, @Class, @SeqNum, @EffDate, @RmvDate, @MinEffDate, @MaxRmvDate

WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE Right_Cursor CURSOR FOR
SELECT EffDate, RmvDate FROM T WHERE AccountNo = @AccountNo AND Class = 3 AND RmvDate >= @EffDate
ORDER BY EffDate

OPEN Right_Cursor
FETCH NEXT FROM Right_Cursor
INTO @TempEffDate, @TempRmvDate

WHILE @@FETCH_STATUS = 0
BEGIN
IF (@MinEffDate IS Null)
BEGIN
SET @MinEffDate = @TempEffDate
SET @MaxRmvDate = @TempRmvDate
END
ELSE IF (@TempEffDate <= DATEADD(d, 1, @MaxRmvDate)) AND (@TempRmvDate > @MaxRmvDate)
BEGIN
SET @MaxRmvDate = @TempRmvDate
END

FETCH NEXT FROM Right_Cursor
INTO @TempEffDate, @TempRmvDate
END
CLOSE Right_Cursor
DEALLOCATE Right_Cursor

INSERT INTO @Work(AccountNo, Class, SeqNum, EffDate, RmvDate, MinEffDate, MaxRmvDate)
SELECT @AccountNo, @Class, @SeqNum, @EffDate, @RmvDate, @MinEffDate, @MaxRmvDate

FETCH NEXT FROM Left_Cursor
INTO @AccountNo, @Class, @SeqNum, @EffDate, @RmvDate, @MinEffDate, @MaxRmvDate
END

CLOSE Left_Cursor
DEALLOCATE Left_Cursor

SELECT * FROM @Work WHERE (MinEffDate > EffDate) OR (MaxRmvDate < RmvDate)



-Adam T. Courtney