Highlight missing follow up payment SQL Server-CodePudding

I am trying to find a way to create a query that highlights if an expected transaction followed a payment in SQL Server 2016.

The data I have is roughly

SELECT 123456 AS Casekey
        , 1 as rnk
        , NULL AS LAPayment
        , 1080.00 AS LAReserve
UNION ALL
SELECT 123456 AS Casekey
        , 2 as rnk
        , 1080.00 AS LAPayment
        , null AS LAReserve
UNION ALL
SELECT 123456 AS Casekey
        , 3 as rnk
        , NULL AS LAPayment
        , NULL AS LAReserve

There should be a follow up LAReserve of 0 after the LAPayment however there isn't - I need to highlight when this occurs - essentially the payment reduced the reserve to 0 but the way the system is configured a 0 is entered into a transaction screen and the data has to reflect the system - this is outside of my control sadly!

CodePudding user response：

You can use the LEAD window function for this

SELECT *
FROM (
    SELECT *,
      NextLAReserve = LEAD(LAReserve) OVER (PARTITION BY Casekey ORDER BY rnk)
    FROM YourTable t
) t
WHERE t.LAPayment > 0 AND t.NextLAReserve IS NULL;

CodePudding user response：

Windows functions are useful in this. You might need LAG() or LEAD() in this issue. Above answer of @Charlieface gives LEAD() and this gives LAG()

LAG() window function can be used in this occasion. It is better the expected output is given so that the result can be tested before posting.

SELECT Casekey
 , rnk
 , LAPayment
 , CASE WHEN LAG(LAPayment) over (order by Casekey)   is not null then  0 ELSE LAReserve END  AS LAReserve  
  FROM
  (SELECT 123456 AS Casekey
     , 1 as rnk
     , NULL AS LAPayment
     , 1080.00 AS LAReserve
    UNION ALL
   SELECT 123456 AS Casekey
      , 2 as rnk
      , 1080.00 AS LAPayment
      , null AS LAReserve
    UNION ALL
   SELECT 123456 AS Casekey
      , 3 as rnk
      , NULL AS LAPayment
      , NULL AS LAReserve
    ) AS A