根据简单条件显示下一日期(潜在客户)

Question

我有一张表，上面有一个关于住在房子里的客户合同的唯一索引，我想知道一栋房子需要多少天才能有下一个人居住。

我已经走得很远了，但不幸的是，我的数据集有类型= 0的合同，这些合同是由系统自动生成的，应该被忽略，如果我不忽略这些类型= 0的“空合同”，那么数据显示实际上所有的房子都在1天内有人居住。

现在我得到的结果如下：

SELECT
CONTRACTID
,RENTALOBJECTID
,TYPE
,VALIDFROM
,VALIDTO
,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'
FROMPMCCONTRACT

使用以下代码：

CONTRACTID  RENTALOBJECTID  TYPE    VALIDFROM   VALIDTO     NextContractId  NextValidFrom   NextValidTo
HC001       1               0       1/1/2015    1/1/2017    HC002           1/2/2017        8/1/2017
HC002       1               0       1/2/2017    8/1/2017    HC003           8/2/2017        NULL
HC003       1               3       8/2/2017    NULL        NULL            NULL            NULL

但是，我希望结果如下所示，其中忽略TYPE = 0的合同。

CONTRACTID  RENTALOBJECTID  TYPE    VALIDFROM   VALIDTO     NextContractId  NextValidFrom   NextValidTo
HC001       1               3       1/1/2015    1/1/2017    HC003           8/2/2017        NULL
HC002       1               0       1/2/2017    8/1/2017    NULL            NULL            NULL
HC003       1               3       8/2/2017    NULL        NULL            NULL            NULL

正如您可以看到的，RENTALOBJECTID =1在CONTRACT = HC001之后再次居住的时间现在超过了一个月。

有人知道这在SQL-server-2012中是如何工作的吗？

致以亲切的问候，

Igor

Answer 1

您的示例数据有些不一致，并且您省略了对期望结果的某些方面的解释，但这基本上应该能满足您的需要。

窗口框架被设置为按降序排列的VALIDFROM，因此ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING包括VALIDFROM较高的所有行。只有带有TYPE <> 0的窗口才会得到一个非空的ConcatResult，并且MIN会挑选出该窗口帧中VALIDFROM最低的窗口(即当前行的下一个最大的窗口)。然后，从该结果中取出三个连接的列值。

WITH PMC
     AS (SELECT CONTRACTID,
                RENTALOBJECTID,
                TYPE,
                VALIDFROM,
                VALIDTO,
                ConcatResult = MIN(CASE
                                     WHEN TYPE <> 0
                                       THEN FORMAT(VALIDFROM, 'yyyy-MM-dd')
                                            + FORMAT(ISNULL(VALIDTO, '1900-01-01'), 'yyyy-MM-dd')
                                            + CONTRACTID
                                   END)
                                 OVER (
                                   PARTITION BY RENTALOBJECTID
                                   ORDER BY VALIDFROM DESC 
                                   ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
         FROM   PMCCONTRACT)
SELECT *,
       NextContractId = SUBSTRING(ConcatResult, 21, 10),
       NextValidFrom = CAST(SUBSTRING(ConcatResult, 1, 10) AS DATE),
       NextValidTo = CAST(NULLIF(SUBSTRING(ConcatResult, 11, 10), '1900-01-01') AS DATE)
FROM   PMC
ORDER  BY RENTALOBJECTID,
          VALIDFROM