CODEWITHSUNDEEP
sqloracle-database
sreekem bose
sreekem bose

Reputation: 471

Query to fetch effective from last 7 days

I have a table xx_asg with :

Effective_start_date Effective_end_date  Person_number
03-jan-2016           31-DEC-4712            12
20-Feb-2015           02-Jan-2016            12
28-Mar-2015           31-Dec-4712            10
07-dec-2016           31-Dec-4712            9

if i write :

select * from xx_asg
where trunc(sysdate) between effective_start_date and effective_end_date;

This gives me all latest records from this table.

Now If i write :

select * from xx_asg
    where trunc(sysdate) - 7 between effective_start_date and effective_end_date;

This gives me records effective from 01-Dec-2016. This query will not return any detail of person_number 9 as its effective from 07-dec-2016 and not 01-dec-2016.

What can i change in my query to fetch all records effective from 01-DEC-2016 / sysdate - 7 till today or 07-dec-2016

Upvotes: 0

Views: 7032

Answers (1)

hinotf
hinotf

Reputation: 1138

Try 

select * 
  from xx_asg
 where effective_start_date <= trunc(sysdate)
   and effective_end_date >= trunc(sysdate - 7);

Upvotes: 1

Related Questions