This forum is currently locked. You can't register or post questions at this time. (read more)

Get Records From One Column, Display Based On Condition

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

ponic
Senior Member
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Get Records From One Column, Display Based On Condition

Postby ponic » Wed Jun 29, 2011 1:41 pm

Tim ,

Any help is highly appreciated.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Get Records From One Column, Display Based On Condition

Postby Tim... » Wed Jun 29, 2011 1:54 pm

Hi.

I am looking at it. I'm just trying some analytic functions (LAG and LEAD) to see if they can simplify things. It's definitely possible, but I'm not sure it wouldn't be simpler to just do it in PL/SQL. :(

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

ponic
Senior Member
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Get Records From One Column, Display Based On Condition

Postby ponic » Wed Jun 29, 2011 2:09 pm

I really appreciate Tim if there is a way of doing it.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Get Records From One Column, Display Based On Condition

Postby Tim... » Wed Jun 29, 2011 2:38 pm

Hi.

I think this may be what you are looking for:

Code: Select all

with t as (
   select 1234 emp_number, to_date('19-JAN-09 07.06.00 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'IN'  SPECIFIER from dual union all
   select 1234 emp_number, to_date('19-JAN-09 08:40:53 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'OUT' SPECIFIER from dual union all
   select 1234 emp_number, to_date('19-JAN-09 08:50:40 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'IN'  SPECIFIER from dual union all
   select 1234 emp_number, to_date('19-JAN-09 09:50:32 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'OUT' SPECIFIER from dual union all
   select 1234 emp_number, to_date('19-JAN-09 10:07:18 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'IN'  SPECIFIER from dual union all
   select 1234 emp_number, to_date('19-JAN-09 02:49:07 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'OUT' SPECIFIER from dual union all   
   select 1234 emp_number, to_date('22-JAN-09 10:42:50 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'IN' SPECIFIER from dual union all
   select 1234 emp_number, to_date('23-JAN-09 06:50:40 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'OUT' SPECIFIER from dual union all
   select 1234 emp_number, to_date('23-JAN-09 10:49:55 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'IN' SPECIFIER from dual union all
   select 1234 emp_number, to_date('24-JAN-09 05:22:19 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'OUT' SPECIFIER from dual union all
   select 1234 emp_number, to_date('24-JAN-09 05:30:46 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'IN' SPECIFIER from dual union all
   select 1234 emp_number, to_date('24-JAN-09 05:43:51 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'OUT' SPECIFIER from dual union all
   select 1234 emp_number, to_date('24-JAN-09 05:48:40 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'IN' SPECIFIER from dual union all
   select 1234 emp_number, to_date('24-JAN-09 05:50:52 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'OUT' SPECIFIER from dual union all
   select 1234 emp_number, to_date('24-JAN-09 06:03:02 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'IN' SPECIFIER from dual union all
   select 1234 emp_number, to_date('24-JAN-09 06:43:44 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'OUT' SPECIFIER from dual
   )
-- end of on-the-fly-test-data
   , q1 as ( -- Pivot data
    SELECT emp_number,
           time_in_out,
           TRUNC(time_in_out) AS day,
           DECODE(specifier, 'IN', time_in_out, NULL) AS TIME_IN,
           DECODE(specifier, 'OUT', time_in_out, NULL) AS TIME_OUT
      FROM t
    ORDER BY 1,2)
  , aq as ( -- Analytic Query
    SELECT emp_number,
           time_in_out,
           day,
           time_in,
           time_out,
           LAG(time_in, 1, NULL) OVER (ORDER BY time_in_out) AS time_in_prev,
           LEAD(time_out, 1, NULL) OVER (ORDER BY time_in_out) AS time_out_next
    FROM   q1
    ORDER by 1,2)
SELECT emp_number,
       NVL(TO_CHAR(time_in, 'dd-MON-yy hh.mi.ss AM'), 'NIL') AS time_in,
       (CASE
          WHEN TRUNC(time_out_next) = TRUNC(time_in) THEN TO_CHAR(time_out_next, 'dd-MON-yy hh.mi.ss AM')
          WHEN time_in IS NULL AND TRUNC(time_in_prev) != TRUNC(time_out) THEN TO_CHAR(time_out, 'dd-MON-yy hh.mi.ss AM')
          ELSE 'NIL'
        END) AS time_out
FROM   aq
WHERE  time_in IS NOT NULL
OR     (time_in IS NULL and TRUNC(time_in_prev) != TRUNC(time_out));


If gives this output:



Code: Select all

EMP_NUMBER TIME_IN               TIME_OUT
---------- --------------------- ---------------------
      1234 19-JAN-09 07.06.00 AM 19-JAN-09 08.40.53 AM
      1234 19-JAN-09 08.50.40 AM 19-JAN-09 09.50.32 AM
      1234 19-JAN-09 10.07.18 AM 19-JAN-09 02.49.07 PM
      1234 22-JAN-09 10.42.50 PM NIL
      1234 NIL                   23-JAN-09 06.50.40 AM
      1234 23-JAN-09 10.49.55 PM NIL
      1234 NIL                   24-JAN-09 05.22.19 AM
      1234 24-JAN-09 05.30.46 AM 24-JAN-09 05.43.51 AM
      1234 24-JAN-09 05.48.40 AM 24-JAN-09 05.50.52 AM
      1234 24-JAN-09 06.03.02 AM 24-JAN-09 06.43.44 AM

10 rows selected.


Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

ponic
Senior Member
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Get Records From One Column, Display Based On Condition

Postby ponic » Wed Jun 29, 2011 2:53 pm

Tim, I really appreciate your support. This looks fine, but I will test with my real data and tables and would get back to you today itself

Thanks a lot, much appreciated.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Get Records From One Column, Display Based On Condition

Postby Tim... » Wed Jun 29, 2011 4:02 pm

Fingers crossed. :)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

ponic
Senior Member
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Get Records From One Column, Display Based On Condition

Postby ponic » Wed Jun 29, 2011 5:43 pm

Tim,

Thanks a ton, this is working well. :D

I have 2 questions
1) when I use my actual tables, could I remove as q1 as and aq as?
I tried

Code: Select all

 SELECT emp_number,
               time_in_out,
               TRUNC(time_in_out) AS day,
               DECODE(specifier, 'IN', time_in_out, NULL) AS TIME_IN,
               DECODE(specifier, 'OUT', time_in_out, NULL) AS TIME_OUT
          FROM table_name
        ORDER BY 1,2) q1

But the above is not accepting.

2) Yesterday we discussed about showing 'No data' if there are no records in table.
How we could plug that into this?

Once again I thank from the bottom of my heart.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Get Records From One Column, Display Based On Condition

Postby Tim... » Wed Jun 29, 2011 9:01 pm

Hi.

Those queries are (or should be) part of the WITH clause. You kinda need them. :)

The only thing you should be removing in "t as (...),"

As for the other bit, I'll take a look at it tomorrow if I have time. Should just be of the "generated dates" gen_date table with the final query in the select.

Code: Select all

    d as (
       select to_date('01-DEC-2009', 'dd-MON-yyyy') + level - 1 as gen_date,
              1234 as emp_number
       from   dual
       connect by level <= 30)


Cheers
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Get Records From One Column, Display Based On Condition

Postby Tim... » Wed Jun 29, 2011 10:19 pm

Hi.

This is what I was thinking.

First let's create a table with data in it some I don't have that extra block in the WITH clause.

Code: Select all

DROP TABLE real_data PURGE;
CREATE TABLE real_data AS
    select 1234 emp_number, to_date('19-JAN-09 07.06.00 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'IN'  SPECIFIER from dual union all
    select 1234 emp_number, to_date('19-JAN-09 08:40:53 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'OUT' SPECIFIER from dual union all
    select 1234 emp_number, to_date('19-JAN-09 08:50:40 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'IN'  SPECIFIER from dual union all
    select 1234 emp_number, to_date('19-JAN-09 09:50:32 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'OUT' SPECIFIER from dual union all
    select 1234 emp_number, to_date('19-JAN-09 10:07:18 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'IN'  SPECIFIER from dual union all
    select 1234 emp_number, to_date('19-JAN-09 02:49:07 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'OUT' SPECIFIER from dual union all   
    select 1234 emp_number, to_date('22-JAN-09 10:42:50 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'IN' SPECIFIER from dual union all
    select 1234 emp_number, to_date('23-JAN-09 06:50:40 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'OUT' SPECIFIER from dual union all
    select 1234 emp_number, to_date('23-JAN-09 10:49:55 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'IN' SPECIFIER from dual union all
    select 1234 emp_number, to_date('24-JAN-09 05:22:19 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'OUT' SPECIFIER from dual union all
    select 1234 emp_number, to_date('24-JAN-09 05:30:46 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'IN' SPECIFIER from dual union all
    select 1234 emp_number, to_date('24-JAN-09 05:43:51 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'OUT' SPECIFIER from dual union all
    select 1234 emp_number, to_date('24-JAN-09 05:48:40 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'IN' SPECIFIER from dual union all
    select 1234 emp_number, to_date('24-JAN-09 05:50:52 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'OUT' SPECIFIER from dual union all
    select 1234 emp_number, to_date('24-JAN-09 06:03:02 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'IN' SPECIFIER from dual union all
    select 1234 emp_number, to_date('24-JAN-09 06:43:44 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'OUT' SPECIFIER from dual;


Now for the query.

Code: Select all

with
  date_query as (
    SELECT 1234 AS emp_number,
           TO_DATE('01-JAN-2009', 'DD-MON-YYYY') + LEVEL - 1 AS day
    FROM   dual
    CONNECT BY LEVEL <= 30
  ),
  pivot_query as ( -- Pivot data data
    SELECT emp_number,
           time_in_out,
           TRUNC(time_in_out) AS day,
           DECODE(specifier, 'IN', time_in_out, NULL) AS TIME_IN,
           DECODE(specifier, 'OUT', time_in_out, NULL) AS TIME_OUT
      FROM real_data
    ORDER BY 1,2
  ),
  analytic_query as ( -- Analytic Query
    SELECT emp_number,
           time_in_out,
           day,
           time_in,
           time_out,
           LAG(time_in, 1, NULL) OVER (ORDER BY time_in_out) AS time_in_prev,
           LEAD(time_out, 1, NULL) OVER (ORDER BY time_in_out) AS time_out_next
    FROM   pivot_query
    ORDER by 1,2
  ),
  final_query as (
    SELECT aq.emp_number,
           aq.day,
           NVL(TO_CHAR(aq.time_in, 'dd-MON-yy hh.mi.ss AM'), 'NIL') AS time_in,
           (CASE
              WHEN TRUNC(aq.time_out_next) = TRUNC(aq.time_in) THEN TO_CHAR(aq.time_out_next, 'dd-MON-yy hh.mi.ss AM')
              WHEN aq.time_in IS NULL AND TRUNC(aq.time_in_prev) != TRUNC(aq.time_out) THEN TO_CHAR(aq.time_out, 'dd-MON-yy hh.mi.ss AM')
              ELSE 'NIL'
            END) AS time_out
    FROM   analytic_query aq
    WHERE  (
            aq.time_in IS NOT NULL
            OR     
            (aq.time_in IS NULL and TRUNC(aq.time_in_prev) != TRUNC(aq.time_out))
           )
  )
SELECT dq.emp_number,
       dq.day,
       fq.time_in,
       fq.time_out
FROM   date_query dq
       LEFT OUTER JOIN final_query fq ON dq.emp_number = fq.emp_number AND dq.day = fq.day
WHERE  dq.emp_number = 1234
ORDER BY 1,2;


This produces the following output.

Code: Select all

EMP_NUMBER DAY                  TIME_IN               TIME_OUT
---------- -------------------- --------------------- ---------------------
      1234 01-JAN-2009 00:00:00
      1234 02-JAN-2009 00:00:00
      1234 03-JAN-2009 00:00:00
      1234 04-JAN-2009 00:00:00
      1234 05-JAN-2009 00:00:00
      1234 06-JAN-2009 00:00:00
      1234 07-JAN-2009 00:00:00
      1234 08-JAN-2009 00:00:00
      1234 09-JAN-2009 00:00:00
      1234 10-JAN-2009 00:00:00
      1234 11-JAN-2009 00:00:00

EMP_NUMBER DAY                  TIME_IN               TIME_OUT
---------- -------------------- --------------------- ---------------------
      1234 12-JAN-2009 00:00:00
      1234 13-JAN-2009 00:00:00
      1234 14-JAN-2009 00:00:00
      1234 15-JAN-2009 00:00:00
      1234 16-JAN-2009 00:00:00
      1234 17-JAN-2009 00:00:00
      1234 18-JAN-2009 00:00:00
      1234 19-JAN-2009 00:00:00 19-JAN-09 10.07.18 AM 19-JAN-09 02.49.07 PM
      1234 19-JAN-2009 00:00:00 19-JAN-09 07.06.00 AM 19-JAN-09 08.40.53 AM
      1234 19-JAN-2009 00:00:00 19-JAN-09 08.50.40 AM 19-JAN-09 09.50.32 AM
      1234 20-JAN-2009 00:00:00

EMP_NUMBER DAY                  TIME_IN               TIME_OUT
---------- -------------------- --------------------- ---------------------
      1234 21-JAN-2009 00:00:00
      1234 22-JAN-2009 00:00:00 22-JAN-09 10.42.50 PM NIL
      1234 23-JAN-2009 00:00:00 23-JAN-09 10.49.55 PM NIL
      1234 23-JAN-2009 00:00:00 NIL                   23-JAN-09 06.50.40 AM
      1234 24-JAN-2009 00:00:00 NIL                   24-JAN-09 05.22.19 AM
      1234 24-JAN-2009 00:00:00 24-JAN-09 05.30.46 AM 24-JAN-09 05.43.51 AM
      1234 24-JAN-2009 00:00:00 24-JAN-09 06.03.02 AM 24-JAN-09 06.43.44 AM
      1234 24-JAN-2009 00:00:00 24-JAN-09 05.48.40 AM 24-JAN-09 05.50.52 AM
      1234 25-JAN-2009 00:00:00
      1234 26-JAN-2009 00:00:00
      1234 27-JAN-2009 00:00:00

EMP_NUMBER DAY                  TIME_IN               TIME_OUT
---------- -------------------- --------------------- ---------------------
      1234 28-JAN-2009 00:00:00
      1234 29-JAN-2009 00:00:00
      1234 30-JAN-2009 00:00:00

36 rows selected.

test@db11g>


I really hope that is clear because I'm losing the will to live. LOL. :)
Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

ponic
Senior Member
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Get Records From One Column, Display Based On Condition

Postby ponic » Thu Jun 30, 2011 4:56 am

Thanks a lot again Tim.

Testing is going on and so far looks fine.

I really hope that is clear because I'm losing the will to live. LOL. :)

Sorry about that, I was in kind of desperate. :(
By the way how did you master SQL

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Get Records From One Column, Display Based On Condition

Postby Tim... » Thu Jun 30, 2011 7:11 am

Hi.

It was only a joke. Don't worry. :)

It's kinda difficult to do this stuff sometimes when you don't have the real data to look at. You are sometimes shooting in the dark.

As for mastering SQL, I wish I had. I'm just constantly writing SQL and I spend a lot of time reading the manuals. There is a great deal of trial and error in trying to find a solution like this. The best thing is to just keep playing around with the different SQL functionality, then you will start getting a feel for what is possible (or not). Once you understand the building blocks and what you are aiming for you can usually start to piece something together. The steps I took to get to the solution are really shown in the elements of the WITH clause. Once problem was solved at a time. :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

ponic
Senior Member
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Get Records From One Column, Display Based On Condition

Postby ponic » Thu Jun 30, 2011 7:16 am

Tim,
Forgive to bother you again, there is a little problem with the ordering of data, I guess this is caused by order by
If you look data on 22-JAN-09 IN_TIME is 22-JAN-09 10:42:50 PM and OUT_TIME is 23-JAN-09 06:50:40 AM And 23-JAN-09 IN_TIME is
23-JAN-09 10.49.55 and OUT_TIME is 24-JAN-09 05:22:19 AM. But in the output IN_TIME and OUT_TIME are not corresponding and not ordered

Code: Select all

select 1234 emp_number, to_date('19-JAN-09 02:49:07 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'OUT' SPECIFIER from dual union all   
    select 1234 emp_number, to_date('22-JAN-09 10:42:50 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'IN' SPECIFIER from dual union all
    select 1234 emp_number, to_date('23-JAN-09 06:50:40 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'OUT' SPECIFIER from dual union all
    select 1234 emp_number, to_date('23-JAN-09 10:49:55 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'IN' SPECIFIER from dual union all
    select 1234 emp_number, to_date('24-JAN-09 05:22:19 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'OUT' SPECIFIER from dual


Code: Select all

EMP_NUMBER DAY                  TIME_IN               TIME_OUT
---------- -------------------- --------------------- ---------------------
      1234 21-JAN-2009 00:00:00
      1234 22-JAN-2009 00:00:00 22-JAN-09 10.42.50 PM NIL
      1234 23-JAN-2009 00:00:00 23-JAN-09 10.49.55 PM NIL
      1234 23-JAN-2009 00:00:00 NIL                   23-JAN-09 06.50.40 AM
      1234 24-JAN-2009 00:00:00 NIL                   24-JAN-09 05.22.19 AM
      1234 24-JAN-2009 00:00:00 24-JAN-09 05.30.46 AM 24-JAN-09 05.43.51 AM
      1234 24-JAN-2009 00:00:00 24-JAN-09 06.03.02 AM 24-JAN-09 06.43.44 AM
      1234 24-JAN-2009 00:00:00 24-JAN-09 05.48.40 AM 24-JAN-09 05.50.52 AM


Is there any way to fix this as well?

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Get Records From One Column, Display Based On Condition

Postby Tim... » Thu Jun 30, 2011 7:29 am

Hi.

I think this bad-boy should do it.

Code: Select all

with
  date_query as (
    SELECT 1234 AS emp_number,
           TO_DATE('01-JAN-2009', 'DD-MON-YYYY') + LEVEL - 1 AS day
    FROM   dual
    CONNECT BY LEVEL <= 30
  ),
  pivot_query as ( -- Pivot data data
    SELECT emp_number,
           time_in_out,
           TRUNC(time_in_out) AS day,
           DECODE(specifier, 'IN', time_in_out, NULL) AS TIME_IN,
           DECODE(specifier, 'OUT', time_in_out, NULL) AS TIME_OUT
      FROM real_data
    ORDER BY 1,2
  ),
  analytic_query as ( -- Analytic Query
    SELECT emp_number,
           time_in_out,
           day,
           time_in,
           time_out,
           LAG(time_in, 1, NULL) OVER (ORDER BY time_in_out) AS time_in_prev,
           LEAD(time_out, 1, NULL) OVER (ORDER BY time_in_out) AS time_out_next
    FROM   pivot_query
    ORDER by 1,2
  ),
  final_query as (
    SELECT aq.emp_number,
           aq.day,
           aq.time_in,
           (CASE
              WHEN TRUNC(aq.time_out_next) = TRUNC(aq.time_in) THEN aq.time_out_next
              WHEN aq.time_in IS NULL AND TRUNC(aq.time_in_prev) != TRUNC(aq.time_out) THEN aq.time_out
              ELSE NULL
            END) AS time_out
    FROM   analytic_query aq
    WHERE  (
            aq.time_in IS NOT NULL
            OR     
            (aq.time_in IS NULL and TRUNC(aq.time_in_prev) != TRUNC(aq.time_out))
           )
  )
SELECT dq.emp_number,
       dq.day,
       COALESCE(fq.time_in, fq.time_out, dq.day) AS event_order,
       NVL(TO_CHAR(fq.time_in, 'dd-MON-yy hh.mi.ss AM'), 'NIL') AS time_in,
       NVL(TO_CHAR(fq.time_out, 'dd-MON-yy hh.mi.ss AM'), 'NIL') AS time_out
FROM   date_query dq
       LEFT OUTER JOIN final_query fq ON dq.emp_number = fq.emp_number AND dq.day = fq.day
WHERE  dq.emp_number = 1234
ORDER BY 1,2,3;


There was no ordering beyond day. I've moved the date conversions to the outer query so I can use the dates for ordering. Used COALESCE so I can order by time_in, time_out or day (whichever is present first).

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

ponic
Senior Member
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Get Records From One Column, Display Based On Condition

Postby ponic » Thu Jun 30, 2011 11:12 am

Tim,
COALESCE did solve that problem.

When I run for multiple employees, I am getting incorrect results with my real data.
Any way I could send you real table data for testing as why incorrect results are coming?

Thanks and much appreciated.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Get Records From One Column, Display Based On Condition

Postby Tim... » Thu Jun 30, 2011 11:19 am

Hi.

Look at the "date_query" in the WITH clause. It requires:

- emp_number
- Start date.
- Number of days.

As a result, this will not work with other customers unless this information is changed also.

The data you sent in all the previous posts suggested that this would only ever be called for one employee at a time. Is this now not the case?

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 3 guests

cron