SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 First and last date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Luuk123
Starting Member

49 Posts

Posted - 11/29/2013 :  02:53:54  Show Profile  Reply with Quote
Hi all,

I have a table with the columns Person, date_from and date_to, (both datetime columns) with multiple records per person.
I want to query this table and get one result per person with the minimal date_from and maximal date_to found by this person, but only if there is overlap in date/time.

How can I achieve this?

A part of the table:

person_id    date_from                             date_to
2            2013-07-30 09:00:00.000               2013-07-30 09:00:00.000
14           2013-08-20 06:00:00.000               2013-08-20 10:00:00.000
15           2013-08-01 10:00:00.000               2013-08-10 09:00:00.000
15           2013-08-20 15:00:00.000               2013-10-24 22:00:00.000
25           2013-08-25 06:00:00.000               2013-08-25 18:00:00.000
25           2013-10-15 06:00:00.000               2013-10-15 18:00:00.000
26           2013-10-15 07:00:00.000               2013-10-15 17:00:00.000
26           2013-10-15 15:00:00.000               2013-10-16 23:59:59.000
54           2013-08-20 17:00:00.000               2013-08-20 23:00:00.000


For example, for person 26 I want the result:
26 2013-10-15 07:00:00.000 2013-10-16 23:59:59.000 (there is an overlap between the date_to of the first and the date_from of the second)

For person 25 there's no overlap so this rules doesn't have to be grouped.

Thanks!

Edited by - Luuk123 on 11/29/2013 02:58:29

khtan
In (Som, Ni, Yak)

Singapore
17598 Posts

Posted - 11/29/2013 :  02:57:23  Show Profile  Reply with Quote

SELECT person_id, MIN(date_from), MAX(date_to)
FROM   yourtable
GROUP BY person_id


EDIT : oh you have added new condition on overlapping. The above query does not apply with that condition


KH
Time is always against us


Edited by - khtan on 11/29/2013 03:01:01
Go to Top of Page

Luuk123
Starting Member

49 Posts

Posted - 11/29/2013 :  02:59:31  Show Profile  Reply with Quote
quote:
Originally posted by khtan


SELECT person_id, MIN(date_from), MAX(date_to)
FROM   yourtable
GROUP BY person_id



KH
Time is always against us





Thanks for your reply but I forgot something in my question so I updated it.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17598 Posts

Posted - 11/29/2013 :  03:01:29  Show Profile  Reply with Quote
so what is the expected result with that new condition added ?


KH
Time is always against us

Go to Top of Page

Luuk123
Starting Member

49 Posts

Posted - 11/29/2013 :  03:03:58  Show Profile  Reply with Quote

person_id    date_from                             date_to
2            2013-07-30 09:00:00.000               2013-07-30 09:00:00.000
14           2013-08-20 06:00:00.000               2013-08-20 10:00:00.000
15           2013-08-01 10:00:00.000               2013-08-10 09:00:00.000
15           2013-08-20 15:00:00.000               2013-10-24 22:00:00.000
25           2013-08-25 06:00:00.000               2013-08-25 18:00:00.000
25           2013-10-15 06:00:00.000               2013-10-15 18:00:00.000
26           2013-10-15 07:00:00.000               2013-10-16 23:59:59.000
54           2013-08-20 17:00:00.000               2013-08-20 23:00:00.000

The result of person 26 is grouped because there's an overlap in the dates. This is the result I need.

Edited by - Luuk123 on 11/29/2013 03:04:35
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17598 Posts

Posted - 11/29/2013 :  03:14:39  Show Profile  Reply with Quote
; with 
cte as
(
	select	person_id, date_from, date_to, rn = row_number() over (partition by person_id order by date_from)
	from	person
),
cte2 as
(
	select	*, overlap = case when 	exists 
					(
						select 	* 
						from 	cte x 
						where 	x.person_id 	= p.person_id 
						and 	x.rn		<> p.rn
						and	(
								x.date_from	between	p.date_from and p.date_to	
							or	x.date_to	between	p.date_from and p.date_to	
							)
					)
				  then	'Y'
				  else	'N'
				  end
	from	cte p
)
select	person_id, date_from, date_to
from	cte2
where	overlap	= 'N'
union	all
select	person_id, date_from = min(date_from), date_to = max(date_to)
from	cte2
where	overlap	= 'Y'
group by person_id



KH
Time is always against us

Go to Top of Page

Luuk123
Starting Member

49 Posts

Posted - 11/29/2013 :  04:08:43  Show Profile  Reply with Quote
Thanks for your reply, but it doesn't work correctly.
The

select	person_id, date_from = min(date_from), date_to = max(date_to)
from	cte2
where	overlap	= 'Y'
group by person_id

groups all dates from one person where there's overlap. See the following example of data:

person_id date_from date_to
15 2013-07-30 09:00:00.000 2013-07-30 17:00:00.000 (overlap with row 2)
15 2013-07-30 15:00:00.000 2013-07-30 20:00:00.000 (overlap with row 1)
15 2013-08-01 10:00:00.000 2013-08-10 09:00:00.000 (no overlap)
15 2013-08-20 15:00:00.000 2013-08-20 17:00:00.000 (overlap with row 5)
15 2013-08-20 16:00:00.000 2013-08-20 22:00:00.000 (overlap with row 4)

Your query gives me this:

person_id date_from date_to
15 2013-07-30 09:00:00.000 2013-08-20 22:00:00.000 (date_from of row 1, date_to of row 5)
15 2013-08-01 10:00:00.000 2013-08-10 09:00:00.000 (row 3)

I need:
person_id date_from date_to
15 2013-07-30 09:00:00.000 2013-07-30 20:00:00.000 (date_from of row 1, date_to of row 2)
15 2013-08-01 10:00:00.000 2013-08-10 09:00:00.000 (no overlap)
15 2013-08-20 15:00:00.000 2013-08-20 22:00:00.000 (date_from of row 4, date_to of row 5)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/29/2013 :  04:33:21  Show Profile  Reply with Quote
this?


--illustration of your table
declare @t table
(
person_id  int,
date_from  datetime,
date_to datetime
)
insert @t
values (2 ,           '2013-07-30 09:00:00.000',               '2013-07-30 09:00:00.000'),
(14,           '2013-08-20 06:00:00.000',              '2013-08-20 10:00:00.000'),
(15,           '2013-08-01 10:00:00.000',               '2013-08-10 09:00:00.000'),
(15,           '2013-08-20 15:00:00.000',               '2013-10-24 22:00:00.000'),
(25,           '2013-08-25 06:00:00.000',               '2013-08-25 18:00:00.000'),
(25 ,          '2013-10-15 06:00:00.000',               '2013-10-15 18:00:00.000'),
(26 ,          '2013-10-15 07:00:00.000',               '2013-10-15 17:00:00.000'),
(26 ,          '2013-10-15 15:00:00.000',               '2013-10-16 23:59:59.000'),
(54 ,          '2013-08-20 17:00:00.000',               '2013-08-20 23:00:00.000'),
(54 ,          '2013-08-20 21:00:00.000',               '2013-08-21 06:00:00.000'),
(54 ,          '2013-08-20 22:00:00.000',               '2013-08-20 23:50:00.000')

--getting data onto a working table for processing
SELECT * INTO #Temp FROM @T

CREATE CLUSTERED INDEX IDX_Clust ON #Temp (person_id,date_from)

DECLARE @PersonID int,@FrmDt datetime,@ToDt datetime

SELECT TOP 1 @PersonID= person_id,@FrmDt=date_from,@ToDt = date_to
FROM #Temp
ORDER BY person_id,date_from

UPDATE t
SET 
@FrmDt=date_from = CASE WHEN person_id = @PersonID AND date_from > @FrmDt AND date_from < = @ToDt THEN @FrmDt ELSE date_from END,
@PersonID= person_id,
@ToDt = date_to
FROm #Temp t
OPTION (MAXDOP 1)

--your final result
SELECT Person_id,date_from,MAX(date_to) AS date_to
FROM #temp
GROUP BY Person_id,date_from
--destroy working table after use
DROP TABLE #Temp

Person_id	date_from	        date_to
---------------------------------------------------------------
2	        2013-07-30 09:00:00.000	2013-07-30 09:00:00.000
14	        2013-08-20 06:00:00.000	2013-08-20 10:00:00.000
15	        2013-08-01 10:00:00.000	2013-08-10 09:00:00.000
15	        2013-08-20 15:00:00.000	2013-10-24 22:00:00.000
25	        2013-08-25 06:00:00.000	2013-08-25 18:00:00.000
25	        2013-10-15 06:00:00.000	2013-10-15 18:00:00.000
26	        2013-10-15 07:00:00.000	2013-10-16 23:59:59.000
54	        2013-08-20 17:00:00.000	2013-08-21 06:00:00.000



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Luuk123
Starting Member

49 Posts

Posted - 11/29/2013 :  05:19:42  Show Profile  Reply with Quote
Hi visakh16,

That's the correct result.
I forgot to tell I want to use this query in a view, so using temp tables or table variables is not an option unfortunately.

Is there an alternative?

EDIT: khtan's query is almost what I want. I think the solution is to give all rows that overlap, belonging to each other, an overlap_identifier. Then group by person_id, overlap_identifier. I only don't know how to query this.

Edited by - Luuk123 on 11/29/2013 05:34:55
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/29/2013 :  05:48:09  Show Profile  Reply with Quote
quote:
Originally posted by Luuk123

Hi visakh16,

That's the correct result.
I forgot to tell I want to use this query in a view, so using temp tables or table variables is not an option unfortunately.

Is there an alternative?

EDIT: khtan's query is almost what I want. I think the solution is to give all rows that overlap, belonging to each other, an overlap_identifier. Then group by person_id, overlap_identifier. I only don't know how to query this.


Make it into a udf and call from your view.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Luuk123
Starting Member

49 Posts

Posted - 11/29/2013 :  05:58:15  Show Profile  Reply with Quote
Thanks! That worked.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/29/2013 :  06:01:22  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Luuk123
Starting Member

49 Posts

Posted - 11/29/2013 :  06:17:18  Show Profile  Reply with Quote
I'm sorry but I don't really understand the query.
It looks like it's updating row by row because you use variables but I don't see anything like a loop.

Can you explain? I would like to understand it!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/29/2013 :  06:39:36  Show Profile  Reply with Quote
quote:
Originally posted by Luuk123

I'm sorry but I don't really understand the query.
It looks like it's updating row by row because you use variables but I don't see anything like a loop.

Can you explain? I would like to understand it!


exactly
it makes use of clustered index and does row by row update by comparing the relevant column values. this method is called quirky update
more details refer this

http://visakhm.blogspot.com/2010/03/using-quirky-updates-to-develop-well.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Edited by - visakh16 on 11/29/2013 06:40:08
Go to Top of Page

Luuk123
Starting Member

49 Posts

Posted - 11/29/2013 :  08:48:48  Show Profile  Reply with Quote
I've found one problem, I can't create a temporary table inside a UDF.

I'm wondering if a PK-constraint on a table variable (person, date_from, date_to) would do the same as the Clustered index on the temp table. When I tried to create it I receive the error that the values of the three columns are not unique.

Any ideas?
Go to Top of Page

Ifor
Aged Yak Warrior

583 Posts

Posted - 11/29/2013 :  09:51:21  Show Profile  Reply with Quote
I suspect a khtan approach will be less hassle and run well enough.
Something like:

WITH OverLaps
AS
(
	SELECT T1.Person_id, T1.date_from, T1.date_to, T2.date_to AS date_to2
	FROM YourTable T1
		JOIN YourTable T2
			ON T1.person_id = T2.person_id
				AND T1.date_from <= T2.date_to
				AND T1.date_to >= T2.date_from
				AND T1.date_from <> T2.date_from
				AND T1.person_id <> T2.date_to
)
SELECT *
FROM YourTable
EXCEPT
SELECT Person_id, date_from, date_to
FROM OverLaps
UNION ALL
SELECT Person_id, MIN(date_from) AS date_from, MAX(date_to2) AS date_to
FROM OverLaps
GROUP BY person_id
ORDER BY person_id

Go to Top of Page

Luuk123
Starting Member

49 Posts

Posted - 11/29/2013 :  10:44:18  Show Profile  Reply with Quote
Thanks Ifor!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/30/2013 :  01:42:11  Show Profile  Reply with Quote
quote:
Originally posted by Luuk123

I've found one problem, I can't create a temporary table inside a UDF.

I'm wondering if a PK-constraint on a table variable (person, date_from, date_to) would do the same as the Clustered index on the temp table. When I tried to create it I receive the error that the values of the three columns are not unique.

Any ideas?


yep..pk would create a clustered index by default unless you explicitly suggest non clustered
However if your columns are not unique just create clustered index as a constraint and continue.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000