基于最多两个日期的列值 [英] Column value based on maximum of two dates

问题描述

我试图根据两个日期的最大值获取列的值.

I am trying to get the value of a column based on the maximum of two dates.

我已经尝试过，以获得最多两个日期.

I have tried this, to get the maximum of two dates.

SELECT VIN,
       MAX(CASE WHEN DELIVERY_TYPE = 25 THEN TRANSACTION_DATE END) AS DELIVERY_DATE,
       MAX(CASE WHEN DELIVERY_TYPE <> 25 THEN TRANSACTION_DATE END) AS TRANSACTION_DATE
FROM TABLE
WHERE VIN IN ('XYZ')
GROUP BY VIN;

我明白了，这正是我需要的.但我需要交付代码.

I get this, which is what I need.But I need the delivery code.

 VIN   DELIVERY_DATE   OTHER_DELIVERY DATE
 XYZ      26-DEC-18           01-MAY-19

我如何得到这个

 VIN   DELIVERY_DATE   OTHER_DELIVERY DATE  Delivery_code
 XYZ      26-DEC-18           01-MAY-19        010

源数据是这样的:

 VIN  TRANSACTION_DATE  Delivery_code
 XYZ      26-DEC-18         025
 XYZ      01-MAY-19         010

新数据源

 VIN              TRANSACTION_DATE  DELIVERY_TYPE
xzy                   10-APR-19         025
xyz                     NULL           010
xzy                  12-JUL-19          035

推荐答案

可以使用keep:

SELECT VIN,
       MAX(CASE WHEN DELIVERY_TYPE = 25 THEN TRANSACTION_DATE END) AS DELIVERY_DATE,
       MAX(CASE WHEN DELIVERY_TYPE <> 25 THEN TRANSACTION_DATE END) AS TRANSACTION_DATE,
       MAX(DELIVERY_CODE) KEEP (DENSE_RANK FIRST ORDER BY TRANSACTION_DATE DESC NULLS LAST) as DELIVERY_CODE
FROM TABLE
WHERE VIN IN ('XYZ')
GROUP BY VIN;

这篇关于基于最多两个日期的列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！