如何遍历PL / SQL中的日期范围 [英] How to iterate over a date range in PL/SQL
问题描述
我需要编写一份报表,根据每个记录的日期范围生成汇总总计。
I need to write a report that generates summary totals against a table with date ranges for each record.
table data:
option start_date end_date
opt1 6/12/2009 6/19/2009
opt1 6/3/2009 6/13/2009
opt2 6/5/2009 6/6/2009
我想要的基本上是这样:
What I want out is basically this:
date option count
6/1/2009 opt1 0
6/1/2009 opt2 0
6/2/2009 opt1 0
6/2/2009 opt2 0
6/3/2009 opt1 0
6/3/2009 opt2 1
我很难说明如何迭代日期范围。我相信这是一些简单的游标,可以为此创建,但我失去了。最好在PL / SQL
I am having a hard time figuring out how to iterate over a date range. I am sure this is some simple cursor that could be created for this but I am at a loss. Preferably in PL/SQL
UPDATE:
我最后使用示例这里完成我想做的事。这将创建一个生成日期表的函数。
I ended up using the example here to accomplish what I wanted to do. This creates a function that generates a table of dates.
推荐答案
您需要某种日历循环日期。我已经使用通过级别连接。然后,您可以将日历与您的数据(交叉连接,因为您想要一个行,即使没有选择的那一天):
You will need some sort of calendar to loop through a range of date. I have built one using the connect by level trick. You can then join the calendar with your data (cross join since you want a row even when there is no option for that day):
SQL> WITH calendar AS (
2 SELECT to_date(:begin_date, 'mm/dd/yyyy') + ROWNUM - 1 c_date
3 FROM dual
4 CONNECT BY LEVEL <= to_date(:end_date, 'mm/dd/yyyy')
- to_date(:begin_date, 'mm/dd/yyyy') + 1
5 )
6 SELECT c_date "date", d_option "option", COUNT(one_day)
7 FROM (SELECT c.c_date, d.d_option,
8 CASE
9 WHEN c.c_date BETWEEN d.start_date AND d.end_date THEN
10 1
11 END one_day
12 FROM DATA d, calendar c)
13 GROUP BY c_date, d_option
14 ORDER BY 1,2;
date option COUNT(ONE_DAY)
----------- ------ --------------
01/06/2009 opt1 0
01/06/2009 opt2 0
02/06/2009 opt1 0
02/06/2009 opt2 0
03/06/2009 opt1 1
03/06/2009 opt2 0
04/06/2009 opt1 1
04/06/2009 opt2 0
05/06/2009 opt1 1
05/06/2009 opt2 1
06/06/2009 opt1 1
06/06/2009 opt2 1
12 rows selected
这篇关于如何遍历PL / SQL中的日期范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!