sqlsql-serversql-server-2016lead

Grouping data using the LEAD() function


I've got this query:

SELECT TOP (1000) [TRANS_ID]
      ,[VSN_VENDR_EFF_DT]
      ,LEAD([VSN_VENDR_EFF_DT], 1, '9999-12-31') OVER (ORDER BY [ACCT_NUM],[VSN_VENDR_EFF_DT]) AS NEXT_EFF_DT
      ,[VSN_VENDR_CD]
      ,[ACCT_NUM]
      ,[VSN_VENDR_CAN_DT]
  FROM [SOBI_REPORTING].[dbo].[AS_tblVSN_ACCT_VENDR]
  --where ACCT_NUM = '0607805'
  order by ACCT_NUM, VSN_VENDR_EFF_DT

It produces these results when I look at just a specific segment of data (the actual dataset returned is hundreds of thousand of records, I'm just zooming in on a specific ACCT_NUM):

TRANS_ID    VSN_VENDR_EFF_DT    NEXT_EFF_DT VSN_VENDR_CD    ACCT_NUM    VSN_VENDR_CAN_DT
8463855     2013-07-01          2023-07-01           VSP    0607805             NULL
9075501     2023-07-01          2012-06-01          EYMD    0607805             NULL
8395520     2012-06-01          2024-06-01           VSP    0607811             NULL
9119702     2024-06-01          2012-06-01          EYMD    0607811             NULL

However, when I uncomment that WHERE statement, it produces these results, which are actually what I'm expecting:

TRANS_ID    VSN_VENDR_EFF_DT    NEXT_EFF_DT VSN_VENDR_CD    ACCT_NUM    VSN_VENDR_CAN_DT
8463855     2013-07-01          2023-07-01           VSP     0607805            NULL
9075501     2023-07-01          9999-12-31          EYMD     0607805            NULL

Note the NEXT_EFF_DT values between the two. How can I get my query to return proper results? I need to group by ACCT_NUM and order by VSN_VENDR_EFF_DT, and my current query appears to be grabbing the next VSN_VENDR_EFF_DT without regard to ACCT_NUM.

To be clear, I'm expecting something like this, given the data above:

TRANS_ID    VSN_VENDR_EFF_DT    NEXT_EFF_DT VSN_VENDR_CD    ACCT_NUM    VSN_VENDR_CAN_DT
8463855     2013-07-01          2023-07-01           VSP    0607805             NULL
9075501     2023-07-01          9999-12-31          EYMD    0607805             NULL
8395520     2012-06-01          2024-06-01           VSP    0607811             NULL
9119702     2024-06-01          9999-12-31          EYMD    0607811             NULL

Solution

  • To "group by" in the window functions' parlance, you need to use PARTITION BY clause, so something like:

    SELECT TOP (1000) [TRANS_ID]
          ,[VSN_VENDR_EFF_DT]
          ,LEAD([VSN_VENDR_EFF_DT], 1, '9999-12-31') OVER (PARTITION BY [ACCT_NUM] ORDER BY [VSN_VENDR_EFF_DT]) AS NEXT_EFF_DT
          ,[VSN_VENDR_CD]
          ,[ACCT_NUM]
          ,[VSN_VENDR_CAN_DT]
      FROM [SOBI_REPORTING].[dbo].[AS_tblVSN_ACCT_VENDR]
      --where ACCT_NUM = '0607805'
      order by ACCT_NUM, VSN_VENDR_EFF_DT