oracle sql比较不到两天的日期

sy5wg1nm 于 2021-07-29 发布在 Java

关注(0)|答案(2)|浏览(383)

嗨，伙计们，我需要你们的支持如何执行这个逻辑。我现在口吃，我真的不知道如何进一步处理。
目标：比较 ref_num, entry_date and status=1 具有 ref_num, change_status_date, status 0 (always the last two rows) 比较一下，如果这些日期 change_status_date 以及 entry_date 小于2天，则从更新状态值 status=1 至 status=2 ，否则如果天数超过3天，则更改为 status=0 你知道如何执行正确的选择sql和更新sql吗？

+--------------+-----------------------+---------------------+----------+
| ref_num      | entry_date            |  change_status_date | status
+--------------+-----------------------+---------------------+----------+
| x326585      | 28/04/2020 16:54:14   |                     |    1     |
| x326585      | 25/04/2020 13:14:00   | 27/04/2020 23:44:00 |    0     |
| x326585      | 20/04/2020 11:15:02   | 20/04/2020 23:52:01 |    0     |

| A142585      | 28/04/2020 16:55:14   |                     |    1     |
| A142585      | 26/04/2020 11:54:04   | 27/04/2020 22:54:51 |    0     | 
| A142585      | 24/04/2020 10:44:14   | 25/04/2020 13:17:23 |    0     |

| B188532      | 29/04/2020 11:34:41   |                     |    1     |
| B188532      | 14/04/2020 11:44:24   | 15/05/2020 23:11:10 |    0     |
| B188532      | 11/04/2020 08:34:10   | 13/05/2020 11:44:41 |    0     |
+--------------+-----------------------+---------------------+----------+

END RESULTS:  
+--------------+-----------------------+---------------------+----------+
| ref_num      | entry_date            |  change_status_date | status
+--------------+-----------------------+---------------------+----------+
| x326585      | 28/04/2020 16:54:14   | 27/07/2020 23:47:31 |    2     |  is less than 3 days (28/04/2020 16:54:14 - 27/04/2020 23:44:00) -> status 2
| x326585      | 25/04/2020 13:14:00   | 27/04/2020 23:44:00 |    0     |
| x326585      | 20/04/2020 11:15:02   | 20/04/2020 23:52:01 |    0     |

| A142585      | 28/04/2020 16:35:58   | 27/07/2020 23:47:31 |    2     |  is less than 3 days (28/04/2020 16:35:58 - 27/04/2020 22:54:51) -> status 2
| A142585      | 26/04/2020 11:54:04   | 27/04/2020 22:54:51 |    0     | 
| A142585      | 24/04/2020 10:44:14   | 25/04/2020 13:17:23 |    0     |

| B188532      | 29/04/2020 11:34:41   | 27/07/2020 23:47:31 |    0     |  is more than 3 days (29/04/2020 11:34:41  - 15/05/2020 23:11:10) -> status 0
| B188532      | 14/04/2020 11:44:24   | 15/05/2020 23:11:10 |    0     |
| B188532      | 11/04/2020 08:34:10   | 13/05/2020 11:44:41 |    0     |
+--------------+-----------------------+---------------------+----------+

选择x.ref\u num、x.entry\u date、x.change\u status\u date、x.status from kl\u table x
感谢您的支持和建议

sql oracle

来源：https://stackoverflow.com/questions/63124676/oracle-sql-compare-dates-less-than-two-days-old

2条答案

按热度按时间

tkclm6bt1#

我就是这样理解这个问题的。
样本数据：

SQL> with test (ref_num, entry_date, change_status_date, status) as
  2    (select 'x3', to_date('28.04.2020 16:54', 'dd.mm.yyyy hh24:mi'), null                                             , 1 from dual union all
  3     select 'x3', to_date('25.04.2020 13:14', 'dd.mm.yyyy hh24:mi'), to_date('27.04.2020 23:44', 'dd.mm.yyyy hh24:mi'), 0 from dual union all
  4     select 'x3', to_date('20.04.2020 11:15', 'dd.mm.yyyy hh24:mi'), to_date('20.04.2020 23:52', 'dd.mm.yyyy hh24:mi'), 0 from dual union all
  5     --
  6     select 'b1', to_date('29.04.2020 11:34', 'dd.mm.yyyy hh24:mi'), null                                             , 1 from dual union all
  7     select 'b1', to_date('14.04.2020 11:44', 'dd.mm.yyyy hh24:mi'), to_date('15.05.2020 23:11', 'dd.mm.yyyy hh24:mi'), 0 from dual union all
  8     select 'b1', to_date('11.04.2020 08:34', 'dd.mm.yyyy hh24:mi'), to_date('13.05.2020 11:44', 'dd.mm.yyyy hh24:mi'), 0 from dual
  9    ),

最大值 change_status_date 为了这个 ref_num 谁的 status = 0 ; 它将被比作 entry_date ```
10 temp as
11 (select
12 a.ref_num,
13 a.entry_date,
14 a.change_status_date,
15 --
16 (select max(b.change_status_date)
17 from test b
18 where b.ref_num = a.ref_num
19 and b.status = 0
20 ) compare_change_status_date,
21 a.status
22 from test a
23 )

最后：我想 `change_status_date` （那是 `NULL` )应替换为 `sysdate` . 这些日期之间的差异应该是 `ABS` 消除负数。

24 select
25 t.ref_num,
26 t.entry_date,
27 --
28 nvl(t.change_status_date, sysdate) change_status_date,
29 --
30 case when t.status = 1 then
31 case when abs(t.entry_date - t.compare_change_status_date) < 2 then 2
32 when abs(t.entry_date - t.compare_change_status_date) > 3 then 0
33 end
34 else t.status
35 end status
36 from temp t
37 order by t.ref_num desc, t.entry_date desc;

RE ENTRY_DATE CHANGE_STATUS_DA STATUS

x3 28.04.2020 16:54 28.07.2020 08:21 2
x3 25.04.2020 13:14 27.04.2020 23:44 0
x3 20.04.2020 11:15 20.04.2020 23:52 0
b1 29.04.2020 11:34 28.07.2020 08:21 0
b1 14.04.2020 11:44 15.05.2020 23:11 0
b1 11.04.2020 08:34 13.05.2020 11:44 0

6 rows selected.

SQL>

如果要更新 `status = 1` ，上面发布的代码可以重用，例如。 `MERGE` :

merge into test a
using (with temp
as (select a.ref_num,
a.entry_date,
a.change_status_date,
--
(select max (b.change_status_date)
from test b
where b.ref_num = a.ref_num
and b.status = 0)
compare_change_status_date,
a.status
from test a)
select t.ref_num,
t.entry_date,
--
nvl (t.change_status_date, sysdate) change_status_date,
--
case
when t.status = 1
then
case
when abs (
t.entry_date - t.compare_change_status_date) <
2
then
2
when abs (
t.entry_date
- t.compare_change_status_date) > 3
then
0
end
else
t.status
end
status
from temp t) x
on ( a.ref_num = x.ref_num
and a.entry_date = x.entry_date)
when matched
then
update set a.status = x.status
where a.status = 1;

赞(0）回复(0）举报 2021-07-29

zhte4eai2#

您的解决方案如下所示。

update [tablename] set status=2 where DATEDIFF(day, [tablename].entry_date, [tablename].change_status_date) < 2 

update [tablename] set status=0 where DATEDIFF(day, [tablename].entry_date, [tablename].change_status_date) > 3

谢谢

赞(0）回复(0）举报 2021-07-29

我来回答

oracle sql比较不到两天的日期

2条答案

相关问题

热门标签

最新问答