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
Post a Comment