有表如下:
ticker as_of_date price
----------------------------
x1 2009-08-03 12.4
x1 2009-08-04 12.4
x1 2009-08-06 12.4
x1 2009-08-07 12.4
x1 2009-08-010 12.4
x1 2009-08-011 13.4
x1 2009-08-012 14.4
x1 2009-08-013 15.4
x1 2009-08-014 16.4
......
x2 2009-08-03 17.4
x2 2009-08-04 18.4
x2 2009-08-05 12.4
x2 2009-08-06 12.4
x2 2009-08-07 12.4
x2 2009-08-010 12.4
x2 2009-08-011 12.4
x2 2009-08-012 12.4
x2 2009-08-013 12.4
x2 2009-08-014 12.4
......
每天(工作日,不包括周六,日)会从上流系统导数据到这个表; 现在有一个需求就是:写一个sp或者sql检查这个表,看所有ticker哪个工作日没有数据。举个例子:ticker x1 从2009-08-03开始出现,则检查ticker x1是否从2009-08-03到今天这中间的每个工作日都有数据,比如如果x1在2009-08-05这天没有数据,则记录下来存入另外一个表。
ticker as_of_date price
----------------------------
x1 2009-08-03 12.4
x1 2009-08-04 12.4
x1 2009-08-06 12.4
x1 2009-08-07 12.4
x1 2009-08-010 12.4
x1 2009-08-011 13.4
x1 2009-08-012 14.4
x1 2009-08-013 15.4
x1 2009-08-014 16.4
......
x2 2009-08-03 17.4
x2 2009-08-04 18.4
x2 2009-08-05 12.4
x2 2009-08-06 12.4
x2 2009-08-07 12.4
x2 2009-08-010 12.4
x2 2009-08-011 12.4
x2 2009-08-012 12.4
x2 2009-08-013 12.4
x2 2009-08-014 12.4
......
每天(工作日,不包括周六,日)会从上流系统导数据到这个表; 现在有一个需求就是:写一个sp或者sql检查这个表,看所有ticker哪个工作日没有数据。举个例子:ticker x1 从2009-08-03开始出现,则检查ticker x1是否从2009-08-03到今天这中间的每个工作日都有数据,比如如果x1在2009-08-05这天没有数据,则记录下来存入另外一个表。
select t2.rq from
table1 t1,
(select to_char(date'2009-08-03'+rownum-1,'yyyy-mm-dd') rq from dual
connect by date'2009-08-03'+rownum<=sysdate+1)t2
where t1.as_of_date=t2.rq(+)
and t2.rq is null
insert into B --b为要插入的那个表
select t2.rq from
table1 t1,
(select to_char(date'2009-08-03'+rownum-1,'yyyy-mm-dd') rq from dual
connect by date'2009-08-03'+rownum <=to_date(:maxdate,'yyyy-mm-dd')+1)t2
where t1.as_of_date(+)=t2.rq
and t1.rq is null:maxdate表示table1里最大的日期
select to_char(date'2009-08-03'+rownum-1,'yyyy-mm-dd') as_of_date from dual connect by (date'2009-08-03'+rownum) <=to_date('2009-11-11','yyyy-mm-dd')+1我回去还要研究一下,周末不上班,下周把任务作完了揭帖。
select start_date+rownum-1 dt from dual connect by (start_date + rownum)<=end_date+1
来模拟日期序列。