oracle - Select subsequence n days between date -


i want display records every 7 days between 2 date following

select *  user1.report1  project_name = 'f1'  , to_date(report_date, 'yyyy/mm/dd') >= to_date('2013/1/1', 'yyyy/mm/dd') , (to_date(report_date,'yyyy/mm/dd') between to_date('2013/1/1','yyyy/mm/dd') + 7                     , current_timestamp) order report_date, report_time desc 

tried refrence here not work out

input: started date (in case, 2013/1/1 in statement above)

expected output: records started date(2013/1/1) until current date (1 week 1 record, if started date fall on monday, next record monday , on...)

you use mod function every seventh day starting given start date e.g.

select * user1.report1  project_name = 'f1'  , to_date(report_date,'yyyy/mm/dd') between to_date('2013/01/01','yyyy/mm/dd')                                           , sysdate , mod(to_date(report_date,'yyyy/mm/dd') - to_date('2013/01/01','yyyy/mm/dd'), 7) = 0 order report_date        , report_time desc 

edit

the following query:

with report1   (select 'f1' project_name         , to_char(trunc(sysdate) - (level - 1), 'yyyy/mm/dd') report_date    dual    connect level <= 365) select * report1 project_name = 'f1' , to_date(report_date,'yyyy/mm/dd') between to_date('2014/01/01','yyyy/mm/dd')                                           , sysdate , mod(to_date(report_date,'yyyy/mm/dd') - to_date('2014/01/01','yyyy/mm/dd'), 7) = 0 order report_date 

gives following output:

project_name report_date ------------ ----------- f1           2014/01/01   f1           2014/01/08   f1           2014/01/15   f1           2014/01/22   f1           2014/01/29   f1           2014/02/05   f1           2014/02/12   f1           2014/02/19   f1           2014/02/26   f1           2014/03/05   f1           2014/03/12   f1           2014/03/19   f1           2014/03/26   f1           2014/04/02   f1           2014/04/09   f1           2014/04/16   f1           2014/04/23   f1           2014/04/30   f1           2014/05/07   f1           2014/05/14   f1           2014/05/21   f1           2014/05/28   f1           2014/06/04   f1           2014/06/11   f1           2014/06/18   f1           2014/06/25   f1           2014/07/02   f1           2014/07/09   f1           2014/07/16   f1           2014/07/23   f1           2014/07/30   f1           2014/08/06   f1           2014/08/13   f1           2014/08/20   f1           2014/08/27   f1           2014/09/03   f1           2014/09/10    37 rows selected  

i.e. input of record every day past year output record per week starting on specified start date , on same day of week date. appear have asked for. if not wish edit question give records forming input , output expect input; may need explain in words rules achieving output.


Comments

Popular posts from this blog

javascript - how to protect a flash video from refresh? -

android - Associate same looper with different threads -

visual studio 2010 - Connect to informix database windows form application -