WHERE子句中的Datediff不起作用
WHERE子句中的Datediff不起作用
提问于 2015-11-10 19:25:20
我有下面的脚本。我需要在30天内得到顾客回来的所有访问。脚本中的所有操作都很好，但where子句中的datediff却很好。谁能帮我修一下逻辑吗？
select OverallNumber,VisitID,coalesce(CheckInDateTime,ServiceDateTime) as ArrivalDateTime, DepartDateTime
from Visits
where OverallNumber='100'
and DepartDateTime is not null
and coalesce(CheckInDateTime,ServiceDateTime) is not null
--and DATEDIFF(day,MIN(coalesce(CheckInDateTime,ServiceDateTime)), Max(DepartDateTime))<30
order by ArrivalDateTime;当前数据：
OverallNumber VisitID ArrivalDateTime DepartDateTime
100 1 6/13/2015 6/15/2015
100 2 11/5/2015 11/7/2015
100 3 11/20/2015 11/25/2015
Desired:
OverallNumber VisitID ArrivalDateTime DepartDateTime
100 2 11/5/2015 11/7/2015
100 3 11/20/2015 11/25/2015在这个例子中，客户从2015年11月5日到2015年11月25日两次回来。请帮帮忙。谢谢。
回答 1
Stack Overflow用户
回答已采纳
发布于 2015-11-10 20:02:09
SELECT OverallNumber,
VisitID,
ArrivalDateTime,
DepartDateTime
FROM (
SELECT
OverallNumber,
VisitID,
ArrivalDateTime,
DepartDateTime,
LAG(DepartDateTime, 1, NULL) OVER (PARTITION BY OverallNumber ORDER BY ArrivalDateTime) PreviousDateDiff,
LEAD(ArrivalDateTime, 1, NULL) OVER (PARTITION BY OverallNumber ORDER BY ArrivalDateTime) NextDateDiff
FROM (
SELECT
*,
COALESCE(CheckInDateTime,ServiceDateTime) ArrivalDateTime
FROM
Visits
WHERE
OverallNumber = '100'
AND DepartDateTime IS NOT NULL) t
) v
WHERE
ArrivalDateTime < DATEADD(day, 30, PreviousDateDiff)
OR DATEADD(day, -30, NextDateDiff) < DepartDateTime
ORDER BY
ArrivalDateTime;滞后将给您上一个DepartDateTime，而铅函数将给您下一个ArrivalDateTime。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/33637949
复制相关文章
相似问题
腾讯云开发者
