OraFAQ Forum: SQL & PL/SQL » QUERY FOR HOW TO ACCESS NEXT ROW WHILE IN CURRENT ROW

Home » SQL & PL/SQL » SQL & PL/SQL » QUERY FOR HOW TO ACCESS NEXT ROW WHILE IN CURRENT ROW (oracle 9i / 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

QUERY FOR HOW TO ACCESS NEXT ROW WHILE IN CURRENT ROW [message #604799]

Sat, 04 January 2014 04:29

msaom
Messages: 8
Registered: January 2014

Junior Member

Hi, can you please make a oracle sql query for the following.

My Data in table dt0attlog:
ID staffid logdt login logout

01 aw101 01.12.11 01.12.11 08.30
01 aw101 01.12.11 01.12.11 10.30
01 aw101 01.12.11 01.12.11 11.40
01 aw101 01.12.11 01.12.11 14.30
01 aw101 01.12.11 01.12.11 15.00
01 aw101 01.12.11 01.12.11 17.45

01 aw102 01.12.11 01.12.11 19.20
01 aw102 01.12.11 01.12.11 20.30

01 aw102 01.12.11 01.12.11 21.50
01 aw102 01.12.11 01.12.11 23.10

01 aw102 01.12.11 01.12.11 23.20
01 aw102 02.12.11 02.12.11 07.00

01 aw102 02.12.11 02.12.11 18.00
01 aw102 03.12.11 03.12.11 04.30

01 aw103 01.12.11 01.12.11 09.40
01 aw104 01.12.11 01.12.11 18.00

My required output:

ID staffid logdt login logout diff-in-seconds

01 aw101 01.12.11 01.12.11 08.30 01.12.11 10.30 7200
01 aw101 01.12.11 01.12.11 11.40 01.12.11 14.30 10200
01 aw101 01.12.11 01.12.11 15.00 01.12.11 17.45 9900

01 aw102 01.12.11 01.12.11 19.20 01.12.11 20.30 4200
01 aw102 01.12.11 01.12.11 21.50 01.12.11 23.10 4800
01 aw102 01.12.11 01.12.11 23.20 02.12.11 07.00 27600

01 aw102 02.12.11 02.12.11 18.00 03.12.11 04.30 37800

01 aw103 01.12.11 01.12.11 09.40
01 aw104 01.12.11 01.12.11 18.00

Here record aw102 & dt. 01.12.11 / 02.12.11, it should shows the next day out in
the current date ie. 01.12.11.

thanks and await your great help please.

Attachment: qry.txt
(Size: 1.72KB, Downloaded 1192 times)

[Updated on: Sat, 04 January 2014 04:33]

Report message to a moderator

Re: QUERY FOR HOW TO ACCESS NEXT ROW WHILE IN CURRENT ROW [message #604805 is a reply to message #604799]

Sat, 04 January 2014 04:44

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Welcome to the forum. Please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read

You will need to provide a little more information before anyone can assist, note item 9 of the forum guide.

Report message to a moderator

Re: QUERY FOR HOW TO ACCESS NEXT ROW WHILE IN CURRENT ROW [message #604806 is a reply to message #604805]

Sat, 04 January 2014 04:49

msaom
Messages: 8
Registered: January 2014

Junior Member

thanks.

this my table structure:
create table dt0attlog
( id varchar2(2),
staffid varchar2(10),
logdt date,
login date,
logout date);

required formatted query is available as an attachment.

thanks

Report message to a moderator

Re: QUERY FOR HOW TO ACCESS NEXT ROW WHILE IN CURRENT ROW [message #604810 is a reply to message #604806]

Sat, 04 January 2014 04:57

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Properly formatted code looks like this:

CREATE TABLE dt0attlog
  (
     id      VARCHAR2(2),
     staffid VARCHAR2(10),
     logdt   DATE,
     login   DATE,
     logout  DATE
  );

which you would know if you had read the guidelines. Please read them now. And then provide the INSERT statements needed to complete your test case.

What code have you tried so far?

I would approach the problem in stages: begin by writing one query tht lists all the logons and another query lists the logouts. Then consider how you can join these two results to get the answer you need.

Report message to a moderator

Re: QUERY FOR HOW TO ACCESS NEXT ROW WHILE IN CURRENT ROW [message #604813 is a reply to message #604810]

Sat, 04 January 2014 05:20

msaom
Messages: 8
Registered: January 2014

Junior Member

well,
this is my base table which contains logs from the finger-print-device
create table dt0device (
deviceid varchar2(15),
staffid varchar2(10),
logdt date,
in_out_flag varchar2(3));

eg.

insert into dt0device values('01','aw101',to_date('01.12.2011 08:30','DD/MM/RRRR HH24.MI'),'IN');
insert into dt0device values('01','aw101',to_date('01.12.2011 10:30','DD/MM/RRRR HH24.MI'),'OUT');
insert into dt0device values('01','aw101',to_date('01.12.2011 11:40','DD/MM/RRRR HH24.MI'),'IN');
insert into dt0device values('01','aw101',to_date('01.12.2011 14:30','DD/MM/RRRR HH24.MI'),'OUT');
insert into dt0device values('01','aw101',to_date('01.12.2011 15:00','DD/MM/RRRR HH24.MI'),'IN');
insert into dt0device values('01','aw101',to_date('01.12.2011 17:45','DD/MM/RRRR HH24.MI'),'OUT');

insert into dt0device values('01','aw102',to_date('01.12.2011 19:20','DD/MM/RRRR HH24.MI'),'IN');
insert into dt0device values('01','aw102',to_date('01.12.2011 20:30','DD/MM/RRRR HH24.MI'),'OUT');
insert into dt0device values('01','aw102',to_date('01.12.2011 21:50','DD/MM/RRRR HH24.MI'),'IN');
insert into dt0device values('01','aw102',to_date('01.12.2011 23:10','DD/MM/RRRR HH24.MI'),'OUT');
insert into dt0device values('01','aw102',to_date('01.12.2011 23:20','DD/MM/RRRR HH24.MI'),'IN');
insert into dt0device values('01','aw102',to_date('02.12.2011 07:00','DD/MM/RRRR HH24.MI'),'OUT');
insert into dt0device values('01','aw102',to_date('02.12.2011 18:00','DD/MM/RRRR HH24.MI'),'IN');
insert into dt0device values('01','aw102',to_date('03.12.2011 04:30','DD/MM/RRRR HH24.MI'),'OUT');

insert into dt0device values('01','aw103',to_date('01.12.2011 09:40','DD/MM/RRRR HH24.MI'),'IN');
insert into dt0device values('01','aw104',to_date('01.12.2011 18:00','DD/MM/RRRR HH24.MI'),'OUT');

from this table(dt0device), i need to insert the record into another table dt0attlogdevice as
per the below format:
create table dt0attlogdevice
( staffid varchar2(10),
logdt date,
login date,
logout date);

insert by query :
insert into dt0attlogdevice as
select staffid,to_char(logdt,'DD/MM/RRRR') logdt,
decode(in_out_flag,'IN',to_char(logdt,'DD/MM/RRRR HH24:MI'),null) login,
decode(in_out_flag,'OUT',to_char(logdt,'DD/MM/RRRR HH24:MI'),null) logout
from dt0device;

output looks like:

staffid logdt login logout

aw101 01.12.11 01.12.11 08.30
aw101 01.12.11 01.12.11 10.30
aw101 01.12.11 01.12.11 11.40
aw101 01.12.11 01.12.11 14.30
aw101 01.12.11 01.12.11 15.00
aw101 01.12.11 01.12.11 17.45

aw102 01.12.11 01.12.11 19.20
aw102 01.12.11 01.12.11 20.30

aw102 01.12.11 01.12.11 21.50
aw102 01.12.11 01.12.11 23.10

aw102 01.12.11 01.12.11 23.20
aw102 02.12.11 02.12.11 07.00

aw102 02.12.11 02.12.11 18.00
aw102 03.12.11 03.12.11 04.30

aw103 01.12.11 01.12.11 09.40
aw104 01.12.11 01.12.11 18.00

from the above table, I want to generate ouput as below:

staffid logdt login logout diff-in-seconds

aw101 01.12.11 01.12.11 08.30 01.12.11 10.30 7200
aw101 01.12.11 01.12.11 11.40 01.12.11 14.30 10200
aw101 01.12.11 01.12.11 15.00 01.12.11 17.45 9900

aw102 01.12.11 01.12.11 19.20 01.12.11 20.30 4200
aw102 01.12.11 01.12.11 21.50 01.12.11 23.10 4800
aw102 01.12.11 01.12.11 23.20 02.12.11 07.00 27600

aw102 02.12.11 02.12.11 18.00 03.12.11 04.30 37800

aw103 01.12.11 01.12.11 09.40
aw104 01.12.11 01.12.11 18.00

Here record aw102 & dt. 01.12.11 / 02.12.11, it should shows the next day out in
the current date ie. 01.12.11.

I dont know how to make query for the above output.

Attachment: nw-qry.txt
(Size: 4.01KB, Downloaded 1179 times)

Report message to a moderator

Re: QUERY FOR HOW TO ACCESS NEXT ROW WHILE IN CURRENT ROW [message #604817 is a reply to message #604813]

Sat, 04 January 2014 05:31

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Thank you for the INSERT statements.
However, I have twice asked you to read the forum guidelines, and to format your code properly with [code] tags. Please do so in future, or I shall say "good bye".

I have also suggested one approach to the problem. Did you try it? Or do you think the approach is wrong? Or do you not understand? Or did you decide that trying something yourself was too much work and you would prefer me to do it all for you?

Report message to a moderator

Re: QUERY FOR HOW TO ACCESS NEXT ROW WHILE IN CURRENT ROW [message #604819 is a reply to message #604817]

Sat, 04 January 2014 05:39

msaom
Messages: 8
Registered: January 2014

Junior Member

thanks.
I am new to online forum.

even I tried like

select staffid,logdt,login,lead(logout,1) over (partition by staffid,logdt order by staffid,logdt) logout
from dt0attlogdevice
where login is not null
order by logdt,staffid

not working.
if you can help by restructing the query, really I would be very much glad.

Report message to a moderator

Re: QUERY FOR HOW TO ACCESS NEXT ROW WHILE IN CURRENT ROW [message #604820 is a reply to message #604817]

Sat, 04 January 2014 05:42

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

And one more hint: your conversion of dates to chars as you insert into dt0attlogdevice is disastrous. This is what I get:

orclz> select * from dt0attlogdevice
  2  ;

STAFFID    LOGDT               LOGIN               LOGOUT
---------- ------------------- ------------------- -------------------
aw101      0001-12-20 11:00:00 0001-12-20 11:08:30
aw101      0001-12-20 11:00:00                     0001-12-20 11:10:30
aw101      0001-12-20 11:00:00 0001-12-20 11:11:40
aw101      0001-12-20 11:00:00                     0001-12-20 11:14:30
aw101      0001-12-20 11:00:00 0001-12-20 11:15:00
aw101      0001-12-20 11:00:00                     0001-12-20 11:17:45
aw102      0001-12-20 11:00:00 0001-12-20 11:19:20
aw102      0001-12-20 11:00:00                     0001-12-20 11:20:30
aw102      0001-12-20 11:00:00 0001-12-20 11:21:50
aw102      0001-12-20 11:00:00                     0001-12-20 11:23:10
aw102      0001-12-20 11:00:00 0001-12-20 11:23:20
aw102      0002-12-20 11:00:00                     0002-12-20 11:07:00
aw102      0002-12-20 11:00:00 0002-12-20 11:18:00
aw102      0003-12-20 11:00:00                     0003-12-20 11:04:30
aw103      0001-12-20 11:00:00 0001-12-20 11:09:40
aw104      0001-12-20 11:00:00                     0001-12-20 11:18:00

16 rows selected.

Report message to a moderator

Re: QUERY FOR HOW TO ACCESS NEXT ROW WHILE IN CURRENT ROW [message #604825 is a reply to message #604820]

Sat, 04 January 2014 05:55

msaom
Messages: 8
Registered: January 2014

Junior Member

thanks.
I tried and got the same output. please check the attachment

Attachment: sql-output.txt
(Size: 4.36KB, Downloaded 1001 times)

Report message to a moderator

Re: QUERY FOR HOW TO ACCESS NEXT ROW WHILE IN CURRENT ROW [message #604828 is a reply to message #604825]

Sat, 04 January 2014 06:00

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

You appear to be ignoring everything I say. No point in me trying any further. Good bye.

Though it may interest you to know that having de-bugged your inserts, my solution works perfectly, without using any analytic functions. Just a simple join and filter:

orclz> /

STAFFID    LIN                 LOUT                       DUR
---------- ------------------- ------------------- ----------
aw101      2011-12-01 08:30:00 2011-12-01 10:30:00       7200
aw101      2011-12-01 11:40:00 2011-12-01 14:30:00      10200
aw101      2011-12-01 15:00:00 2011-12-01 17:45:00       9900
aw102      2011-12-01 19:20:00 2011-12-01 20:30:00       4200
aw102      2011-12-01 21:50:00 2011-12-01 23:10:00       4800
aw102      2011-12-01 23:20:00 2011-12-02 07:00:00      27600
aw102      2011-12-02 18:00:00 2011-12-03 04:30:00      37800
aw103      2011-12-01 09:40:00

8 rows selected.

orclz>

[Updated on: Sat, 04 January 2014 06:00]

Report message to a moderator

Re: QUERY FOR HOW TO ACCESS NEXT ROW WHILE IN CURRENT ROW [message #604829 is a reply to message #604828]

Sat, 04 January 2014 06:04

msaom
Messages: 8
Registered: January 2014

Junior Member

really I am sorry, I could not understand where I am mis-understood your points.

I am sorry

Report message to a moderator

Re: QUERY FOR HOW TO ACCESS NEXT ROW WHILE IN CURRENT ROW [message #604830 is a reply to message #604829]

Sat, 04 January 2014 06:06

msaom
Messages: 8
Registered: January 2014

Junior Member

really thanks for your effort.
if you can provide the correct query.it would be much great help.

Report message to a moderator

Re: QUERY FOR HOW TO ACCESS NEXT ROW WHILE IN CURRENT ROW [message #604831 is a reply to message #604830]

Sat, 04 January 2014 06:10

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

All right, one more time. But you have to do some work too. Either that or pay for my time as a consultant.

First, debug your insert query. Do NOT convert the dates to chars. There is one other trivial syntax error, too.
Second write two queries: one to list all the logons, the other to list all the logouts.
Third, join these two result sets together.
Fourth, look at the joined rows, and add a filter that will remove the ones you do not want.

And use [code] tags when you paste anything to te forum.

Report message to a moderator

Re: QUERY FOR HOW TO ACCESS NEXT ROW WHILE IN CURRENT ROW [message #604833 is a reply to message #604831]

Sat, 04 January 2014 06:19

msaom
Messages: 8
Registered: January 2014

Junior Member

Now I understood the procedures to post a question on forum.
Next, I will try to work out my level best according to your suggestion.
Next, regarding consultant fee, I need to check with my friend who is newly joined with a company. I am just doing some generous help to him.

any way thanks for your valuable suggestion / moral support to people like me.
keep it up.

Report message to a moderator

Previous Topic:	Fetch one row with exact that value with different data type
Next Topic:	Need Static SQL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 00:49:05 CDT 2024