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
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