如何编写查询来比较记录之间的日期 [英] How write a query to comparing dates between records
问题描述
大家都在努力为以下场景撰写查询:
Hi all am struggling to write a query for below scenario:
以下是该场景:
ID  &的StartDate NBSP;   EndDate
ID StartDate EndDate
条件: 比较 开始&端 日期介于1到5个记录之间,并查找是否有任何记录介于"开始"和"开始"之间。端 日期 然后"重叠"否则"插入"。
Condition : Compare Start & End dates between 1 through 5 records and find if any of the records fall between Start & End dates then "Overlap" else "Insert".
示例:
ID 1记录不在任何记录之间开始&端  所以它是一个"插入" 。
ID 1 record does not fall between any of the records Start & End so its a "Insert" .
ID 2记录StartDate只会落在ID 1 Start&端 与其他记录进行比较的日期,因此其 ""重叠 。
ID 2 record StartDate will fall only between ID 1 Start & End dates when compared with other records, so its "Overlap" .
ID 3记录StartDate不会落在任何 记录 Start&端 日期 所以它 "" Insert" 。
ID 3 record StartDate will not fall between any of record Start & End dates so its "Insert" .
ID 4条记录StartDate只会在ID 5开始& 之间端&NBSP;与其他记录进行比较的日期, """""<
ID 4 record StartDate will fall only between ID 5 Start & End dates when compared with other records, so its "Overlap" .
最后ID 5不会介于任何记录之间。端&NBSP;日期 所以它的"插入"
Finally ID 5 does not fall between any of the records Start & End dates so its "Insert"
结果:
ID &NBSP;&的StartDate NBSP;&NBSP;&NBSP;结束日期&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;检查
ID StartDate EndDate Check
提前致谢。
推荐答案
SELECT a.id, a.StartDate, a.EndDate,
[Check] = CASE WHEN EXISTS (SELECT *
FROM tbl b
WHERE a.id <> b.id
AND a.StartDate <= b.EndDate
AND a.EndDate >= b.StartDate)
THEN 'Overlap'
ELSE 'Insert'
END
FROM tbl b
这不会对任何卷执行,因为所有行多次相互比较。
This will not perform with any volumes, since all rows are compared against each other a multiple times.
这篇关于如何编写查询来比较记录之间的日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!