SQL / ORACLE Filter active records in month

3

I have a table that writes my records where one column writes the data_inicial and another column the data_final .

I need to generate a report where the user searches all the records that were between the period of eg 03/03/2017 to 03/31/2017.

What I needed is to get the records that even having started before that period occupied days in my 03 month. If I use for example data_inicial between 01/03/2017 and 31/03/2017 , my records that had start date 2/27/2017 and end date 01 / 04/2017 do not appear.

How could I do this?

    
asked by anonymous 10.04.2017 / 19:41

1 answer

2

The between will not allow because it is direct: Only records from 03/03/2017 until 03/31/2017 will be returned.

To consider ALSO the periods that contain month 03, you must include the OR clause indicating that you want the smaller ones and at the same time, greater than the 03 month.

All records that started at least before March 31 and ended later.

select * from teste
where dataInicial between '01/03/2017' and '31/03/2017'
      or (dataInicial < '01/03/2017' and dataFinal > '31/03/2017')

In this case, you will also consider those that have at least ended within month 03

select * from teste
where dataInicial between '01/03/2017' and '31/03/2017'
      or (dataInicial < '01/03/2017' and dataFinal >= '01/03/2017')
    
10.04.2017 / 19:56