t在列中查找日期后的下一个日期值(SQL Server 2008)

Question

我正在尝试用两个表Orders和Repairs构建一个查询。目标是找到任何特定客户的订单日期之后的第一个维修日期，如果订单之间没有维修，则使用"null"。以下是每个的当前列：

CustomerID LocationID SaleDate
-------------------------------
1          1b         1/10/2019
1          1b         2/23/2019
1          1c         1/29/2019
2          2a         3/01/2019
2          2a         3/25/2019
CustomerID LocationID RepairDate
--------------------------------
1          1b         2/25/2019
1          1c         2/13/2019
1          1c         2/27/2019
2          2a         3/03/2019
2          2a         3/17/2019

这是预期的结果。有什么想法吗？出于某种原因，我尝试过的似乎都不起的作用

CustomerID LocationID SaleDate  RepairDate
-------------------------------------------
1          1b         1/10/2019 null
1          1b         2/23/2019 2/25/2019
1          1c         1/29/2019 2/13/2019
2          2a         3/1/2019  3/3/2019
2          2a         3/25/2019 null

Answer 1

这有点冗长，但这是我想到的最好的方法，可以防止相同的维修与以前的所有销售相匹配，我认为这是Gordon Linoff简单得多的答案的一个问题。我已经将您的两个表统一为一个事件日志，然后使用row_number窗口函数来识别影响同一客户和位置的事件序列。在这里试试

WITH eventlog
AS (SELECT *,
Row_number()
OVER(
partition BY customerid, locationid
ORDER BY eventdate) AS seq
FROM   (SELECT customerid,
locationid,
saledate AS eventdate,
's'      AS type
FROM   orders
UNION
SELECT customerid,
locationid,
repairdate AS repairdate,
'r'        AS type
FROM   repairs) a)
SELECT s.customerid,
s.locationid,
s.eventdate AS saledate,
r.eventdate AS repairdate
FROM   eventlog s
LEFT JOIN eventlog r
ON s.seq + 1 = r.seq
AND s.customerid = r.customerid
AND s.locationid = r.locationid
AND r.type = 'r'
WHERE  s.type = 's';  

Answer 2

使用outer apply:

select o.*, r.*
from orders o outer apply
(select top (1) r.*
from repairs r
where r.customerId = o.customerId and
r.locationid = o.locationid and
r.RepairDate > o.SaleDate
order by r.RepairDate
) r;

注意：您指定您想要客户的第一个维修日期。然而，数据表明，你真正指的是客户/地点的组合。

编辑：

感谢您设置SQL Fiddle。这澄清了这个问题。我认为这个调整符合你的要求：

--Gordon Linoff 的回答

select o.*, r.*
from (select o.*,
lead(saledate) over (partition by customerid order by saledate) as next_salesdate
from orders o
) o outer apply
(select top (1) r.*
from repairs r
where r.customerId = o.customerId and
r.locationid = o.locationid and
r.RepairDate > o.SaleDate and
(r.RepairDate < o.next_salesdate or o.next_salesdate is null)
order by r.RepairDate
) r;

这是数据库<>不停摆弄