check for continous rows with time entries

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 2005 Forums

Transact-SQL (2005)

check for continous rows with time entries

Author

Topic

mickdunde
Starting Member

4 Posts

Posted - 2007-11-01 : 03:05:51

Hi there

I have a table which looks like follows:
Date | TimeStart | TimeEnd | Activity
1.1.2007 | 07:30 | 09:00 | a
1.1.2007 | 09:00 | 10:00 | b
1.1.2007 | 10:15 | 12:00 | c

I'm looking now for a query which outputs like the following:
1.1.2007 | 07:30 | 10:00
1.1.2007 | 10:15 | 12:00

First two rows are "grouped" together as b follows inmediately after a, but between b and c there is a break between, so new row.

Min and Max, grouped by date does not work here, as then SQL does not recognize the break in between.

Also the following does not work:

select * from tbl_test t
where t.TimeEnd not in (select t2.TimeStart from tbl_test t2 where t2.Date = t.Date)

Anyone can help?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-11-01 : 05:08:07

select * from tbl_test t1
left join tbl_test t2 on t1.date = t2.date and t1.timeend = t2.timestart
where t2.timestart is null
--maybe with this as well
union
select * from tbl_test t1
left join tbl_test t2 on t1.date = t2.date and t1.timestart = t2.timeend
where t2.timestart is null

mickdunde
Starting Member

4 Posts

Posted - 2007-11-01 : 06:17:44

Thanks Andrew
But this only works partially, if I have like three or more in entries per period, say:
Date | TimeStart | TimeEnd | Activity
1.1.2007 | 07:30 | 09:00 | a
1.1.2007 | 09:00 | 10:00 | b
1.1.2007 | 10:00 | 11:00 | c
1.1.2007 | 11:00 | 12:00 | d
1.1.2007 | 12:30 | 14:00 | e
then only the first two are recognized, from 07:30 to 10:00

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-01 : 06:20:42

quote:
Originally posted by mickdunde

Thanks Andrew
But this only works partially, if I have like three or more in entries per period, say:
Date | TimeStart | TimeEnd | Activity
1.1.2007 | 07:30 | 09:00 | a
1.1.2007 | 09:00 | 10:00 | b
1.1.2007 | 10:00 | 11:00 | c
1.1.2007 | 11:00 | 12:00 | d
1.1.2007 | 12:30 | 14:00 | e
then only the first two are recognized, from 07:30 to 10:00

please also post the corresponding required result

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

mickdunde
Starting Member

4 Posts

Posted - 2007-11-01 : 06:44:55

sure
result of the above
1.1.2007 | 07:30 | 12:00
1.1.2007 | 12:30 | 14:00

Because activity a to d are done straight without a break, but between end of activity d and start of e there is a break of 30 min.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-11-01 : 07:36:26

I'd take a guess and say that implementing this in a "sophisticated" front-end rather than in raw T-SQL would be simpler. You're looking at a recursive query otherwise....which is difficult and resource intensive...and may not work.

mickdunde
Starting Member

4 Posts

Posted - 2007-11-01 : 08:01:44

I came to a similar conclusion after some hours without success. But thanks for your help!

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-11-01 : 09:57:36

Hi Try this,

DECLARE @T TABLE ( Date DATETIME, TimeStart DATETIME, TimeEnd DATETIME, Activity CHAR(1) )

INSERT INTO @T
SELECT '1.1.2007', '07:30', '09:00', 'a' UNION ALL
SELECT '1.1.2007', '09:00', '10:00', 'b' UNION ALL
SELECT '1.1.2007', '10:00', '11:00', 'c' UNION ALL
SELECT '1.1.2007', '11:00', '12:00', 'd' UNION ALL
SELECT '1.1.2007', '12:30', '14:00', 'e'

--SELECT * FROM @T

DECLARE @TimeEnd TABLE(ID INT IDENTITY(1,1), Date DATETIME, TimeEnd DATETIME)
INSERT INTO @TimeEnd
SELECT DISTINCT T.Date, T.TimeEnd
FROM @T T
LEFT JOIN @T T1 ON T1.TimeStart = T.TimeEnd
WHERE T1.TimeStart IS NULL

--SELECT * FROM @TimeEnd

DECLARE @TimeFrom TABLE(ID INT IDENTITY(1,1), Date DATETIME, TimeStart DATETIME)
INSERT INTO @TimeFrom
SELECT DISTINCT T.Date, T.TimeStart
FROM @T T
LEFT JOIN @T T1 ON T1.TimeEnd = T.TimeStart
WHERE T1.TimeEnd IS NULL

--SELECT * FROM @TimeFrom

SELECT T.Date, T.TimeStart, T1.TimeEnd
FROM @TimeFrom T
INNER JOIN @TimeEnd T1 ON T.ID = T1.ID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources