Get Records From One Column, Display Based On Condition

[ponic]

I have the following sql as temp data

Code: Select all

with t as (
   select 1234 emp_number, to_date('11-DEC-09 10.50.41 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'IN'  SPECIFIER from dual union all
   select 1234 emp_number, to_date('12-DEC-09 06.19.53 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'OUT' SPECIFIER from dual union all
   select 1234 emp_number, to_date('13-DEC-09 11.53.26 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'IN'  SPECIFIER from dual union all
   select 1234 emp_number, to_date('14-DEC-09 05.09.36 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'OUT' SPECIFIER from dual union all
   select 1234 emp_number, to_date('11-DEC-09 10.50.41 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'IN'  SPECIFIER from dual union all
   select 1234 emp_number, to_date('11-DEC-09 11.19.53 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'OUT' SPECIFIER from dual     
   )
-- end of on-the-fly-test-data
SELECT emp_number,
       DECODE(specifier, 'IN', TO_CHAR(time_in_out, 'dd-MON-yy hh.mi.ss AM'), 'NIL') AS TIME_IN,
       DECODE(specifier, 'OUT', TO_CHAR(time_in_out, 'dd-MON-yy hh.mi.ss AM'), 'NIL') AS TIME_OUT
  FROM t

if IN_TIME and OUT_TIME is on the same day, then time_in_out should display in same row, otherwise I would like to get the output like the following

Code: Select all

EMP_NUMBER    TIME_IN                TIME_OUT
 
1234   11/Dec/09 10:50:41 PM             NIL
1234    NIL                                   12/Dec/09 6:19:53 AM
1234   13/Dec/09 11:53:26 PM                NIL
1234   NIL                                  14/Dec/09 5:09:36 AM


[Tim...]

Hi.

You mean like this?

Code: Select all

with t as (
   select 1234 emp_number, to_date('11-DEC-09 10.50.41 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'IN'  SPECIFIER from dual union all
   select 1234 emp_number, to_date('12-DEC-09 06.19.53 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'OUT' SPECIFIER from dual union all
   select 1234 emp_number, to_date('13-DEC-09 11.53.26 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'IN'  SPECIFIER from dual union all
   select 1234 emp_number, to_date('14-DEC-09 05.09.36 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'OUT' SPECIFIER from dual union all
   select 1234 emp_number, to_date('11-DEC-09 10.50.41 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'IN'  SPECIFIER from dual union all
   select 1234 emp_number, to_date('11-DEC-09 11.19.53 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'OUT' SPECIFIER from dual     
   )
-- end of on-the-fly-test-data
SELECT emp_number,
       TRUNC(time_in_out) AS day,
       NVL(MAX(DECODE(specifier, 'IN', TO_CHAR(time_in_out, 'dd-MON-yy hh.mi.ss AM'), NULL)),'NIL') AS TIME_IN,
       NVL(MAX(DECODE(specifier, 'OUT', TO_CHAR(time_in_out, 'dd-MON-yy hh.mi.ss AM'), NULL)),'NIL') AS TIME_OUT
  FROM t
GROUP BY emp_number, TRUNC(time_in_out)
ORDER BY 1,2;

EMP_NUMBER DAY                  TIME_IN               TIME_OUT
---------- -------------------- --------------------- ---------------------
      1234 11-DEC-2009 00:00:00 11-DEC-09 10.50.41 PM 11-DEC-09 11.19.53 AM
      1234 12-DEC-2009 00:00:00 NIL                   12-DEC-09 06.19.53 AM
      1234 13-DEC-2009 00:00:00 13-DEC-09 11.53.26 PM NIL
      1234 14-DEC-2009 00:00:00 NIL                   14-DEC-09 05.09.36 AM

4 rows selected.

test@db11g>

Cheers

Tim...

[ponic]

Yes, this looks 98% to the requirement. I need to test with real data.

Thanks a lot, much appreciated.

I have another question.

Code: Select all

 with t as (
       select 1234 emp_number, to_date('11-DEC-09 10.50.41 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'IN'  SPECIFIER from dual union all
       select 1234 emp_number, to_date('12-DEC-09 06.19.53 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'OUT' SPECIFIER from dual union all
       select 1234 emp_number, to_date('13-DEC-09 11.53.26 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'IN'  SPECIFIER from dual union all
       select 1234 emp_number, to_date('14-DEC-09 05.09.36 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'OUT' SPECIFIER from dual union all
       select 1234 emp_number, to_date('11-DEC-09 10.50.41 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'IN'  SPECIFIER from dual union all
       select 1234 emp_number, to_date('11-DEC-09 11.19.53 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'OUT' SPECIFIER from dual     
       )

In the above if I do not have data for say on 15th Dec 2009 in my real table, is it possible to show 'Absent' in another column called Comments?

[Tim...]

Hi.

Then you have to do things quite a bit differently. You must know the range of dates you are planning to use and drive against a separate "table" that provides those dates so there are guaranteed no missing rows.

Code: Select all

with t as (
       select 1234 emp_number, to_date('11-DEC-09 10.50.41 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'IN'  SPECIFIER from dual union all
       select 1234 emp_number, to_date('12-DEC-09 06.19.53 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'OUT' SPECIFIER from dual union all
       select 1234 emp_number, to_date('13-DEC-09 11.53.26 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'IN'  SPECIFIER from dual union all
       select 1234 emp_number, to_date('14-DEC-09 05.09.36 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'OUT' SPECIFIER from dual union all
       select 1234 emp_number, to_date('11-DEC-09 10.50.41 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'IN'  SPECIFIER from dual union all
       select 1234 emp_number, to_date('11-DEC-09 11.19.53 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'OUT' SPECIFIER from dual     
       ),
-- end of on-the-fly-test-data
    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)
SELECT d.emp_number,
       d.gen_date AS day,
       NVL(MAX(DECODE(t.specifier, 'IN', TO_CHAR(t.time_in_out, 'dd-MON-yy hh.mi.ss AM'), NULL)),'NIL') AS TIME_IN,
       NVL(MAX(DECODE(t.specifier, 'OUT', TO_CHAR(t.time_in_out, 'dd-MON-yy hh.mi.ss AM'), NULL)),'NIL') AS TIME_OUT
  FROM d
  LEFT OUTER JOIN t ON d.gen_date = TRUNC(time_in_out) and d.emp_number = t.emp_number
GROUP BY d.emp_number, d.gen_date
ORDER BY 1,2;

That would give:

Code: Select all

EMP_NUMBER DAY                  TIME_IN               TIME_OUT
---------- -------------------- --------------------- ---------------------
      1234 01-DEC-2009 00:00:00 NIL                   NIL
      1234 02-DEC-2009 00:00:00 NIL                   NIL
      1234 03-DEC-2009 00:00:00 NIL                   NIL
      1234 04-DEC-2009 00:00:00 NIL                   NIL
      1234 05-DEC-2009 00:00:00 NIL                   NIL
      1234 06-DEC-2009 00:00:00 NIL                   NIL
      1234 07-DEC-2009 00:00:00 NIL                   NIL
      1234 08-DEC-2009 00:00:00 NIL                   NIL
      1234 09-DEC-2009 00:00:00 NIL                   NIL
      1234 10-DEC-2009 00:00:00 NIL                   NIL
      1234 11-DEC-2009 00:00:00 11-DEC-09 10.50.41 PM 11-DEC-09 11.19.53 AM

EMP_NUMBER DAY                  TIME_IN               TIME_OUT
---------- -------------------- --------------------- ---------------------
      1234 12-DEC-2009 00:00:00 NIL                   12-DEC-09 06.19.53 AM
      1234 13-DEC-2009 00:00:00 13-DEC-09 11.53.26 PM NIL
      1234 14-DEC-2009 00:00:00 NIL                   14-DEC-09 05.09.36 AM
      1234 15-DEC-2009 00:00:00 NIL                   NIL
      1234 16-DEC-2009 00:00:00 NIL                   NIL
      1234 17-DEC-2009 00:00:00 NIL                   NIL
      1234 18-DEC-2009 00:00:00 NIL                   NIL
      1234 19-DEC-2009 00:00:00 NIL                   NIL
      1234 20-DEC-2009 00:00:00 NIL                   NIL
      1234 21-DEC-2009 00:00:00 NIL                   NIL
      1234 22-DEC-2009 00:00:00 NIL                   NIL

EMP_NUMBER DAY                  TIME_IN               TIME_OUT
---------- -------------------- --------------------- ---------------------
      1234 23-DEC-2009 00:00:00 NIL                   NIL
      1234 24-DEC-2009 00:00:00 NIL                   NIL
      1234 25-DEC-2009 00:00:00 NIL                   NIL
      1234 26-DEC-2009 00:00:00 NIL                   NIL
      1234 27-DEC-2009 00:00:00 NIL                   NIL
      1234 28-DEC-2009 00:00:00 NIL                   NIL
      1234 29-DEC-2009 00:00:00 NIL                   NIL
      1234 30-DEC-2009 00:00:00 NIL                   NIL

30 rows selected.

test@db11g>

Cheers

Tim...

[ponic]

Tim,

The parameters passed from report for dates could be anything.
I didn't understand the second table part.

Table 2 should have emp-number as well along with dates?

Is it like create a range of dates in table 2 and use table 2 in where condition?

[Tim...]

Hi.

The original data has rows missing and you need to create the missing rows. How do you do that? You need a table to join to that contains all the missing data. Luckily we can generate that using connect by.

Now the date range "table" is the driving query that provides all the days, we then hook the real data (if present) on to that. This way all days are represented, regardless of if rows are missing from the real data.

Cheers

Tim...

[ponic]

Can I create a view like this

Code: Select all

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


and use this view in the above sql part?

How can I create a range starting from 01-Jan-2011 till present day?

Thanks a lot for help and suggestions

[ponic]

I managed to do this with my real tables. However when I execute it is executing for all the records since 01-Jan-2011

This is my modified query

Code: Select all

SELECT d.emp_number,
           d.gen_date AS day,
           NVL(MAX(DECODE(specifier, 'IN', TO_CHAR(tab.time_in_out, 'dd-MON-yy hh.mi.ss AM'), NULL)),'NIL') AS TIME_IN,
           NVL(MAX(DECODE(specifier, 'OUT', TO_CHAR(tab.time_in_out, 'dd-MON-yy hh.mi.ss AM'), NULL)),'NIL') AS TIME_OUT
      FROM v_employees_dates d     
      LEFT OUTER JOIN tab ON d.gen_date = TRUNC(time_in_out) and d.emp_number = tab.emp_number
       and trunc(time_in_out) >='01-May-2011' and
   trunc(time_in_out) <= '10-May-2011'
    GROUP BY d.emp_number, d.gen_date
    ORDER BY 1,2;


SQL for v_employees_dates view

Code: Select all

select trunc(to_date('01-JAN-2011', 'dd-MON-yyyy') + level - 1) as gen_date,'1234' as emp_number
       from   dual
       connect by level <= (select round(sysdate - to_date('01-JAN-2011', 'dd-MON-yyyy')) from dual)


When I execute my query it is executing from '01-JAN-2011' till '28-JUN-2011'.Not my where condition
trunc(time_in_out) >='01-May-2011' and
trunc(time_in_out) <= '10-May-2011'

How can I solve this?

[Tim...]

Hi.

Using strings for dates without a TO_DATE around them. Tut! Tut!

You should never do this as the implicit conversion from string-to-date is dependent on the default NLS settings. Very dangerous!

Also, Oracle may choose to convert the value in the DATE column to a string to do the comparison against the string you provided. If this happens, there is no telling what the date comparison will result in since '11-Jan-2011' is larger than '11-Feb-2011' in a string-to-string comparison, but if converted to dates it is the other way round.

Use dates for dates and strings for strings. Never mix and match.

Cheers

Tim...

[ponic]

Hmmm

may be like this

Code: Select all

    SELECT d.emp_number,
               d.gen_date AS day,
               NVL(MAX(DECODE(specifier, 'IN', TO_CHAR(tab.time_in_out, 'dd-MON-yy hh.mi.ss AM'), NULL)),'NIL') AS TIME_IN,
               NVL(MAX(DECODE(specifier, 'OUT', TO_CHAR(tab.time_in_out, 'dd-MON-yy hh.mi.ss AM'), NULL)),'NIL') AS TIME_OUT
          FROM v_employees_dates d     
          LEFT OUTER JOIN tab ON d.gen_date = to_date(time_in_out,dd-MON-yyyy) and d.emp_number = tab.emp_number
           and to_date(time_in_out,dd-MON-yyyy) >='01-MAY-2011' and
       to_date(time_in_out,dd-MON-yyyy)) <= '10-MAY-2011'
        GROUP BY d.emp_number, d.gen_date
        ORDER BY 1,2;


Sorry I couldn't grasp from what you mentioned in your last post!

[Tim...]

Hi.

From your original test data, TIME_IN_OUT is a date column:

Code: Select all

with t as (
       select 1234 emp_number, to_date('11-DEC-09 10.50.41 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'IN'  SPECIFIER from dual union all
       select 1234 emp_number, to_date('12-DEC-09 06.19.53 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'OUT' SPECIFIER from dual union all
       select 1234 emp_number, to_date('13-DEC-09 11.53.26 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'IN'  SPECIFIER from dual union all
       select 1234 emp_number, to_date('14-DEC-09 05.09.36 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'OUT' SPECIFIER from dual union all
       select 1234 emp_number, to_date('11-DEC-09 10.50.41 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'IN'  SPECIFIER from dual union all
       select 1234 emp_number, to_date('11-DEC-09 11.19.53 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'OUT' SPECIFIER from dual     
       )

The query you sent had this in it.

Code: Select all

   and trunc(time_in_out) >='01-May-2011' and
   trunc(time_in_out) <= '10-May-2011'

Oracle can choose to convert the "trunc(time_in_out)" value into a string and for example end up doing the comparison '01-Nov-2010' >='01-May-2011'. This is a string to string comparison, not a date to date comparison, so

Code: Select all

TRUE:  '01-Nov-2010' >= '01-May-2011'
FALSE: TO_DATE('01-Nov-2010', 'DD-MON-YYYY') >=TO_DATE('01-May-2011', 'DD-MON-YYYY')

Very different things happen depending on which value Oracle does an implicit conversion on. What your code should have had is this.

Code: Select all

   and trunc(time_in_out) >=TO_DATE('01-May-2011', 'DD-MON-YYYY') and
   trunc(time_in_out) <= TO_DATE('10-May-2011', 'DD-MON-YYYY')

It's the *only* way you can *guarantee* what is going to happen in the comparison.

Remember the following mantra:

"If I have a date value in a string and I don't explicitly convert it to a DATE using the TO_DATE function, I deserved to have bug-riddled code. "

Cheers

Tim...

[ponic]

Now please don't slap me, even this didn't work

Code: Select all

 and trunc(time_in_out) >=TO_DATE('01-MAY-2011', 'DD-MON-YYYY') and
   trunc(time_in_out) <= TO_DATE('10-MAY-2011', 'DD-MON-YYYY')


[Tim...]

Hi.

Don't worry. I don't mind if it doesn't work. I just mind if it is possible not working because of date conversion issues.

Are you sure those last two items should be part of the join predicates, not in the WHERE claise?

Code: Select all

LEFT OUTER JOIN tab ON d.gen_date = to_date(time_in_out,dd-MON-yyyy) and d.emp_number = tab.emp_number
           and to_date(time_in_out,dd-MON-yyyy) >='01-MAY-2011' and
       to_date(time_in_out,dd-MON-yyyy)) <= '10-MAY-2011'

OR

Code: Select all

LEFT OUTER JOIN tab ON d.gen_date = to_date(time_in_out,dd-MON-yyyy) and d.emp_number = tab.emp_number
WHERE to_date(time_in_out,dd-MON-yyyy) >='01-MAY-2011'
AND      to_date(time_in_out,dd-MON-yyyy)) <= '10-MAY-2011'

The only thing in the join should be the join information, not extra filters, which should really be in the WHERE.

Can you explain what "v_employees_dates" is. You are using it like it is a generated list of date, but I can't be sure.

Do you really have a table called "TAB"? That is a really bad idea as it is a public synonym.

Code: Select all

test@db11g> desc tab
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 TNAME                                                 NOT NULL VARCHAR2(30)
 TABTYPE                                                        VARCHAR2(7)
 CLUSTERID                                                      NUMBER

test@db11g>

Cheers

Tim...

[ponic]

v_employees_dates is a table created for storing dates ranges with the following query

Code: Select all

select trunc(to_date('01-JAN-2011', 'dd-MON-yyyy') + level - 1) as gen_date,'1234' as emp_number
       from   dual
       connect by level <= (select round(sysdate - to_date('01-JAN-2011', 'dd-MON-yyyy')) from dual)


Code: Select all

desc tab
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMP_NUMBER                                         VARCHAR2(6)
 time_in_out                                           DATE
SPECIFIER                                   VARCHAR2(1)
PROCESS_DATE                                         DATE


Code: Select all

Select all
    LEFT OUTER JOIN tab ON d.gen_date = to_date(time_in_out,dd-MON-yyyy) and d.emp_number = tab.emp_number
               and to_date(time_in_out,dd-MON-yyyy) >='01-MAY-2011' and
           to_date(time_in_out,dd-MON-yyyy)) <= '10-MAY-2011'


The above didn't work, same as before.

Code: Select all

 Select all
    LEFT OUTER JOIN tab ON d.gen_date = to_date(time_in_out,dd-MON-yyyy) and d.emp_number = tab.emp_number
    WHERE to_date(time_in_out,dd-MON-yyyy) >='01-MAY-2011'
    AND      to_date(time_in_out,dd-MON-yyyy)) <= '10-MAY-2011'


No rows returned

[ponic]

Tim

I was testing with my real data

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
SELECT emp_number,
       TRUNC(time_in_out) AS day,
       NVL(MAX(DECODE(specifier, 'IN', TO_CHAR(time_in_out, 'dd-MON-yy hh.mi.ss AM'), NULL)),'NIL') AS TIME_IN,
       NVL(MAX(DECODE(specifier, 'OUT', TO_CHAR(time_in_out, 'dd-MON-yy hh.mi.ss AM'), NULL)),'NIL') AS TIME_OUT
  FROM t
GROUP BY emp_number, TRUNC(time_in_out)
ORDER BY 1,2;


would give the following output

Code: Select all

EMP_NUMBER DAY                TIME_IN                                           TIME_OUT

1234          19/Jun/11           19-JAN-09 10.07.18 AM                   19-JAN-09 09.50.32 AM
1234           22/Jun/11          22-JAN-09 10.42.50 PM                   NIL
1234          23/Jun/11        23-JAN-09 10.49.55 PM                   23-JAN-09 06.50.40 AM
1234         24/Jun/11         24-JAN-09 06.03.02 AM                   24-JAN-09 06.43.44 AM


If you look at data on 22-JAN-09, we have IN_TIME at 22-JAN-09 10.42.50 PM and next day OUT_TIME at 23-JAN-09 06.50.40 AM and again on the same day
there is a IN_TIME at 23-JAN-09 10.49.55 PM and OUT_TIME at 24-JAN-09 05:22:19 AM. So ideally output should be

Code: Select all

EMP_NUMBER DAY                TIME_IN                                           TIME_OUT

1234          19/Jun/11           19-JAN-09 10.07.18 AM                   19-JAN-09 09.50.32 AM
1234           22/Jun/11          22-JAN-09 10.42.50 PM                   NIL
1234          23/Jun/11                  NIL                           23-JAN-09 06.50.40 AM
1234         24/Jun/11         23-JAN-09 10.49.55 PM                   NIL
1234         24/Jun/11                    NIL                         24-JAN-09 05:22:19 AM 


How could I achieve this and would like to display all the clockings if there are more than one on a single day?