Non sequential overlapping dates

[deemat]

I am attempting to prompt a report user, when two range of dates overlap, so corrections can be made. I am grouping on customers id then in the details, showing how long they were provided services. Sometimes the range overlaps and is not always sequential. I have tried the previous and next functions but they do not work when the overlap isn't sequential. For example

Customer XYZ
Dates of Services 05-07-2013 - 05-31-2013
06-01-2013 - 12-31-2013
08-01-2013 - 08-31-2013 [highlight #EF2929]Overlap Error[/highlight]
09-01-2013 - 02-28-2014
03-01-2014 - 08-31-2014

Both the 08-01-2013 - 08-31-2013 and 09-01-2013 - 02-28-2014 overlap into the 06-01-2013 - 12-31-2013 service dates but I can only get one of them to show using the previous function. Any suggestions. Thanks,

 

[deemat]

Sorry, forgot to add formula I am using to get the "Overlap Error"

If OnFirstRecord then
False
else
If {@CurrentStart} in {@PreviousStart} to {@PreviousEnd} then
True
Else
False

 

[SkipVought]

hi,

This is a complex kind of analysis.

Your comparison is between row and previous row.

Don't know what your source table looks like but...

You would need to compare each row in the group with every other row in the group, in toher words a cartesian join like this, where fields in table are
Customer
Svc_FROM
Svc_THRU

[pre]
select
Customer
, frm
, thr
, sum(logic)

from
(
SELECT
sv2.Cust
, sv2.Svc_FROM as frm
, sv2.Svc_THRU as thr
, sv1.Svc_FROM
, sv1.Svc_THRU
, case when sv1.Svc_FROM<=sv2.Svc_THRU and sv1.Svc_THRU>=sv2.Svc_FROM then 1 else 0 end as logic

FROM
tableA sv2
, tableA sv1

WHERE sv2.Cust = sv1.Cust
and sv2.Svc_THRU <>sv1.Svc_THRU
and sv2.Svc_FROM <>sv1.Svc_FROM
)
Group by
Cust
, frm
, thr
[/pre]

The result I get from your example (translated into a proper table)
[pre]
Cust frm thr logic_sum
XYZ 5/7/2013 5/31/2013 0
XYZ 6/1/2013 12/31/2013 2
XYZ 8/1/2013 8/31/2013 1
XYZ 9/1/2013 2/28/2014 1
XYZ 3/1/2014 8/31/2014 0
[/pre]


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

oops...

I posted a version where [highlight #FCE94F]Customer[/highlight] was not changed to [highlight #FCE94F]Cust[/highlight].

sorry

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]