update statement based end date of previous row equaling start date of next row

Question

I have identified overlapping dates from trainers that trained users between a start date and end date. I now need to identify those USER_IDs where the end date for one of their trainers is the same as the start date for their next trainer. I need to flag them 'Y' and 'N'.

I run into a problem when there are trainers who have the same start date and end date as the prior trainers end date for the USER_ID.

UPDATE #SERVICE A
SET DIS_ADM_MATCH = CASE WHEN B.FROM_DATE = A.TO_DATE OR
                              A.FROM_DATE  = B.TO_DATE
                         THEN 'Y' ELSE 'N' END
FROM #SERVICE A
INNER JOIN #SERVICE B
ON A.USER_ID = B.USER_ID
AND A.TRAINER_ID <> B.TRAINER_ID;

(1) Tag your question with the database you are using. (2) Provide sample data and desired results. — Gordon Linoff, Jul 03 '19 at 01:20

Gordon Linoff · Answer 1 · 2019-07-03T11:03:55.337

0

I think you want:

update service
    set DIS_ADM_MATCH = (case when exists (select 1
                                           from service s2
                                           where s2.user_id = service.user_id and
                                                 s2.from_date = service.to_date
                                          )
                              then 'Y' else 'N'
                         end);

edited Jul 03 '19 at 11:03

answered Jul 03 '19 at 01:24

Gordon Linoff

1,242,037
58
646
786

Thanks for the feedback, unfortunately I am unable to use a subquery inside a conditional expression as it is not supported. – Jul 03 '19 at 03:02
@Leigh.Granger . . . I do not recall a limitation like that in Sybase IQ. Are you sure that is the real problem? (I fixed an issue with table aliases.) – Gordon Linoff Jul 03 '19 at 11:04

update statement based end date of previous row equaling start date of next row

1 Answers1