Re: Tricky SQL extraction question

[Steve Richter <stephenrichter@xxxxxxxxx>]

since you are comparing rows in a table to other rows in the same table,
you might want to use WITH to first assign a row_number to each row within
Client:

with t1 as (
select  a.client, a.startDate, a.endDate,
      row_number() over ( partition by a.client order by a.startDate) rownum
from  client_date_table a
}
select   a.client, a.startDate, a.endDate, a.rownum
from    t1 a
where ....


On Wed, Dec 20, 2023 at 12:27 PM Dave <dfx1@xxxxxxxxxxxxxx> wrote:

> Hi,
>
> I never thought I would need to make a request and SQL could not help
> me, but I can't see how I could use it here.
> Any ideas how I can accomplish this? In the exemple, I need to extract
> clients C3 and C4. C3 because one of the periods overlap; C4 because the
> 2nd period does not immediately follow the 1st one.
>
> Client    StartDate         EndDate
> C1         2023-05-31
> C2         2023-01-01     2023-04-15
> C2         2023-04-16     2023-12-31
> C3         2023-02-28     2023-06-01
> C3         2023-04-01
> C4         2023-10-11     2023-11-13
> C4         2023-11-15
>
> Thanks!
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.