need help with SQL query

[angelasg]

Here is sample data I'm working with:

ID ShiftDate SegTime
99 5/2/2005 5/2/2005 1:00:00 PM
99 5/2/2005 5/2/2005 1:04:00 PM
99 5/2/2005 5/2/2005 1:43:00 PM
99 5/2/2005 5/2/2005 2:15:00 PM
99 5/2/2005 5/2/2005 2:30:00 PM
99 5/2/2005 5/2/2005 5:00:00 PM
99 5/3/2005 5/3/2005 1:00:00 PM
99 5/3/2005 5/3/2005 1:05:00 PM
99 5/3/2005 5/3/2005 2:15:00 PM
99 5/3/2005 5/3/2005 2:30:00 PM
99 5/3/2005 5/3/2005 5:00:00 PM

Here is the result I'm trying to accomplish:

ID ShiftDate Segstart SegStop
99 5/2/2005 5/2/2005 1:00:00 PM 5/2/2005 1:04:00 PM
99 5/2/2005 5/2/2005 1:04:00 PM 5/2/2005 1:43:00 PM
99 5/2/2005 5/2/2005 1:43:00 PM 5/2/2005 2:15:00 PM
99 5/2/2005 5/2/2005 2:15:00 PM 5/2/2005 2:30:00 PM
99 5/2/2005 5/2/2005 2:30:00 PM 5/2/2005 5:00:00 PM
99 5/3/2005 5/3/2005 1:00:00 PM 5/3/2005 1:05:00 PM
99 5/3/2005 5/3/2005 1:05:00 PM 5/3/2005 2:15:00 PM
99 5/3/2005 5/3/2005 2:15:00 PM 5/3/2005 2:30:00 PM
99 5/3/2005 5/3/2005 2:30:00 PM 5/3/2005 5:00:00 PM

So each record represents a start time and the next record is the end
time. Then that end time becomes the start time of the next record and
so on. Then it starts over with the next shift date. The number of
records vary with each shift date.

Any help building this SQL query will be appreciated. Thanks.

[AK]

although there is a pure SQL solution, solving such problems is a snap
using OLAP aka analytical functions. On SQL 2000 they are not
available, but there is a substitute

---- set up data
create table t(n int, d smalldatetime)
insert into t values(1, '1/1/2005')
insert into t values(1, '1/2/2005')
insert into t values(1, '1/9/2005')
insert into t values(1, '1/14/2005')
insert into t values(2, '1/21/2005')
insert into t values(2, '1/23/2005')
go
create function dbo.t_with_rn()
returns @a table(n int, d smalldatetime, rn int)
as
begin
declare a_cur cursor
local
for select n,d from t order by n,d

declare @n int
declare @d smalldatetime
declare @rn int

set @rn=0

open a_cur

fetch next from a_cur into @n, @d

while @@fetch_status=0
begin
set @rn = @rn + 1

insert into @a values(@n, @d, @rn)

fetch next from a_cur into @n, @d
end
return
end
go
select * from dbo.t_with_rn()
---------this function will assign consecutive numbers to rows
n d rn

----------- ------------------------------------------------------
-----------
1 2005-01-01 00:00:00 1
1 2005-01-02 00:00:00 2
1 2005-01-09 00:00:00 3
1 2005-01-14 00:00:00 4
2 2005-01-21 00:00:00 5
2 2005-01-23 00:00:00 6

go
select left_ends.n, left_ends.d left_end, right_ends.d right_end from
dbo.t_with_rn() left_ends,
dbo.t_with_rn() right_ends
where left_ends.n = right_ends.n
and left_ends.rn = right_ends.rn - 1
go
---------- here are the intervals
n left_end
right_end
----------- ------------------------------------------------------
------------------------------------------------------
1 2005-01-01 00:00:00
2005-01-02 00:00:00
1 2005-01-02 00:00:00
2005-01-09 00:00:00
1 2005-01-09 00:00:00
2005-01-14 00:00:00
2 2005-01-21 00:00:00
2005-01-23 00:00:00
---- clean up
drop function dbo.t_with_rn
go
select * from t
drop table t

[Erland Sommarskog]

(an******@comcast.net) writes:

So each record represents a start time and the next record is the end
time. Then that end time becomes the start time of the next record and
so on. Then it starts over with the next shift date. The number of
records vary with each shift date.

Any help building this SQL query will be appreciated. Thanks.

Query below. One thing seems funny to me, and that is I would expect
shifts to last past midnight. But in such case you need find another
criteria for when a row is not the start of a new shift as well.

create table t(id int not null,
shiftdate datetime not null,
shifttime datetime NOT NULL)

insert t(id, shiftdate, shifttime)
SELECT 99, '5/2/2005', '5/2/2005 1:00:00 PM' UNION ALL
SELECT 99, '5/2/2005', '5/2/2005 1:04:00 PM' UNION ALL
SELECT 99, '5/2/2005', '5/2/2005 1:43:00 PM' UNION ALL
SELECT 99, '5/2/2005', '5/2/2005 2:15:00 PM' UNION ALL
SELECT 99, '5/2/2005', '5/2/2005 2:30:00 PM' UNION ALL
SELECT 99, '5/2/2005' , '5/2/2005 5:00:00 PM' UNION ALL
SELECT 99, '5/3/2005', '5/3/2005 1:00:00 PM' UNION ALL
SELECT 99, '5/3/2005', '5/3/2005 1:05:00 PM' UNION ALL
SELECT 99, '5/3/2005', '5/3/2005 2:15:00 PM' UNION ALL
SELECT 99, '5/3/2005', '5/3/2005 2:30:00 PM' UNION ALL
SELECT 99, '5/3/2005', '5/3/2005 5:00:00 PM'
go
SELECT id, shifttime, shiftend
FROM (SELECT t.id, t.shifttime,
shiftend = (SELECT MIN(t2.shifttime)
FROM t t2
WHERE t2.id = t.id
AND t2.shiftdate = t.shiftdate
AND t2.shifttime > t.shifttime)
FROM t) AS x
WHERE shiftend IS NOT NULL
ORDER BY id, shifttime
go
drop table t


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[angelasg]

Erland,

There are shifts that end past midnight. But the shiftdate always
represents the date the shift started. In the original sample data, ID
99 represents an employee. ShiftDate 5/2/2005 represents the entire
work day. The segtimes are start/end times of different segments of
the day such as taking cust calls, training, break, lunch, etc. So Emp
99 worked from 1p-5p on 5/2. She could have very well ended her shift
past midnight, but the shift date would have been the same.

[angelasg]

AK,

Thanks for the response. I am new to SQL and your code was a little
over my head but I tried to work with it. I ended up creating 3 stored
procedures and one custom function, but I don't know how to call the
procedures to get the data. Did I do it right and what else do I need
to do?

Thanks.

AK wrote:

although there is a pure SQL solution, solving such problems is a snap
using OLAP aka analytical functions. On SQL 2000 they are not
available, but there is a substitute

---- set up data
create table t(n int, d smalldatetime)
insert into t values(1, '1/1/2005')
insert into t values(1, '1/2/2005')
insert into t values(1, '1/9/2005')
insert into t values(1, '1/14/2005')
insert into t values(2, '1/21/2005')
insert into t values(2, '1/23/2005')
go
create function dbo.t_with_rn()
returns @a table(n int, d smalldatetime, rn int)
as
begin
declare a_cur cursor
local
for select n,d from t order by n,d

declare @n int
declare @d smalldatetime
declare @rn int

set @rn=0

open a_cur

fetch next from a_cur into @n, @d

while @@fetch_status=0
begin
set @rn = @rn + 1

insert into @a values(@n, @d, @rn)

fetch next from a_cur into @n, @d
end
return
end
go
select * from dbo.t_with_rn()
---------this function will assign consecutive numbers to rows
n d rn

----------- ------------------------------------------------------
-----------
1 2005-01-01 00:00:00 1
1 2005-01-02 00:00:00 2
1 2005-01-09 00:00:00 3
1 2005-01-14 00:00:00 4
2 2005-01-21 00:00:00 5
2 2005-01-23 00:00:00 6

go
select left_ends.n, left_ends.d left_end, right_ends.d right_end from
dbo.t_with_rn() left_ends,
dbo.t_with_rn() right_ends
where left_ends.n = right_ends.n
and left_ends.rn = right_ends.rn - 1
go
---------- here are the intervals
n left_end
right_end
----------- ------------------------------------------------------
------------------------------------------------------
1 2005-01-01 00:00:00
2005-01-02 00:00:00
1 2005-01-02 00:00:00
2005-01-09 00:00:00
1 2005-01-09 00:00:00
2005-01-14 00:00:00
2 2005-01-21 00:00:00
2005-01-23 00:00:00
---- clean up
drop function dbo.t_with_rn
go
select * from t
drop table t

[angelasg]

AK,

Thanks for the response. I am new to SQL and your code was a little
over my head but I tried to work with it. I ended up creating 3 stored
procedures and one custom function, but I don't know how to call the
procedures to get the data. Did I do it right and what else do I need
to do?

Thanks.

AK wrote:

although there is a pure SQL solution, solving such problems is a snap
using OLAP aka analytical functions. On SQL 2000 they are not
available, but there is a substitute

---- set up data
create table t(n int, d smalldatetime)
insert into t values(1, '1/1/2005')
insert into t values(1, '1/2/2005')
insert into t values(1, '1/9/2005')
insert into t values(1, '1/14/2005')
insert into t values(2, '1/21/2005')
insert into t values(2, '1/23/2005')
go
create function dbo.t_with_rn()
returns @a table(n int, d smalldatetime, rn int)
as
begin
declare a_cur cursor
local
for select n,d from t order by n,d

declare @n int
declare @d smalldatetime
declare @rn int

set @rn=0

open a_cur

fetch next from a_cur into @n, @d

while @@fetch_status=0
begin
set @rn = @rn + 1

insert into @a values(@n, @d, @rn)

fetch next from a_cur into @n, @d
end
return
end
go
select * from dbo.t_with_rn()
---------this function will assign consecutive numbers to rows
n d rn

----------- ------------------------------------------------------
-----------
1 2005-01-01 00:00:00 1
1 2005-01-02 00:00:00 2
1 2005-01-09 00:00:00 3
1 2005-01-14 00:00:00 4
2 2005-01-21 00:00:00 5
2 2005-01-23 00:00:00 6

go
select left_ends.n, left_ends.d left_end, right_ends.d right_end from
dbo.t_with_rn() left_ends,
dbo.t_with_rn() right_ends
where left_ends.n = right_ends.n
and left_ends.rn = right_ends.rn - 1
go
---------- here are the intervals
n left_end
right_end
----------- ------------------------------------------------------
------------------------------------------------------
1 2005-01-01 00:00:00
2005-01-02 00:00:00
1 2005-01-02 00:00:00
2005-01-09 00:00:00
1 2005-01-09 00:00:00
2005-01-14 00:00:00
2 2005-01-21 00:00:00
2005-01-23 00:00:00
---- clean up
drop function dbo.t_with_rn
go
select * from t
drop table t

[Erland Sommarskog]

(an******@comcast.net) writes:

There are shifts that end past midnight. But the shiftdate always
represents the date the shift started. In the original sample data, ID
99 represents an employee. ShiftDate 5/2/2005 represents the entire
work day. The segtimes are start/end times of different segments of
the day such as taking cust calls, training, break, lunch, etc. So Emp
99 worked from 1p-5p on 5/2. She could have very well ended her shift
past midnight, but the shift date would have been the same.

OK. In that case the query would work. I was thinking that having two
columns were redudant, since the date appears in both, but with your
explanation, I see why the separate date column is needed.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[AK]

Angel,

I would use Erland's solution - it is way simpler and more practical