Retrieving Data from next row

I have a little problem. An example of the data I work with is pasted below.
Qry_TestCtrack NAME STATUSTEXT ASSEMBLED LastAssembled
Bell B30 Dumper 1st Startup 03/03/2005 07:17:29 03/03/2005 07:17:29
Bell B30 Dumper Normal 03/03/2005 07:18:29 03/03/2005 07:18:29
Bell B30 Dumper Normal 03/03/2005 07:19:29 03/03/2005 07:19:29
Bell B30 Dumper Normal 03/03/2005 07:20:29 03/03/2005 07:20:29
Bell B30 Dumper Normal 03/03/2005 07:21:29 03/03/2005 07:21:29
Bell B30 Dumper Normal 03/03/2005 07:22:29 03/03/2005 07:22:29
Bell B30 Dumper Normal 03/03/2005 07:23:29 03/03/2005 07:23:29
Bell B30 Dumper Ignition off 03/03/2005 07:24:06 03/03/2005 07:24:06
The Query I'm making needs to give me the next row's ASSEMBLED as the
Previous row's LastAssembled so that I can calculate the time that was
carried out on that action. Anyone that can give me a helping hand? ^^
Thanks ^^Hi
Look at below example
create table tblConnection
(
StartTimeCon datetime not null,
EndTimeCon datetime not null
)
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
10:10','20000610 10:10')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
10:10','20000610 20:22')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
20:23','20000610 20:25')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
20:25','20000610 21:00')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
21:00','20000610 21:15')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
21:16','20000610 21:25')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
21:25','20000610 21:35')
SELECT
DISTINCT StartTimeCon
FROM tblConnection AS S1
WHERE ISNULL(
DATEDIFF(
minute,
(SELECT MAX(EndTimeCon)
FROM tblConnection AS S2
WHERE S2.EndTimeCon <= S1.StartTimeCon),S1.StartTimeCon),0) = 0
"Nightshade" <Nightshad3@.telkomsa.net> wrote in message
news:d18otn$1l6h$1@.newsreader02.ops.uunet.co.za...
> I have a little problem. An example of the data I work with is pasted
below.
> Qry_TestCtrack NAME STATUSTEXT ASSEMBLED LastAssembled
> Bell B30 Dumper 1st Startup 03/03/2005 07:17:29 03/03/2005 07:17:29
> Bell B30 Dumper Normal 03/03/2005 07:18:29 03/03/2005 07:18:29
> Bell B30 Dumper Normal 03/03/2005 07:19:29 03/03/2005 07:19:29
> Bell B30 Dumper Normal 03/03/2005 07:20:29 03/03/2005 07:20:29
> Bell B30 Dumper Normal 03/03/2005 07:21:29 03/03/2005 07:21:29
> Bell B30 Dumper Normal 03/03/2005 07:22:29 03/03/2005 07:22:29
> Bell B30 Dumper Normal 03/03/2005 07:23:29 03/03/2005 07:23:29
> Bell B30 Dumper Ignition off 03/03/2005 07:24:06 03/03/2005 07:24:06
>
> The Query I'm making needs to give me the next row's ASSEMBLED as the
> Previous row's LastAssembled so that I can calculate the time that was
> carried out on that action. Anyone that can give me a helping hand? ^^
> Thanks ^^
>|||Thank you for the response. I think me previous example was a bit confusing.
In my previous example I had the Column LastAssembled. That was my failure
to get it to show as I want it.
This is what I have to work with.
Qry_TestCtrack NAME STATUSTEXT ASSEMBLED
Bell B30 Dumper 1st Startup 03/03/2005 07:17:29
Bell B30 Dumper Normal 03/03/2005 07:18:29
Bell B30 Dumper Normal 03/03/2005 07:19:29
Bell B30 Dumper Normal 03/03/2005 07:20:29
Bell B30 Dumper Normal 03/03/2005 07:21:29
Bell B30 Dumper Normal 03/03/2005 07:22:29
Bell B30 Dumper Normal 03/03/2005 07:23:29
Bell B30 Dumper Ignition off 03/03/2005 07:24:06
Thus There's only 1 timestamp per event. But the one timestamp to the next
is the lenght of the action. Thus what I'm tyring, is to create another
tale, or just a function, wich would either place the 2nd line's Assembled
next to the 1s't lines assembled, so I can work out the difference between
them, or even directly subtract the first line from the 2nd line. End of the
day I need to caculate how much time was spent on each action, so I can just
add the totals of the actions.
Thanks again ^^
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eMNawEgKFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Hi
> Look at below example
> create table tblConnection
> (
> StartTimeCon datetime not null,
> EndTimeCon datetime not null
> )
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 10:10','20000610 10:10')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 10:10','20000610 20:22')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 20:23','20000610 20:25')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 20:25','20000610 21:00')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 21:00','20000610 21:15')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 21:16','20000610 21:25')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 21:25','20000610 21:35')
>
> SELECT
> DISTINCT StartTimeCon
> FROM tblConnection AS S1
> WHERE ISNULL(
> DATEDIFF(
> minute,
> (SELECT MAX(EndTimeCon)
> FROM tblConnection AS S2
> WHERE S2.EndTimeCon <= S1.StartTimeCon),S1.StartTimeCon),0) = 0
>
>
> "Nightshade" <Nightshad3@.telkomsa.net> wrote in message
> news:d18otn$1l6h$1@.newsreader02.ops.uunet.co.za...
> below.
>|||Hi
Does that mean the query I posted does not work?
Please post DDL + expected result.
"Nightshade" <Nightshad3@.telkomsa.net> wrote in message
news:d18r49$1lbg$1@.newsreader02.ops.uunet.co.za...
> Thank you for the response. I think me previous example was a bit
confusing.
> In my previous example I had the Column LastAssembled. That was my failure
> to get it to show as I want it.
> This is what I have to work with.
> Qry_TestCtrack NAME STATUSTEXT ASSEMBLED
> Bell B30 Dumper 1st Startup 03/03/2005 07:17:29
> Bell B30 Dumper Normal 03/03/2005 07:18:29
> Bell B30 Dumper Normal 03/03/2005 07:19:29
> Bell B30 Dumper Normal 03/03/2005 07:20:29
> Bell B30 Dumper Normal 03/03/2005 07:21:29
> Bell B30 Dumper Normal 03/03/2005 07:22:29
> Bell B30 Dumper Normal 03/03/2005 07:23:29
> Bell B30 Dumper Ignition off 03/03/2005 07:24:06
>
> Thus There's only 1 timestamp per event. But the one timestamp to the next
> is the lenght of the action. Thus what I'm tyring, is to create another
> tale, or just a function, wich would either place the 2nd line's Assembled
> next to the 1s't lines assembled, so I can work out the difference between
> them, or even directly subtract the first line from the 2nd line. End of
the
> day I need to caculate how much time was spent on each action, so I can
just
> add the totals of the actions.
> Thanks again ^^
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eMNawEgKFHA.3928@.TK2MSFTNGP09.phx.gbl...
0
07:17:29
>