时间戳

首页 > 解决方案 > 根据最新交易过滤给定日期/时间的数据

sql - 根据最新交易过滤给定日期/时间的数据

问题描述

这是 unitlog 表内容的示例。它显示了执行了哪些交易，在哪些单位以及在哪个时间/日期执行。一旦转移到 WH 的单位可以由 WH 工作人员以任何理由退还。所以一个单位可以一天多次进出WH。每当一个单元进入/接受仓库时，这将是“WI”交易类型，在此之前总会有一个交易“VT”告诉该单元现在去仓库。

交易类型有：

VT : 指示去仓库 W01
WI : 单元被接受并已进入仓库 W01
V1 : 单元从一个位置进入另一个位置，或者你可以说从一个货架到另一个货架或从一个传送带到另一个传送带。（如果 WH_LOC 不是 RTI、CONV、CONVS，则无关紧要）
V2：仓库内的移动（无关紧要）
SP：将单元移出仓库。

单位状态类型：

FL : 单元未包装且不在仓库中。
WH : 单元现在被包裹并且可以去 WH。或者仅当 WH_loc 不是 (RTI, CONV, CONVS,RHS) 时已经在 WH 中

要求：我需要查看在给定日期有多少单位进入仓库 W01 并且从未退回，因此这意味着这些单位必须仍在 WH 中或进一步发货。

方法：我正在考虑在单元的最后一个事务上打一个复选标记，如果最后一个事务是 SP，那么该单元在外部，或者最后一个事务中的 wh_loc 是否在（RTI，CONV，CONVS，RHS）之内然后单位在外面。但我不知道如何实现这一目标。

在此处输入图像描述

我的输出表，如果我为所有进入仓库的单位生成报告，应该看起来像

在此处输入图像描述

样本数据是，

CREATE TABLE LOG( UNIT_ID INT, DATE_TRANSAC datetime, TYPE_TRANSAC varchar(10), UNIT_STAT varchar(10), WH_CODE varchar(10), WH_LOC varchar(10) );

insert into LOG (UNIT_ID, DATE_TRANSAC, TYPE_TRANSAC, UNIT_STAT, WH_CODE, WH_LOC) values ('1129463816', '20191117 14:30:08', 'V2', 'WH', 'W01', 'I002');

insert into LOG (UNIT_ID, DATE_TRANSAC, TYPE_TRANSAC, UNIT_STAT, WH_CODE, WH_LOC) values ('1193200552', '20191117 15:29:50', 'WI' ,'WH', 'W01' ,'H001');

insert into LOG (UNIT_ID, DATE_TRANSAC, TYPE_TRANSAC, UNIT_STAT, WH_CODE, WH_LOC) values ('1193200552', '20191117 14:49:29', 'VT' ,'WH' ,'W01' ,'CONV');

insert into LOG (UNIT_ID, DATE_TRANSAC, TYPE_TRANSAC, UNIT_STAT, WH_CODE, WH_LOC) values ('1193200552', '20191117 14:45:40', 'SP' ,'WH' ,'F01R' ,'*');

insert into LOG (UNIT_ID, DATE_TRANSAC, TYPE_TRANSAC, UNIT_STAT, WH_CODE, WH_LOC) values ('1193200552', '20191117 14:39:33', 'WI' ,'WH' ,'W01' ,'H001');

insert into LOG (UNIT_ID, DATE_TRANSAC, TYPE_TRANSAC, UNIT_STAT, WH_CODE, WH_LOC) values ('1193200456', '20191117 15:30:04', 'WI' ,'WH' ,'W01' ,'H001');

insert into LOG (UNIT_ID, DATE_TRANSAC, TYPE_TRANSAC, UNIT_STAT, WH_CODE, WH_LOC) values ('1193200346', '20191117 14:53:25', 'VI' ,'WH' ,'W01' ,'CONV');

insert into LOG (UNIT_ID, DATE_TRANSAC, TYPE_TRANSAC, UNIT_STAT, WH_CODE, WH_LOC) values ('1193200345', '20191117 15:29:21', 'SP' ,'WH' ,'W01' ,'RTI');

insert into LOG (UNIT_ID, DATE_TRANSAC, TYPE_TRANSAC, UNIT_STAT, WH_CODE, WH_LOC) values ('1193200344', '20191117 15:35:53', 'SP' ,'WH' ,'W01' ,'RTI');

insert into LOG (UNIT_ID, DATE_TRANSAC, TYPE_TRANSAC, UNIT_STAT, WH_CODE, WH_LOC) values ('1193200333', '20191117 14:23:06', 'WI' ,'WH' ,'W01' ,'F004');

insert into LOG (UNIT_ID, DATE_TRANSAC, TYPE_TRANSAC, UNIT_STAT, WH_CODE, WH_LOC) values ('1193200332', '20191118 14:23:06', 'WI' ,'WH' ,'W01' ,'F004');

insert into LOG (UNIT_ID, DATE_TRANSAC, TYPE_TRANSAC, UNIT_STAT, WH_CODE, WH_LOC) values ('1129463816', '20191118 16:30:29', 'SP' ,'WH' ,'W01' ,'RTI');

insert into LOG (UNIT_ID, DATE_TRANSAC, TYPE_TRANSAC, UNIT_STAT, WH_CODE, WH_LOC) values ('1193200345', '20191118 16:35:29', 'VT' ,'WH' ,'W01' ,'CONV');

insert into LOG (UNIT_ID, DATE_TRANSAC, TYPE_TRANSAC, UNIT_STAT, WH_CODE, WH_LOC) values ('1193200345', '20191118 16:36:34', 'WI' ,'WH' ,'W01' ,'D004');

SELECT * FROM LOG WHERE DATE_TRANSAC BETWEEN '20191117 13:00:00' and '20191117 17:00:00'

Oracle 命令（但不显示时间戳，仅显示日期）

CREATE TABLE UNITLOG(

UNIT_ID NUMBER,

DATE_TRANSAC DATE,

TYPE_TRANSAC varchar2(10),

UNIT_STAT varchar2(10),

WH_CODE varchar2(10),

WH_LOC varchar2(10)

);

insert into UNITLOG (UNIT_ID, DATE_TRANSAC, TYPE_TRANSAC, UNIT_STAT, WH_CODE, WH_LOC)

values ('1129463816',TO_DATE('17/10/2019 14:30:08', 'DD/MM/YYYY HH24:MI:SS') , 'V2', 'WH', 'W01', 'I002');

insert into UNITLOG (UNIT_ID, DATE_TRANSAC, TYPE_TRANSAC, UNIT_STAT, WH_CODE, WH_LOC)

values ('1193200552',TO_DATE('17/10/2019 15:29:50', 'DD/MM/YYYY HH24:MI:SS') , 'WI' ,'WH', 'W01' ,'H001');

insert into UNITLOG (UNIT_ID, DATE_TRANSAC, TYPE_TRANSAC, UNIT_STAT, WH_CODE, WH_LOC)

values ('1193200552',TO_DATE('17/10/2019 14:49:29', 'DD/MM/YYYY HH24:MI:SS') , 'VT' ,'WH' ,'W01' ,'CONV');

insert into UNITLOG (UNIT_ID, DATE_TRANSAC, TYPE_TRANSAC, UNIT_STAT, WH_CODE, WH_LOC)

values ('1193200552',TO_DATE('17/10/2019 14:45:40', 'DD/MM/YYYY HH24:MI:SS') , 'SP' ,'WH' ,'F01R' ,'*');

insert into UNITLOG (UNIT_ID, DATE_TRANSAC, TYPE_TRANSAC, UNIT_STAT, WH_CODE, WH_LOC)

values ('1193200552',TO_DATE('17/10/2019 14:39:33', 'DD/MM/YYYY HH24:MI:SS') , 'WI' ,'WH' ,'W01' ,'H001');

insert into UNITLOG (UNIT_ID, DATE_TRANSAC, TYPE_TRANSAC, UNIT_STAT, WH_CODE, WH_LOC)

values ('1193200456',TO_DATE('17/10/2019 15:30:04', 'DD/MM/YYYY HH24:MI:SS') , 'WI' ,'WH' ,'W01' ,'H001');

insert into UNITLOG (UNIT_ID, DATE_TRANSAC, TYPE_TRANSAC, UNIT_STAT, WH_CODE, WH_LOC)

values ('1193200346',TO_DATE('17/10/2019 14:53:25', 'DD/MM/YYYY HH24:MI:SS') , 'VI' ,'WH' ,'W01' ,'CONV');

insert into UNITLOG (UNIT_ID, DATE_TRANSAC, TYPE_TRANSAC, UNIT_STAT, WH_CODE, WH_LOC)

values ('1193200345',TO_DATE('17/10/2019 15:29:21', 'DD/MM/YYYY HH24:MI:SS') , 'SP' ,'WH' ,'W01' ,'RTI');

insert into UNITLOG (UNIT_ID, DATE_TRANSAC, TYPE_TRANSAC, UNIT_STAT, WH_CODE, WH_LOC)

values ('1193200344',TO_DATE('17/10/2019 15:35:53', 'DD/MM/YYYY HH24:MI:SS') , 'SP' ,'WH' ,'W01' ,'RTI');

insert into UNITLOG (UNIT_ID, DATE_TRANSAC, TYPE_TRANSAC, UNIT_STAT, WH_CODE, WH_LOC)

values ('1193200333',TO_DATE('17/10/2019 14:23:06', 'DD/MM/YYYY HH24:MI:SS') , 'WI' ,'WH' ,'W01' ,'F004');

insert into UNITLOG (UNIT_ID, DATE_TRANSAC, TYPE_TRANSAC, UNIT_STAT, WH_CODE, WH_LOC)

values ('1193200332',TO_DATE('18/10/2019 14:23:06', 'DD/MM/YYYY HH24:MI:SS') , 'WI' ,'WH' ,'W01' ,'F004');

insert into UNITLOG (UNIT_ID, DATE_TRANSAC, TYPE_TRANSAC, UNIT_STAT, WH_CODE, WH_LOC)

values ('1129463816',TO_DATE('18/10/2019 16:30:29', 'DD/MM/YYYY HH24:MI:SS') , 'SP' ,'WH' ,'W01' ,'RTI');

insert into UNITLOG (UNIT_ID, DATE_TRANSAC, TYPE_TRANSAC, UNIT_STAT, WH_CODE, WH_LOC)

values ('1193200345',TO_DATE('18/10/2019 16:35:29', 'DD/MM/YYYY HH24:MI:SS') , 'VT' ,'WH' ,'W01' ,'CONV');


insert into UNITLOG (UNIT_ID, DATE_TRANSAC, TYPE_TRANSAC, UNIT_STAT, WH_CODE, WH_LOC)

values ('1193200345',TO_DATE('18/10/2019 16:35:29', 'DD/MM/YYYY HH24:MI:SS') , 'WI' ,'WH' ,'W01' ,'D004');

这是样本数据的输出，我用相同的颜色代码突出显示了相同的单位。

这是样本数据的输出，我用相同的颜色代码突出显示了相同的单位。

这些是我需要的输出。

在此处输入图像描述

标签： sqloracle

解决方案

据我了解，您需要analytical function按以下方式使用：

Select * from
(Select t.*,
       Row_number() over (partition by unit_id, trunc(DATE_TRANSAC) order by DATE_TRANSAC desc) as rn
  From UNITLOG t
Where WH_CODE = 'W01' )
Where rn = 1
  And (TYPE_TRANSAC <> 'SP' 
        or coalesce(wh_loc,'RTI') not in ('RTI', 'CONV', 'CONVS','RHS')
      );

Coalesce如果它为空，则使用它，那么它不能包含在最终输出中。如果这不是可为空的列，那么您可以删除coalesce.

干杯！！

推荐阅读