Leonzc's Avatar Selecting multiple start and end t

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)

Leonzc's Avatar Selecting multiple start and end t

Author

Topic

Leonzc
Starting Member

5 Posts

Posted - 2008-11-17 : 01:56:50

hi guys, i am currently creating a sql select statement for my project. The project would create a log data every minute if the user is away from the work station, thus the log would pretty much look like

Name DateTime
Alvin 11/09/08 11:30:00AM
Alvin 11/09/08 11:31:00AM
Alvin 11/09/08 11:32:00AM
Alvin 11/09/08 03:24:00AM
Alvin 11/09/08 03:25:00AM

I would need a select statement which could produce

Name StartTime EndTime Interval
Alvin 11:30:00AM 11:32:00AM 2
Alvin 03:24:00AM 03:25:00AM 1

could anyone help me with it?
=)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-17 : 02:02:23

[code]SELECT Name,MIN(DateTime) AS StartTime,Max(DateTime) AS EndTime,DATEDIFF(n,Min(DateTime),Max(DateTime)) AS Interval
FROM YourTable
GROUP BY Name,DATEADD(hh,DATEDIFF(hh,0,DateTime),0)[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-17 : 02:10:06

[code]Declare @Log table
(
[Name] varchar(20),
[DateTime] datetime
)
insert into @Log
select 'Alvin','11/09/08 11:30:00AM' UNION ALL
select 'Alvin','11/09/08 11:31:00AM' UNION ALL
select 'Alvin','11/09/08 11:32:00AM' UNION ALL
select 'Alvin','11/09/08 03:24:00AM' UNION ALL
select 'Alvin','11/09/08 03:25:00AM'

SELECT [Name],MIN([DateTime]) AS startTime,MAX([DateTime]) AS endtime, DATEDIFF(n,MIN([DateTime]),MAX([DateTime])) AS Interval
FROM @Log
GROUP BY [Name],DATEADD(hh,DATEDIFF(hh,0,[DateTime]),0)
ORDER BY DATEADD(hh,DATEDIFF(hh,0,[DateTime]),0)

Output
-----------------------------------
Name startTime endtime Interval
Alvin 2008-11-09 03:24:00.000 2008-11-09 03:25:00.000 1
Alvin 2008-11-09 11:30:00.000 2008-11-09 11:32:00.000 2[/code]

Leonzc
Starting Member

5 Posts

Posted - 2008-11-17 : 05:04:01

Oh my god i have researching for this sql statement for weeks,posted on multiple forum and you are the only one who provided an answer. Thanks alot man ~!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-17 : 05:04:32

Cheers

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-17 : 05:11:36

Add this sample data too, and try again

select 'Alvin','11/09/08 11:51:00AM' UNION ALL
select 'Alvin','11/09/08 11:52:00AM' UNION ALL

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-17 : 05:17:55

[code]Declare @Log table
(
[Name] varchar(20),
[DateTime] datetime,
primary key clustered
(
name,
datetime
),
seq int
)
insert into @Log (name, datetime)
select 'Alvin','11/09/08 11:30:00AM' UNION ALL
select 'Alvin','11/09/08 11:31:00AM' UNION ALL
select 'Alvin','11/09/08 11:32:00AM' UNION ALL
select 'Alvin','11/09/08 03:24:00AM' UNION ALL
select 'Alvin','11/09/08 11:51:00AM' UNION ALL
select 'Alvin','11/09/08 11:52:00AM' UNION ALL
select 'Alvin','11/09/08 03:25:00AM'

declare @name varchar(20), @datetime datetime, @seq int

select top 1 @name = name, @datetime = dateadd(minute, -1, datetime), @seq = 0
from @log
order by name, datetime

update @log
set @seq = seq = case when name = @name and datetime = dateadd(minute, 1, @datetime) then @seq else @seq + 1 end,
@name = name,
@datetime = datetime

SELECT min(Name),
MIN(DateTime),
MAX(DateTime),
COUNT(*)
FROM @Log
GROUP BY seq
order by seq[/code]

E 12°55'05.63"
N 56°04'39.26"

Leonzc
Starting Member

5 Posts

Posted - 2008-11-18 : 01:08:48

declare @name varchar(20), @datetime datetime, @seq int

select top 1 @name = name, @datetime = dateadd(minute, -1, datetime), @seq = 0
from @log
order by name, datetime

update @log
set @seq = seq = case when name = @name and datetime = dateadd(minute, 1, @datetime) then @seq else @seq + 1 end,
@name = name,
@datetime = datetime

SELECT min(Name),
MIN(DateTime),
MAX(DateTime),
COUNT(*)
FROM @Log
GROUP BY seq
order by seq

what's the use of the update of the log? would it affect my original table in any way?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-18 : 14:47:08

Yes. You will need a new column named seq and you also must have a clustered index over name and datetime.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources