用于在列中添加日期的SQL查询

I have a tables named as deep_test in which there are column A,B,C and D. column A is primary key and B,C are date datatype columns and D is foreign key from other table.

我有一个名为deep_test的表,其中有A,B,C和D列。列A是主键,B,C是日期数据类型列,D是来自其他表的外键。

requirement :- I need to add all dates which are lying between +16 days of column C and -16 days of column B.

要求: - 我需要添加位于C列+16天和B列-16天之间的所有日期。

For example, now i have data like this in my table.

例如,现在我的表中有这样的数据。

A    B             C              D
------------------------------------
1    20.04.2014    21.04.2014     40
2    22.04.2014    22.04.2014     40
3    06.05.2014    10.05.2014     40
4    15.05.2014    30.05.2014     40
5    01.01.2014    10.01.2014     41
6    11.01.2014    25.01.2014     41
7    01.02.2014    10.02.2014     41
8    15.02.2014    20.02.2014     41
9    25.02.2014    26.02.2014     41

If you check +16 days of C column and -16 days of B column if any dates lies with in this range it should clubbed against column D which is foreign key and show me the below output.

如果检查C柱的+16天和B柱的-16天,如果任何日期位于此范围内,它应该对作为外键的D列进行晃动并向我显示以下输出。

I am using oracle 11i database.

我正在使用oracle 11i数据库。

output as :-

输出为: -

B            C           D
---------------------------
20.04.2014   30.05.2014  40
01.01.2014   26.02.2014  41

Query I am using is :-

我正在使用的查询是: -

SELECT X.* 
  FROM dummy X
 WHERE ( X.date_end BETWEEN (X.date_start - 16)
                        AND X.date_start
         OR date_start BETWEEN X.date_end
                           AND (X.date_end + 16) ) 
 GROUP BY X.D;

But it is not giving me exact output.

但它没有给我确切的输出。

2 个解决方案

#1

Guessing:

select min(B), max(C), D
from T
group by D

#2

So you want to get all adjacent date ranges where gaps up to 16 days are allowed.

因此,您希望获得所有相邻的日期范围,其中允许的间隔最长为16天。

Here is how to: Order the records by D and B and for every record look into the previous record if we are in an adjacent date range (with LAG). Mark every group change with a 1 (with CASE). Then go through the records again and have a running total of group changes (with SUM OVER), which gives you group numbers. Then get min start date and max end date per group.

以下是如何:按D和B对记录进行排序,如果我们处于相邻的日期范围(使用LAG),则每条记录都会查看上一条记录。将每个组更改标记为1(使用CASE)。然后再次浏览记录并运行总计的组更改(使用SUM OVER),它会为您提供组编号。然后获得每组的最小开始日期和最大结束日期。

B           C           D   group_change  range_group
01.01.2014  02.01.2014  10  1             1
04.01.2014  06.01.2014  10  0             1
31.01.2014  02.01.2014  10  1             2
10.02.2014  14.02.2014  10  0             2
01.01.2014  10.01.2014  20  1             3
20.01.2014  25.01.2014  20  0             3

B           C           D
01.01.2014  06.01.2014  10
31.01.2014  14.02.2014  10
01.01.2014  25.01.2014  20

select min(B) as start_date, max(C) as end_date, min(D) as D
from
(
  select B, C, D, sum(group_change) over (order by D, B) as range_group
  from
  (
    select B, C, D, 
      case 
        when B - lag(C) over (partition by D order by B) <= 16 then 0 
        else 1 
      end as group_change
    from mytable
  )
)
group by range_group
order by min(B), D;

Here is an SQL fiddle: http://www.sqlfiddle.com/#!4/a1144/10.

这是一个SQL小提琴:http://www.sqlfiddle.com/#!4 / a1144 / 10。

#1