我必须找到其他开始和结束日期的开始和结束日期是否下降。 [英] I have to find whether starting and ending date is falling in others start and end date.
本文介绍了我必须找到其他开始和结束日期的开始和结束日期是否下降。的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一张有id，licenseno，stdate和mddate的表。我想发现任何许可证已经落入其他许可证的开始日期或结束日期。
例如我的表格是 -
id licenseno startdate enddate
1 L1 01-01-13 15-01-13
2 L1 02-01-13 10-01-13
3 L1 20-12-12 20-01-13
4 L1 02-12-11 18-12-12
10 L1 25-01-13 30-11-13
5 L2 01-01-13 15-12-13
6 L2 02-01-13 10-12-13
7 L2 02-12-12 30-12-12
8 L2 05-12-12 30-12-12
12 L2 01-12-14 30-11-14
9 L3 01-11-12 15-11-12
11 L3 01-01-14 30-11-14
AND我想要这样的输出 -
id licenseno startdate enddate flag
1 L1 01-01-13 15-01-13 0
2 L1 02-01-13 10-01-13 0
3 L1 20-12-12 20-01-13 0
4 L1 02-12-11 18-12-12 1
10 L1 25-01-13 30-11-13 1
5 L2 01-01- 13 15-12-13 0
6 L2 02-01-13 10-12-13 0
7 L2 02-12-12 30-12-12 0
8 L2 05-12 -12 30-12-12 0
12 L2 01-12-14 30-11-14 1
9 L3 01-11-12 15-11-12 1
11 L3 01- 01-14 30-11-14 1
有人可以帮忙吗？
解决方案
我更好地告诉你：
选择 m.id，
m.licenceId，
m.sd， - Startdate
m.ed， - EndDate
count（c.id） as NumberOfOverlaps
from mytable m
left outer join mytable c on m.licenceId = c.licenceId AND c.ed> m.sd AND c.sd< m.ed
group by m.id，
m.licenceId，
m.sd，
m.ed
订单 按 m。 licenceId，m.sd
看看是否有效
更新：
我添加了一些东西来显示重叠中包含的ID：
选择 m.id，
m.licenceId，
m.sd，
m.ed，
count（c.id） as flag，
STUFF（（ SELECT DISTINCT ' ，' + NULLIF （cast（t2.id as nvarchar （max）），< span class =code-string>' '）
FROM mycte t2
WHERE m.licenceId = t2.licenceId AND t2.ed> m.sd AND t2.sd< m.ed
FOR XML PATH（' '）， TYPE
）.value（' 。'，' VARCHAR（MAX）'）
， 1 ， 2 ，' < span class =code-string>'） AS checkcsv
来自 mycte m
left 外部 join mycte c on m.licenceId = c.licenceId AND c.ed> m.sd AND c.sd< m.ed
group by m.id，
m.licenceId，
m.sd，
m.ed
订单 按 m。 licenceId，m.sd
这表明每行都匹配。 12没有，因为我在我的文本数据中混淆了日期（结束是在开始之前）
为了消除这个，我已经包含了一个额外的条件：
已修复：
选择 m.id，
m.licenceId，
m.sd，
m.ed，
case count （c.id）当 0 然后 0 else 1 end as flag
from mycte m
left 外部 join mycte c c.ed> m.sd AND c.sd< m.ed AND c.id！= m.id
group by m.id，
m.licenceId，
m.sd，
m.ed
订单 按 m.licenceId，m.sd
输出包括上面查询中没有的checkcsv：
id lId sd ed flag checkcsv
4 L1 2011-12-02 2012 -12-18 0 NULL
3 L1 2012-12-20 2013-01-20 1 1,2
1 L1 2013-01-01 2013-01-15 1 2,3
2 L1 2013-01-10 2013-01-20 1 1,3
10 L1 2013-01-25 2013-11-30 0 NULL
7 L2 2012-12-02 2012-12-30 1 8
8 L2 2012-12-05 2012-12-30 1 7
5 L2 2013-01-01 2013-12-15 1 6
6 L2 2013-01-02 2013 -10-12 1 5
12 L2 2014-12-01 2014-11-30 0 NULL
9 L3 2012-11-01 2012-11-15 0 NULL
11 L3 2014- 01-01二〇一四年十一月三十○日0 NULL
I have a table which has id, licenseno, stdate and mddate. I want to find that any license has been fallen into other license startdate or enddate.
for example my table is-
id licenseno startdate enddate
1 L1 01-01-13 15-01-13
2 L1 02-01-13 10-01-13
3 L1 20-12-12 20-01-13
4 L1 02-12-11 18-12-12
10 L1 25-01-13 30-11-13
5 L2 01-01-13 15-12-13
6 L2 02-01-13 10-12-13
7 L2 02-12-12 30-12-12
8 L2 05-12-12 30-12-12
12 L2 01-12-14 30-11-14
9 L3 01-11-12 15-11-12
11 L3 01-01-14 30-11-14
AND I want output like this-
id licenseno startdate enddate flag
1 L1 01-01-13 15-01-13 0
2 L1 02-01-13 10-01-13 0
3 L1 20-12-12 20-01-13 0
4 L1 02-12-11 18-12-12 1
10 L1 25-01-13 30-11-13 1
5 L2 01-01-13 15-12-13 0
6 L2 02-01-13 10-12-13 0
7 L2 02-12-12 30-12-12 0
8 L2 05-12-12 30-12-12 0
12 L2 01-12-14 30-11-14 1
9 L3 01-11-12 15-11-12 1
11 L3 01-01-14 30-11-14 1
can anyone help?
解决方案
I go you one better:
select m.id, m.licenceId, m.sd, --Startdate m.ed, --EndDate count(c.id) as NumberOfOverlaps from mytable m left outer join mytable c on m.licenceId = c.licenceId AND c.ed > m.sd AND c.sd < m.ed group by m.id, m.licenceId, m.sd, m.ed order by m.licenceId, m.sd
see if that works
UPDATE:
I added a stuff to show ids that were being included in the overlaps:
select m.id, m.licenceId, m.sd, m.ed, count(c.id) as flag, STUFF((SELECT DISTINCT ', ' + NULLIF(cast(t2.id as nvarchar(max)),'') FROM mycte t2 WHERE m.licenceId = t2.licenceId AND t2.ed > m.sd AND t2.sd < m.ed FOR XML PATH(''),TYPE ).value('.','VARCHAR(MAX)') ,1,2,'') AS checkcsv from mycte m left outer join mycte c on m.licenceId = c.licenceId AND c.ed > m.sd AND c.sd < m.ed group by m.id, m.licenceId, m.sd, m.ed order by m.licenceId, m.sd
This showed that each row matched itself. 12 did not because I got the dates mixed up in my text data (end was before start)
To eliminate this I have included an extra condition:
Fixed:
select m.id, m.licenceId, m.sd, m.ed, case count(c.id) when 0 then 0 else 1 end as flag from mycte m left outer join mycte c on m.licenceId = c.licenceId AND c.ed > m.sd AND c.sd < m.ed AND c.id != m.id group by m.id, m.licenceId, m.sd, m.ed order by m.licenceId, m.sd
Output including checkcsv not in query above:
id lId sd ed flag checkcsv 4 L1 2011-12-02 2012-12-18 0 NULL 3 L1 2012-12-20 2013-01-20 1 1, 2 1 L1 2013-01-01 2013-01-15 1 2, 3 2 L1 2013-01-10 2013-01-20 1 1, 3 10 L1 2013-01-25 2013-11-30 0 NULL 7 L2 2012-12-02 2012-12-30 1 8 8 L2 2012-12-05 2012-12-30 1 7 5 L2 2013-01-01 2013-12-15 1 6 6 L2 2013-01-02 2013-10-12 1 5 12 L2 2014-12-01 2014-11-30 0 NULL 9 L3 2012-11-01 2012-11-15 0 NULL 11 L3 2014-01-01 2014-11-30 0 NULL
这篇关于我必须找到其他开始和结束日期的开始和结束日期是否下降。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
