从多个表返回具有最大日期的行

Question

我试图从产品序列号上的最后一个交易日期中提取信息，而不管最后一个位置。销售、制造和库存交易都位于不同的表中。

我想我已经用嵌套的CASE语句缩小了列的范围，但是现在我需要返回最近的行，“序列号”是唯一的值。

[IV00101].[ITEMDESC] AS 'Item Description',
[IV00200].[SERLNMBR] AS 'Serial Number',
[IV00200].[LOCNCODE] AS 'Location Code',  
    CASE ISNULL(COALESCE([SOP30200].[CUSTNAME],[SOP10100].[CUSTNAME]),'')
    WHEN '' THEN CASE ISNULL([BM30400].[DATERECD],'')
        WHEN '' THEN CASE
            WHEN [IV30400].[IVDOCTYP] = 1 THEN 'Adjustment'
            WHEN [IV30400].[IVDOCTYP] = 2 THEN 'Variance'
            WHEN [IV30400].[IVDOCTYP] = 3 THEN 'Transfer'
            ELSE 'Other'
            END
        ELSE 'Assembly'
        END
    ELSE CASE
        WHEN [SOP30200].[SOPNUMBE] LIKE 'RTN%' THEN 'Return'
        ELSE 'Sale'
        END
    END AS 'Transaction Type',
COALESCE([SOP30200].[DOCDATE], [BM30400].[DATERECD],[IV30200].[DOCDATE]) AS 'Last Transaction Date',
[SOP10201].[SOPNUMBE] AS 'Document Number',
COALESCE([SOP30200].[CUSTNAME],[SOP10100].[CUSTNAME]) AS 'Bill To' ,
[SOP10106].[USERDEF2] AS 'End Client' from [IV00200]
 inner join [IV00101] on [IV00200].[ITEMNMBR] = [IV00101].[ITEMNMBR]
 left join [IV30400] on [IV00200].[SERLNMBR] = [IV30400].[SERLTNUM]
 left join [SOP10201] on [IV00200].[SERLNMBR] = [SOP10201].[SERLTNUM]
 left join [SOP30200] on [SOP10201].[SOPNUMBE] = [SOP30200].[SOPNUMBE]
 left join [IV30200] on [IV30400].[DOCNUMBR] = [IV30200].[DOCNUMBR]
 left join [BM30400] on [IV00200].[SERLNMBR] = [BM30400].[SERLTNUM]
 left join [SOP10106] on [SOP10201].[SOPNUMBE] = [SOP10106].[SOPNUMBE]
 left join [SOP10100] on [SOP10201].[SOPNUMBE] = [SOP10100].[SOPNUMBE]

因为我所有的左加入，这就是我要得到的：

Item A  Product Info    123456789   SITEID1 Adjustment  10/31/2018  120696  Customer A
Item A  Product Info    123456789   SITEID2 Transfer    11/20/2018  120696  Customer A
Item A  Product Info    123456789   SITEID2 Sale    3/25/2019   120696  Customer A

我如何分组我的行，使我只返回这个？

Item A Product Info 123456789 SITEID2 Sale 3/25/2019 120696 Customer A

Answer 1

一个公共表表达式(CTE)加上窗口函数ROW_NUMBER应该可以工作。

1. 将查询放入CTE中
2. 添加ROW_NUMBER
3. 通过RowNum =1过滤CTE结果(以获得最新的事务)

WITH [SomeGoodName_CTE]
     AS (SELECT [IV00101].[ITEMDESC] AS 'Item Description'
              , [IV00200].[SERLNMBR] AS 'Serial Number'
              , [IV00200].[LOCNCODE] AS 'Location Code'
              , CASE ISNULL(COALESCE([SOP30200].[CUSTNAME], [SOP10100].[CUSTNAME]), '')
                  WHEN '' THEN CASE ISNULL([BM30400].[DATERECD], '')
                                 WHEN '' THEN CASE
                                                WHEN [IV30400].[IVDOCTYP] = 1 THEN 'Adjustment'
                                                WHEN [IV30400].[IVDOCTYP] = 2 THEN 'Variance'
                                                WHEN [IV30400].[IVDOCTYP] = 3 THEN 'Transfer'
                                                ELSE 'Other'
                                              END
                                 ELSE 'Assembly'
                               END
                  ELSE CASE
                         WHEN [SOP30200].[SOPNUMBE] LIKE 'RTN%' THEN 'Return'
                         ELSE 'Sale'
                       END
                END AS 'Transaction Type'
              , COALESCE([SOP30200].[DOCDATE], [BM30400].[DATERECD], [IV30200].[DOCDATE]) AS 'Last Transaction Date'
              , [SOP10201].[SOPNUMBE] AS 'Document Number'
              , COALESCE([SOP30200].[CUSTNAME], [SOP10100].[CUSTNAME]) AS 'Bill To'
              , [SOP10106].[USERDEF2] AS 'End Client'
              , [RowNum] = ROW_NUMBER() OVER(PARTITION BY [IV00200].[ITEMNMBR] ORDER BY COALESCE([SOP30200].[DOCDATE], [BM30400].[DATERECD], [IV30200].[DOCDATE]) DESC)
         FROM [IV00200]
              INNER JOIN [IV00101]
                ON [IV00200].[ITEMNMBR] = [IV00101].[ITEMNMBR]
              LEFT JOIN [IV30400]
                ON [IV00200].[SERLNMBR] = [IV30400].[SERLTNUM]
              LEFT JOIN [SOP10201]
                ON [IV00200].[SERLNMBR] = [SOP10201].[SERLTNUM]
              LEFT JOIN [SOP30200]
                ON [SOP10201].[SOPNUMBE] = [SOP30200].[SOPNUMBE]
              LEFT JOIN [IV30200]
                ON [IV30400].[DOCNUMBR] = [IV30200].[DOCNUMBR]
              LEFT JOIN [BM30400]
                ON [IV00200].[SERLNMBR] = [BM30400].[SERLTNUM]
              LEFT JOIN [SOP10106]
                ON [SOP10201].[SOPNUMBE] = [SOP10106].[SOPNUMBE]
              LEFT JOIN [SOP10100]
                ON [SOP10201].[SOPNUMBE] = [SOP10100].[SOPNUMBE])
     SELECT *
     FROM [SomeGoodName_CTE]
     WHERE [RowNum] = 1;