CODEWITHSUNDEEP
sqloracle-databaseplsql
user3523768
user3523768

Reputation: 1

Date with in multiple Date Ranges

In Oracle, I have two tables, one table has a secvicepoint connection stamp history and other has reading data.

SRVCPOINT table

SPID    STARTDATE      STOPDATE       COMPANY
30      13-APR-2001   25-MAR-2002     UKPOWER
30      18-APR-2005   25-MAR-2006     UKPOWER


READING Table

READID SPID    READSTART      READSTOP
16      30     13-JAN-2001    12-APR-2001
23      30     13-APR-2001    25-JAN-2002
24      30     26-JAN-2001    24-MAR-2002
53      30     26-MAR-2002    25-MAR-2003
65      30     13-APR-2004    25-MAR-2004
74      30     20-APR-2005    25-MAR-2006
86      30     25-APR-2006    25-MAR-2007

I want to remove the reads which were taken during the period when the service was not given by UKPOWER, i.e. READID 16,53,65,86. I tried READSTART not between STARTDATE and STOPDATE and READTOP not between STARTDATE and STOPDATE, But it ignores only reads 16 and gives 53,65,86. I mean it compares date with only first date range not with others.

can any one help?

Upvotes: 0

Views: 50

Answers (1)

Serpiton
Serpiton

Reputation: 3684

If I understand well what you need the condition is something like

(ReadStart BETWEEN StartDate AND StopDate) 
AND (ReadStop BETWEEN StartDate AND StopDate)

For the row outside you can negate the same to this

NOT((ReadStart BETWEEN StartDate AND StopDate) 
    AND (ReadStop BETWEEN StartDate AND StopDate))

Or this

ReadStop < StartDate OR ReadStart > StopDate

Upvotes: 1

Related Questions