Re: Tricky SQL extraction question

Dave, this is not quite the answer you're looking for but I think you might
get an idea from the code.

I have pricing tables with effective and expiration dates, I'm receiving
data with an effective date but no cancel date. Once the data is loaded, I
run this routine to reverse-engineer the cancel date. You're trying to do
a similar task: if the starting date is not the day after the previous
date, some action must be taken. The trick is to look at the same file
(GPP060) from two different perspectives (a.GPP060 and b.GPP060).

For your requirement, I'd read the dates in descending order. For 11-15,
you want no previous record or one with an end date of 11-14. I think.

Good luck!

UPDATE gpp060 a
SET a.accan = IFNULL((SELECT b.aceff - 1 DAY --This value becomes the
cancellation date
FROM gpp060 b
WHERE a.acco = b.acco
AND a.acdiv = b.acdiv
AND a.acaccess = b.acaccess
AND a.acmode = b.acmode
AND a.accust = b.accust
AND a.aceff < b.aceff
AND a.accan = '0001-01-01' FETCH FIRST ROW
ONLY),'0001-01-01')
WHERE a.accan = '0001-01-01'
ORDER BY a.acco,
a.acdiv,
a.acaccess,
a.acmode,
a.accust,
a.aceff DESC
WITH NC



On Wed, Dec 20, 2023 at 9:27 AM 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.