Блог

У меня есть таблица с уникальным индексом по Контрактам Клиентов, которые живут в Домах. Я хочу знать, сколько дней на дом, сколько времени требуется, когда кто-то съезжает (дата окончания контракта) и начинается новый контракт. Для этого я хочу знать, какой первый следующий контракт будет в этом доме, но в той же строке, что и старый контракт для (потенциально другого) клиента.

Вот как выглядит таблица в данный момент, я выбираю здесь топ-10:

 SELECT TOP 10
PMCCONTRACT.ACCOUNTNUM  --Customer
,PMCCONTRACT.RENTALOBJECTID --House
,PMCCONTRACT.CONTRACTID --Contract amp; Unique index of the table
,PMCCONTRACT.VALIDFROM  --Contract Start Date
,PMCCONTRACT.VALIDTO    --Contract End Date
FROM PMCCONTRACT
 

Затем выкатывается вот это:

 ACCOUNTNUM  RENTALOBJECTID  CONTRACTID  VALIDFROM               VALIDTO
101852      2488            HC000001    1994-03-01 00:00:00.000 NULL
101136      2489            HC000002    1920-01-01 00:00:00.000 NULL
101352      2491            HC000003    1996-09-16 00:00:00.000 NULL
100687      2492            HC000004    1984-11-01 00:00:00.000 NULL
105160      2499            HC000005    1975-05-02 00:00:00.000 2018-01-31 00:00:00.000
102821      2501            HC000006    1997-09-16 00:00:00.000 NULL
100731      2506            HC000007    1920-01-01 00:00:00.000 2018-11-15 00:00:00.000
102797      2508            HC000008    1998-02-01 00:00:00.000 NULL
102155      2512            HC000009    1981-09-01 00:00:00.000 NULL
102563      2515            HC000010    1965-10-17 00:00:00.000 2017-06-30 00:00:00.000
 

И чего я хочу, так это чтобы на основе RENTALOBJECTID он показывал, каким был Первый следующий контракт на этот дом (поэтому важно, чтобы CONTRACTID оставался уникальным в этой таблице).

Ниже приведен код, который я использую для его получения, однако он показывает все следующие изменения контракта для этого конкретного RENTALOBJECTID (House).

 SELECT --TOP 1000
     PMCCONTRACT.CONTRACTID
    ,PMCCONTRACT.RENTALOBJECTID
    ,PMCCONTRACT.VALIDFROM
    ,PMCCONTRACT.VALIDTO
    ,P2.CONTRACTID AS 'FirstNextContractId'
    ,P2.VALIDFROM 
    ,P2.VALIDTO 
FROM PMCCONTRACT
    LEFT JOIN PMCCONTRACT P2 
        ON  PMCCONTRACT.RENTALOBJECTID = P2.RENTALOBJECTID
        LEFT JOIN 
            (SELECT 
                RENTALOBJECTID,
                MAX(CONTRACTID) AS CONTRACTID 
            FROM PMCCONTRACT  
            GROUP BY RENTALOBJECTID) X ON X.CONTRACTID = P2.CONTRACTID
WHERE P2.VALIDFROM > PMCCONTRACT.VALIDTO
 

Это то, что я получаю, когда я выбираю только ContractID HC000028, он показывает 2 строки, в то время как я хочу, чтобы он показывал только первую строку.

 CONTRACTID  RENTALOBJECTID  VALIDFROM               VALIDTO                 FirstNextContractId VALIDFROM2              VALIDTO2
HC000028    75              1995-01-01 00:00:00.000 2016-04-30 00:00:00.000 HC009990            2016-05-01 00:00:00.000 2018-11-25 00:00:00.000 --<< Only row I want to show
HC000028    75              1995-01-01 00:00:00.000 2016-04-30 00:00:00.000 HC025218            2018-11-26 00:00:00.000 1900-01-01 00:00:00.000 --Too far in the future
 

С уважением,

Игорь

Похоже, достаточно простой LEAD оконной функции. Он возвращает следующую строку, как определено в предложениях partitioning и ordering .

 SELECT TOP 10
    PMCCONTRACT.ACCOUNTNUM  --Customer
    ,PMCCONTRACT.RENTALOBJECTID --House
    ,PMCCONTRACT.CONTRACTID --Contract amp; Unique index of the table
    ,PMCCONTRACT.VALIDFROM  --Contract Start Date
    ,PMCCONTRACT.VALIDTO    --Contract End Date
    ,LEAD(CONTRACTID) OVER (PARTITION BY RENTALOBJECTID ORDER BY VALIDFROM) AS NextContractID
    ,LEAD(VALIDFROM)  OVER (PARTITION BY RENTALOBJECTID ORDER BY VALIDFROM) AS NextVALIDFROM
    ,LEAD(VALIDTO)    OVER (PARTITION BY RENTALOBJECTID ORDER BY VALIDFROM) AS NextVALIDTO
FROM PMCCONTRACT
;