First and last date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

First and last date

Author

Topic

Luuk123
Yak Posting Veteran

52 Posts

Posted - 2013-11-29 : 02:53:54

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!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-11-29 : 02:57:23

[code]
SELECT person_id, MIN(date_from), MAX(date_to)
FROM yourtable
GROUP BY person_id
[/code]

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

KH
[spoiler]Time is always against us[/spoiler]

Luuk123
Yak Posting Veteran

52 Posts

Posted - 2013-11-29 : 02:59:31

quote:
Originally posted by khtan

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

KH
[spoiler]Time is always against us[/spoiler]

Thanks for your reply but I forgot something in my question so I updated it.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-11-29 : 03:01:29

so what is the expected result with that new condition added ?

KH
[spoiler]Time is always against us[/spoiler]

Luuk123
Yak Posting Veteran

52 Posts

Posted - 2013-11-29 : 03:03:58

[code]
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
[/code]
The result of person 26 is grouped because there's an overlap in the dates. This is the result I need.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-11-29 : 03:14:39

[code]; 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
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Luuk123
Yak Posting Veteran

52 Posts

Posted - 2013-11-29 : 04:08:43

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)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-29 : 04:33:21

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

Luuk123
Yak Posting Veteran

52 Posts

Posted - 2013-11-29 : 05:19:42

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-29 : 05:48:09

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

Luuk123
Yak Posting Veteran

52 Posts

Posted - 2013-11-29 : 05:58:15

Thanks! That worked.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-29 : 06:01:22

welcome

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

Luuk123
Yak Posting Veteran

52 Posts

Posted - 2013-11-29 : 06:17:18

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!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-29 : 06:39:36

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

Luuk123
Yak Posting Veteran

52 Posts

Posted - 2013-11-29 : 08:48:48

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?

Ifor
Aged Yak Warrior

700 Posts

Posted - 2013-11-29 : 09:51:21

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

Luuk123
Yak Posting Veteran

52 Posts

Posted - 2013-11-29 : 10:44:18

Thanks Ifor!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-30 : 01:42:11

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources