Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2014
    Posts
    2

    Post Unanswered: Help with time between events

    I have the database like this ...

    ABC 1/1/12 Reg Day
    ABC 1/2/12 Sick Day
    .
    .
    .
    ABC 1/15/12 Sick Day
    ABC 1/16/12 Reg Day

    XYZ 1/1/12 Reg Day
    XYZ 1/2/12 Sick Day
    XYZ 1/3/12 Reg Day

    I want a query that will return

    ABC 1/2/12 13 days (Sick)
    XYZ 1/2/12 1 day (Sick)

    Is there a way I can run this query?

    Thanks!

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Here's one option:
    Code:
    SQL> alter session set nls_date_format = 'mm/dd/yyyy';
    
    Session altered.
    
    SQL> select * from test order by ename, datum;
    
    ENAME DATUM      WHAT
    ----- ---------- ----------
    ABC   01/01/2012 Reg
    ABC   01/02/2012 Sick
    ABC   01/03/2012 Sick
    ABC   01/04/2012 Sick
    ABC   01/05/2012 Sick
    ABC   01/06/2012 Sick
    ABC   01/07/2012 Sick
    ABC   01/08/2012 Sick
    ABC   01/09/2012 Sick
    ABC   01/10/2012 Sick
    ABC   01/11/2012 Sick
    ABC   01/12/2012 Sick
    ABC   01/13/2012 Sick
    ABC   01/14/2012 Sick
    ABC   01/15/2012 Sick
    ABC   01/16/2012 Reg
    XYZ   01/01/2012 Reg
    XYZ   01/02/2012 Sick
    XYZ   01/03/2012 Reg
    
    19 rows selected.
    
    SQL> select ename, min(datum), count(*), what
      2  from test
      3  where what = 'Sick'
      4  group by ename, what
      5  order by ename;
    
    ENAME MIN(DATUM)   COUNT(*) WHAT
    ----- ---------- ---------- ----------
    ABC   01/02/2012         14 Sick
    XYZ   01/02/2012          1 Sick
    
    SQL>
    (Note that your "required output" is wrong; ABC was sick 14 days, not 13).

  3. #3
    Join Date
    Nov 2014
    Posts
    2

    Smile

    Thanks a lot for your reply - will try that solution!



    Quote Originally Posted by Littlefoot View Post
    Here's one option:
    Code:
    SQL> alter session set nls_date_format = 'mm/dd/yyyy';
    
    Session altered.
    
    SQL> select * from test order by ename, datum;
    
    ENAME DATUM      WHAT
    ----- ---------- ----------
    ABC   01/01/2012 Reg
    ABC   01/02/2012 Sick
    ABC   01/03/2012 Sick
    ABC   01/04/2012 Sick
    ABC   01/05/2012 Sick
    ABC   01/06/2012 Sick
    ABC   01/07/2012 Sick
    ABC   01/08/2012 Sick
    ABC   01/09/2012 Sick
    ABC   01/10/2012 Sick
    ABC   01/11/2012 Sick
    ABC   01/12/2012 Sick
    ABC   01/13/2012 Sick
    ABC   01/14/2012 Sick
    ABC   01/15/2012 Sick
    ABC   01/16/2012 Reg
    XYZ   01/01/2012 Reg
    XYZ   01/02/2012 Sick
    XYZ   01/03/2012 Reg
    
    19 rows selected.
    
    SQL> select ename, min(datum), count(*), what
      2  from test
      3  where what = 'Sick'
      4  group by ename, what
      5  order by ename;
    
    ENAME MIN(DATUM)   COUNT(*) WHAT
    ----- ---------- ---------- ----------
    ABC   01/02/2012         14 Sick
    XYZ   01/02/2012          1 Sick
    
    SQL>
    (Note that your "required output" is wrong; ABC was sick 14 days, not 13).

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •