如何在SQL Query中将另一列的最大日期输入到新列？

I have a table with the order id and the date columns, I need to get the max of enddate column in to a new column . Sample below

我有一个包含订单ID和日期列的表，我需要将enddate列的最大值添加到新列中。以下示例

ID  OrderId StartDate   begindate   enddate
1   2345    06/07/2011  20/08/2011  12/07/2012
2   2345    05/07/2012  11/07/2012  13/01/2014
3   2345    21/05/2014  28/10/2013  27/05/2015
4   2345    23/05/2015  03/06/2015  02/06/2017
5   2345    27/03/2017  20/06/2017  NULL

So what i am trying to get my Output is :

那么我想要的是我的输出是：

REQUIRED OutPUT

需要OutPUT

ID  OrderId StartDate   begindate   enddate      LastEnddate
1   2345    06/07/2011  20/08/2011  12/07/2012     NULL
2   2345    05/07/2012  11/07/2012  13/01/2014     NULL
3   2345    21/05/2014  28/10/2013  27/05/2015      NULL
4   2345    23/05/2015  03/06/2015  **02/06/2017**  NULL
5   2345    27/03/2017  20/06/2017  NULL           **02/06/2017**

Max EndDate should be in Lastenddate column where Enddate is NULL

Max EndDate应位于Endate为NULL的Lastenddate列中

I have tried creating temp table and applying dense rank but still i am missing something, Can you please help ?

我试过创建临时表并应用密集排名，但我仍然缺少一些东西，你能帮忙吗？

I am writing my query from temptable as

我正在从temptable编写我的查询

SELECT id, orderid
     , CASE WHEN MAX(CASE WHEN Lasttenddate IS NULL THEN 1 ELSE 0 END) = 0 THEN MAX(enddate) END
FROM #Test
WHERE
 orderid = 2345
GROUP BY id, orderid

Thanks in advance.

提前致谢。

4 个解决方案

#1

Use Cte , using Row_number. Max date is calculated using max rownumber. date output is in your required string format.

使用Cte，使用Row_number。最大日期使用max rownumber计算。日期输出采用您所需的字符串格式。

DECLARE @table TABLE (id INT, orderid INT, startdate VARCHAR(100), begindate VARCHAR(100), enddate VARCHAR(100))

INSERT INTO @table (ID, OrderId, StartDate, begindate, enddate)
SELECT 1, 2345, '06/07/2011', '20/08/2011', '12/07/2012'

UNION ALL

SELECT 2, 2345, '05/07/2012', '11/07/2012', '13/01/2014'

UNION ALL

SELECT 3, 2345, '21/05/2014', '28/10/2013', '27/05/2015'

UNION ALL

SELECT 4, 2345, '23/05/2015', '03/06/2015', '02/06/2017'

UNION ALL

SELECT 5, 2345, '27/03/2017', '20/06/2017', NULL;

WITH DATE
AS (
    SELECT *, ROW_NUMBER() OVER (
            PARTITION BY orderid ORDER BY enddate DESC
            ) rn
    FROM @table
    WHERE enddate IS NOT NULL
    )
SELECT t.id, t.orderid, t.startdate, t.enddate, CASE WHEN t.enddate IS NOT NULL THEN NULL ELSE convert(VARCHAR(10), (
                    SELECT max(convert(DATETIME, enddate, 103))
                    FROM DATE
                    ), 103) END DATE
FROM @table t
LEFT JOIN DATE d
    ON d.id = t.id
        AND t.orderid = d.orderid

#2

Try this:

尝试这个：

SELECT id, orderid,enddate, CASE WHEN enddate is null 
THEN MAX(enddate) over (partition by orderid)  else null END as LastEnddate
FROM #Test
WHERE orderid = 2345

Output:

输出：

id  orderid enddate LastEnddate
1   2345    2012-07-12  NULL
2   2345    2014-01-13  NULL
3   2345    2015-05-27  NULL
4   2345    2017-06-02  NULL
5   2345    NULL        2017-06-02

#3

You can use CROSS APPLY to get the MAX(enddate) and combine it with your query like following. If you want you can also use CASE with ROW_NUMBER depending on the volume of data. CROSS APPLY is better when there many rows per partition.

您可以使用CROSS APPLY来获取MAX（enddate）并将其与您的查询结合起来，如下所示。如果需要，还可以根据数据量使用CASE和ROW_NUMBER。每个分区有多行时，CROSS APPLY更好。

SELECT id, 
       orderid, 
       startdate, 
       begindate, 
       enddate, 
       ( CASE 
           WHEN T.enddate IS NULL THEN TM.md 
           ELSE NULL 
         END ) AS LastEnddate 
FROM   @MyTable T 
       CROSS apply (SELECT Max(enddate) MD 
                    FROM   @MyTable WHERE OrderId= T.OrderId) TM 
WHERE T.orderid=2345

Complete Example

完整的例子

DECLARE @MyTable TABLE 
  ( 
     id        INT, 
     orderid   INT, 
     startdate DATE, 
     begindate DATE, 
     enddate   DATE 
  ) 

INSERT INTO @MyTable 
SELECT * 
FROM   (VALUES (1, 
       2345, 
       CONVERT(DATETIME, '06/07/2011', 103), 
       CONVERT(DATETIME, '20/08/2011', 103), 
       CONVERT(DATETIME, '12/07/2012', 103)), 
               (2, 
       2345, 
       CONVERT(DATETIME, '05/07/2012', 103), 
       CONVERT(DATETIME, '11/07/2012', 103), 
       CONVERT(DATETIME, '13/01/2014', 103)), 
               (3, 
       2345, 
       CONVERT(DATETIME, '21/05/2014', 103), 
       CONVERT(DATETIME, '28/10/2013', 103), 
       CONVERT(DATETIME, '27/05/2015', 103)), 
               (4, 
       2345, 
       CONVERT(DATETIME, '23/05/2015', 103), 
       CONVERT(DATETIME, '03/06/2015', 103), 
       CONVERT(DATETIME, '02/06/2017', 103)), 
               (5, 
       2345, 
       CONVERT(DATETIME, '27/03/2017', 103), 
       CONVERT(DATETIME, '20/06/2017', 103), 
       NULL) ) t (id, orderid, startdate, begindate, enddate) 

SELECT id, 
       orderid, 
       startdate, 
       begindate, 
       enddate, 
       ( CASE 
           WHEN T.enddate IS NULL THEN TM.md 
           ELSE NULL 
         END ) AS LastEnddate 
FROM   @MyTable T 
       CROSS apply (SELECT Max(enddate) MD 
                    FROM   @MyTable WHERE OrderId= T.OrderId) TM 
WHERE T.orderid=2345

Output

产量

id  orderid startdate   begindate   enddate LastEnddate
1   2345    2011-07-06  2011-08-20  2012-07-12  NULL
2   2345    2012-07-05  2012-07-11  2014-01-13  NULL
3   2345    2014-05-21  2013-10-28  2015-05-27  NULL
4   2345    2015-05-23  2015-06-03  2017-06-02  NULL
5   2345    2017-03-27  2017-06-20  NULL    2017-06-02

#4

In addition to Anagha's answer I'd use the following query (from performance perspective)

除了Anagha的回答，我还会使用以下查询（从性能角度来看）

SELECT id, orderid,enddate, 
        CASE WHEN enddate IS NULL THEN LAST_VALUE(enddate) OVER (PARTITION BY OrderId ORDER BY enddate ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) END LastEndDate
 FROM #Test

in the Max(enddate) solution you'll get more logical reads as opposed to the LAST_VALUE solution

在Max（enddate）解决方案中，与LAST_VALUE解决方案相比，您将获得更多逻辑读取

See Statistics result:

见统计结果：

Max(enddate) - > Table 'Worktable'. Scan count 3, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#B131B1E5'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Max（enddate） - >表'工作表'。扫描计数3，逻辑读取15，物理读取0，预读读取0，lob逻辑读取0，lob物理读取0，lob预读读取0.表'＃B131B1E5'。扫描计数1，逻辑读取1，物理读取0，预读取读取0，lob逻辑读取0，lob物理读取0，lob预读读取0。

LAST_VALUE - > Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#B131B1E5'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

LAST_VALUE - >表'工作表'。扫描计数0，逻辑读取0，物理读取0，预读取读取0，lob逻辑读取0，lob物理读取0，lob预读读取0.表'＃B131B1E5'。扫描计数1，逻辑读取1，物理读取0，预读取读取0，lob逻辑读取0，lob物理读取0，lob预读读取0。

* Note: * This solution will work from SQL Server 2012 and up

*注意：*此解决方案适用于SQL Server 2012及更高版本

#1