Мне нужно создать поле, которое является второй датой для maxdate

#sql #sql-server

#sql #sql-сервер

Вопрос:

Моя база данных SQL Server включает в себя все контракты. мой запрос находит значение контракта для последней даты, мне нужен другой столбец, который показывает значение контракта для предпоследнего контракта.

Пример:

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

Чего бы я хотел, так это чтобы ExpiringAmt 2 показывал 833.15

Код

 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

Ответ №1:

Вы можете сделать это с помощью оконных функций LAG() и 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

Если вас интересует только 1 ContractID , как ваш запрос, то вы можете удалить PARTITION BY ContractID из обоих окон функции.

Смотрите демонстрацию.
Результаты:

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

Ответ №2:

Я бы использовал LEAD() или LAG() для добавления следующего или предыдущего значения ordd_mn_billed_amt , а затем использовал ROW_NUMBER() , чтобы всегда выбирать первую строку…

 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

В этом случае я использовал LEAD() так, чтобы окно для обеих функций было в том же порядке.