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]

Go to Top of Page

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.
Go to Top of Page

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]

Go to Top of Page

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.
Go to Top of Page

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]

Go to Top of Page

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)
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

Luuk123
Yak Posting Veteran

52 Posts

Posted - 2013-11-29 : 05:58:15
Thanks! That worked.
Go to Top of Page

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
Go to Top of Page

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!
Go to Top of Page

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
Go to Top of Page

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?
Go to Top of Page

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

Go to Top of Page

Luuk123
Yak Posting Veteran

52 Posts

Posted - 2013-11-29 : 10:44:18
Thanks Ifor!
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -