Home » SQL & PL/SQL » SQL & PL/SQL » query help / analytic functions / lead+lag
query help / analytic functions / lead+lag [message #208009] Thu, 07 December 2006 16:29 Go to next message
foothillbiker
Messages: 3
Registered: December 2006
Junior Member
All,

I have a table like this:
Name                  Null?    Type
--------------------- ------ -----------------
ID                            NUMBER  -- pk, ascending
NAME                          NVARCHAR2(255)
LOGON                         DATE
LOGOFF                        DATE[/font]

SELECT * FROM mytable WHERE name = 'JBLOW';

NAME          LOGON                    LOGOFF
------------- ---------------------- ----------------------
JBLOW         18-Nov-2006 03:22:12
JBLOW         04-Nov-2006 01:25:51
JBLOW                                04-Nov-2006 04:53:01
JBLOW         26-Sep-2006 00:00:10
JBLOW                                18-Nov-2006 03:01:37
JBLOW         07-Nov-2006 18:10:16


I want to match up logon/logoffs, like this:

NAME          LON                    LOFF
------------- ---------------------- ----------------------
JBLOW         18-Nov-2006 03:22:12
JBLOW         04-Nov-2006 01:25:51   04-Nov-2006 04:53:01
JBLOW         26-Sep-2006 00:00:10   18-Nov-2006 03:01:37
JBLOW         07-Nov-2006 18:10:16[/font]

Not every logon (or logoff) will have a mate.

I have tried the query below -- it's close, but no cigar:

  1   select
  2   name,
  3   logon,
  4   lead(logoff, 1, null)
  5   over
  6   (partition by name  order by id nulls last ) logoff
  7   from my_table
  8*  where name = 'JBLOW'

NAME          LOGON                    LOGOFF
------------- ---------------------- ----------------------
JBLOW     26-Sep-2006 00:00:10
JBLOW     04-Nov-2006 01:25:51   04-Nov-2006 04:53:01
JBLOW
JBLOW     07-Nov-2006 18:10:16   18-Nov-2006 03:01:37
JBLOW
JBLOW     18-Nov-2006 03:22:12



The problem is those two (2) rows with NULL for logon and logoff

Any help appreciated.

Thanks.
cd.



[Updated on: Thu, 07 December 2006 16:39]

Report message to a moderator

Re: query help / analytic functions / lead+lag [message #208011 is a reply to message #208009] Thu, 07 December 2006 16:54 Go to previous messageGo to next message
Admin@Lightsql.Com
Messages: 36
Registered: November 2006
Member

Just curious to try this ( or similar, I have not tried this query)

select
name,
logon,
lead(logoff, 1, null)
over
(partition by name order by id nulls last ) logoff
from my_table
where name = 'JBLOW'
and logon is not null
and ( lead(logoff, 1, null) over (partition by name order by id nulls last) is not null )

I am just making an and condition with login is not null and logoff is not null so that the rows will not be fetched.

If it works, you are lucky.

Jay
Fun Programming with Oracle & Visual C++, Author of http://www.lightsql.com.
Re: query help / analytic functions / lead+lag [message #208014 is a reply to message #208011] Thu, 07 December 2006 17:20 Go to previous messageGo to next message
foothillbiker
Messages: 3
Registered: December 2006
Junior Member
Jay,

thanks for the effort. The trouble is that if we have

WHERE LOGON IS NOT NULL on the main query, then the LOGOFF records are excluded from the whole thing (I had tried it before posting Sad )

Thanks though...

cd
Re: query help / analytic functions / lead+lag [message #208016 is a reply to message #208014] Thu, 07 December 2006 17:32 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
SQL> select * from (select
  2  id,
  3  name,
  4  logon,
  5  lead(logoff)
  6  over
  7  (partition by id order by logon) logoff
  8  from logon_off
  9  where name = 'JBLOW')
 10  where logon is not null;

        ID NAME                 LOGON                LOGOFF
---------- -------------------- -------------------- --------------------
         1 JBLOW                18-NOV-2006 03:22:12
         2 JBLOW                04-NOV-2006 01:25:51 04-NOV-2006 04:53:01
         3 JBLOW                26-SEP-2006 00:00:10 18-NOV-2006 03:01:37
         4 JBLOW                07-NOV-2006 18:10:16
Re: query help / analytic functions / lead+lag [message #211311 is a reply to message #208016] Wed, 27 December 2006 22:23 Go to previous messageGo to next message
foothillbiker
Messages: 3
Registered: December 2006
Junior Member
All,

just fyi -- here is what works ---

  1  select *
  2  from (
  3  select
  4  name, lon, lead(loff) over (partition by name order by id) logoff
  5  from logons_view
  6  )
  7  where (lon is not null or logoff is not null)
  8* and name = 'JBLOW'
SQL> /

NAME                 LON                  LOGOFF
-------------------- -------------------- --------------------
JBLOW                26-Sep-2006 01:14:53
JBLOW                04-Nov-2006 01:24:54 04-Nov-2006 04:53:01
JBLOW                                     18-Nov-2006 03:01:37
JBLOW                18-Nov-2006 03:23:02
JBLOW                12-Dec-2006 16:43:03 12-Dec-2006 17:20:48
JBLOW                15-Dec-2006 20:09:03 15-Dec-2006 21:11:03
JBLOW                15-Dec-2006 23:25:03 15-Dec-2006 23:55:53

7 rows selected.
Re: query help / analytic functions / lead+lag [message #211379 is a reply to message #211311] Thu, 28 December 2006 05:16 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Just curious how you could have a LOGOFF without a corresponding LOGON ?
Re: query help / analytic functions / lead+lag [message #216178 is a reply to message #211379] Thu, 25 January 2007 07:53 Go to previous message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
select * from (
select name,logon,lead(logoff,1)
over(partition by name order by id ) as logoff
from test1
)where logoff is not null or logonon is not null

it aslo working
Previous Topic: Update Issue..
Next Topic: Duplicate Rows..
Goto Forum:
  


Current Time: Sat Dec 10 22:17:50 CST 2016

Total time taken to generate the page: 0.06841 seconds