I need to create a field that is the second date to a maxdate

user_2576682 0
Viewed 41

My SQL Server database includes all contracts. my query finds the contract value for the latest date, I need another column that shows the contract value for the second last contract.

Example:

ContractID  ordd_mn_end_date    ord_mn_billed_amt
-------------------------------------------------
8198        10-31-2021           2574.43
8198        10-31-2020           833.15

What I would like to happen is for the ExpiringAmt 2 to show 833.15

Code

SELECT 
    det.ordd_ContractItemID,
    det.ordd_mn_end_date,
    det.ordd_mn_billed_amt AS Expiring_Amt,
    det.ordd_mn_billed_amt AS Expiring_Amt2
FROM 
    ccmast_restore_20201021.dbo.lti_orddet det
WHERE 
    det.ordd_mn_end_date IN (SELECT MAX(det.ordd_mn_end_date) 
                             FROM ccmast_restore_20201021.dbo.lti_orddet det 
                             GROUP BY det.ordd_ContractItemID)
    AND det.ordd_ContractItemID IN (8198)
ORDER BY 
    det.ordd_mn_end_date DESC
sql-server sql
2 Answers

You can do it with window functions LAG() and ROW_NUMBER():

SELECT ContractID, ordd_mn_end_date, Expiring_Amt, Expiring_Amt2
FROM (
  SELECT ContractID,
         ordd_mn_end_date,
         ord_mn_billed_amt Expiring_Amt,
         LAG(ord_mn_billed_amt) OVER (PARTITION BY ContractID ORDER BY ordd_mn_end_date) Expiring_Amt2,
         ROW_NUMBER() OVER (PARTITION BY ContractID ORDER BY ordd_mn_end_date DESC) rn
  FROM lti_orddet 
  WHERE ContractID IN (8198)
) t
WHERE rn = 1

If you are interested only in 1 ContractID, like your query, then you can remove PARTITION BY ContractID from both window functions.

See the demo.
Results:

ContractID ordd_mn_end_date Expiring_Amt Expiring_Amt2
8198 2021-10-31 2574.43 833.15
user_104988280

I would use LEAD() or LAG() to append the next or previous value of ordd_mn_billed_amt , and then use ROW_NUMBER() to always pick the first row...

WITH
  lti_orddet_augmented AS
(
  SELECT
    lti_orddet.*,
    LEAD(ord_mn_billed_amt)
      OVER (PARTITION BY ordd_ContractItemID
                ORDER BY ordd_mn_end_date DESC
           )
             AS ord_mn_billed_amt_previous,
    ROW_NUMBER()
      OVER (PARTITION BY ordd_ContractItemID
                ORDER BY ordd_mn_end_date DESC
           )
             AS ordd_ContractItemID_RowNum
  FROM
    ccmast_restore_20201021.dbo.lti_orddet
)
SELECT
  *
FROM
  lti_orddet_augmented 
WHERE
      ordd_ContractItemID_RowNum = 1
  AND ordd_ContractItemID        = 8198

In this case I used LEAD() so that the window for both functions is in the same order.

Dems_533410
Related