使用select语句查询日期范围

i want to know that is this possible with select statement query to generate date ranges (i.e start_date column and end_date column) for interval of 10 days up-to 62 days back to sysdate. like below

我想知道这是可能的,使用select语句查询生成日期范围(即start_date列和end_date列),间隔为10天,最多为62天,返回sysdate。如下

result required like

结果需要像

please help anyone

请帮助任何人

thanking you

2 个解决方案

#1

Addressing the original version of the question:

解决问题的原始版本:

Query 1 - Hierarchical Query:

查询1 - 分层查询:

SELECT TRUNC( SYSDATE ) - LEVEL * 10 AS start_date,
       TRUNC( SYSDATE ) - ( LEVEL - 1 ) * 10 AS end_date
FROM   DUAL
CONNECT BY LEVEL <= 5

Query 2 - Recursive Sub-query:

查询2 - 递归子查询:

WITH rsqfc ( end_date, lvl ) AS (
  SELECT CAST( TRUNC( SYSDATE ) AS DATE ), 1 FROM DUAL
UNION ALL
  SELECT end_date - 10, lvl + 1
  FROM   rsqfc
  WHERE  lvl < 5
)
SELECT end_date - 10 AS start_date,
       end_date
FROM   rsqfc;

Output:

(Both output the same)

(两者输出相同)

START_DATE          END_DATE          
------------------- -------------------
2016-05-28 00:00:00 2016-06-07 00:00:00 
2016-05-18 00:00:00 2016-05-28 00:00:00 
2016-05-08 00:00:00 2016-05-18 00:00:00 
2016-04-28 00:00:00 2016-05-08 00:00:00 
2016-04-18 00:00:00 2016-04-28 00:00:00 

Update: Addressing the edit - just change the interval and use GREATEST OR LEAST:

更新:解决编辑问题 - 只需更改间隔并使用最大或最小:

Query 1 - Hierarchical Query:

查询1 - 分层查询:

SELECT TRUNC( SYSDATE ) - LEAST( 62, LEVEL * 11 ) AS start_date,
       TRUNC( SYSDATE ) - ( LEVEL - 1 ) * 10 AS end_date
FROM   DUAL
CONNECT BY LEVEL <= 6

Query 1 - Recursive Sub-Query:

查询1 - 递归子查询:

WITH rsqfc ( end_date, lvl ) AS (
  SELECT CAST( TRUNC( SYSDATE ) AS DATE ), 1 FROM DUAL
UNION ALL
  SELECT end_date - 11, lvl + 1
  FROM   rsqfc
  WHERE  lvl < 6
)
SELECT GREATEST( TRUNC( SYSDATE ) - 62, end_date - 10 ) AS start_date,
       end_date
FROM   rsqfc;

Output:

START_DATE          END_DATE          
------------------- -------------------
2016-05-28 00:00:00 2016-06-08 00:00:00 
2016-05-17 00:00:00 2016-05-29 00:00:00 
2016-05-06 00:00:00 2016-05-19 00:00:00 
2016-04-25 00:00:00 2016-05-09 00:00:00 
2016-04-14 00:00:00 2016-04-29 00:00:00 
2016-04-07 00:00:00 2016-04-19 00:00:00 

#2

I see this is a little more tricky after seeing your edits. You would still use a recursive query to generate the date ranges but then since the last date range can actually be less than 10 days, you have to add a case statement to limit it to 62 days.

看到你的编辑后我发现这有点棘手。您仍然会使用递归查询来生成日期范围,但由于上一个日期范围实际上可能少于10天,您必须添加一个case语句将其限制为62天。

First part of the recursive query sets the END_DT = SYSDATE and the START_DT to SYSDATE - 10 days. The second part keeps subtracting from the dates until you hit 70 days. Then the select statement changes the last START_DT to 62 days.

递归查询的第一部分将END_DT = SYSDATE和START_DT设置为SYSDATE - 10天。第二部分将从日期中减去,直至达到70天。然后select语句将最后一个START_DT更改为62天。

with cte(START_DT, END_DT) 
as(
  select 
  trunc(SYSDATE) - 10  as START_DT, 
  trunc(SYSDATE)       as END_DT 
from dual
union all
  Select 
  START_DT - 11 as START_DT, 
  START_DT - 1  as END_DT
from cte
where START_DT - 11 >= trunc(SYSDATE) - 70
)    

select 
  case when trunc(START_DT) < trunc(SYSDATE) - 62 
       then trunc(SYSDATE) - 62 
       else START_DT
  end START_DT,
  END_DT
from cte
order by start_dt desc

OUTPUT:

START_DT             END_DT             
-------------------- --------------------
29-MAY-2016 00:00:00 08-JUN-2016 00:00:00 
18-MAY-2016 00:00:00 28-MAY-2016 00:00:00 
07-MAY-2016 00:00:00 17-MAY-2016 00:00:00 
26-APR-2016 00:00:00 06-MAY-2016 00:00:00 
15-APR-2016 00:00:00 25-APR-2016 00:00:00 
07-APR-2016 00:00:00 14-APR-2016 00:00:00