I have to find whether starting and ending date is falling in others start and end date.

Question

0.00/5 (No votes)

See more:

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?

Posted 10-Aug-15 1:48am

Member 11898509

Updated 10-Aug-15 2:04am

Maciej Los

v2

Add a Solution

Comments

Maciej Los 10-Aug-15 8:08am

What have you tried? Where are you stuck?

[no name] 10-Aug-15 8:17am

I have tried self join but it returning multiple license id also some of the dates which are overlapping are also there

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Andy Lanng · Accepted Answer · 2015-08-10T02:17:00

I go you one better:

SQL

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:

SQL

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:

SQL

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