Geek asks and answers

Oracle Range and Subquery

I have a table that I'm trying to query for myself, and I'm not sure how to do this.

Table Name: Schedule

  • user_id
  • Startdate
  • Endate
  • sequencyID

Situation: I have a series of lines where user_id = 0. This is an open schedule that someone might require. If the task is scheduled, it is assigned a specific user ID. This is where the difficult part arises. I am trying to select a user and display a schedule time that does not overlap with the fact that they have already been accepted or scheduled.

That's what i still have

SELECT * 
  FROM schedule 
 WHERE user_id = 123456;

It gives me all ranges of times a person has already taken

SELECT * 
  FROM schedule 
 WHERE user_id = 0;

. , , - whos user_id = 0, startdate/enddate .

, -

SELECT * 
  FROM schedule 
 WHERE user_id = 0 
   AND (loop through schedule rows testing for 
           (startdate < loopstartdate and enddate < loopstartdate) || 
           (startdate > loopenddate)

. ? - , , , .

+3
2
SELECT a.*
FROM schedule a
WHERE user_id = 0
  AND NOT EXISTS (
    SELECT NULL
      FROM schedule b
      WHERE b.user_id = 123456
        AND b.start_date <= a.end_date
        AND b.end_date >= a.start_date
  )
+2

, - . , , .

SELECT a.* 
FROM SCHEDULE a,
(SELECT start_date, end_date FROM SCHEDULE WHERE user_id = 123456) b
WHERE a.user_id = 0
AND a.start_date BETWEEN b.start_date AND b.end_date
0

More articles:


All Articles