Get Records From One Column, Display Based On Condition

[ponic]

To deal with other customers I created a view with the following
sql

Code: Select all

 SELECT    TRUNC (TO_DATE ('01-JUN-2009', 'dd-MON-yyyy') + LEVEL - 1) AS day,
                '1234' as emp_number
         FROM    DUAL
   CONNECT BY    LEVEL <= 30                  
   UNION ALL
        SELECT    TRUNC (TO_DATE ('01-JUN-2009', 'dd-MON-yyyy') + LEVEL - 1) AS day,
                '5678' as emp_number
         FROM    DUAL
CONNECT BY    LEVEL <= 30                  


and use the following in date_query part

Code: Select all

SELECT emp_number,
               day
        FROM   view_name


And use the following condition in pivot_query and last select part(one after final_query)

Code: Select all

where emp_number in('1234','5678')   
          and trunc(time_in_out)>=to_date('10-JAN-2009','DD-MON-YYYY') and
    trunc(time_in_out)<=to_date('20-JAN-2009','DD-MON-YYYY')


May be I am missing something obvious?

[Tim...]

Hi.

Yes, we are talking very much at crossed purposes.

Questions: You you expect a single run of this query to return information for several customers, or will it only every be used to report for a single customer?

By that I mean,

- Run query for customer 1234, then later run query for customer 2345.

OR.

- Run query once and get results back for all customers.

Which is it? As I said before, the test data you sent implied that it would only ever be run for a single customer at a time, so I wrote it to do that. If that is not the case, then you should have sent something that represented the interaction you were expecting.

Cheers

Tim...

[Tim...]

Hi.

The date_query will now deal with any number of customers.

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 union all -- new cust
    select 2345 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 2345 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 2345 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 2345 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 2345 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 2345 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 2345 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 2345 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 2345 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 2345 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 2345 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 2345 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 2345 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 2345 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 2345 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 2345 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;
   
with
  date_query as (
    SELECT emp_number,
           day
    FROM   (SELECT distinct emp_number
            FROM   real_data
            --WHERE  emp_number IN (1234, 2345)
            )
            CROSS JOIN
           (SELECT 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 IN (1234, 2345)
ORDER BY 1,2,3;

Just limit the people in the WHERE clause. The date_query could get very heavy if you were going to have many customers. Might want to incorporate the where clause in there also. I've included it, but commented it out.

Cheers

Tim...

[ponic]

Tim,

Thanks again, I am testing with real data.

[Tim...]

Hi.

OK. Just remember, the date query will generate all the days for all the employees unless you limit it with a where clause. If yo have lots of employees, or lots of days to query, or both it could be heavy.

Cheers

Tim...

[ponic]

Tim,

I am still having issues with in-correct results, so some real data.

Code: Select all

select 1234 emp_number,    to_date('13-JAN-2009 07.05.50 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'IN' SPECIFIER from dual union all
select 1234 emp_number,    to_date('13-JAN-2009 02.38.40 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'OUT' SPECIFIER from dual union all
select 1234 emp_number,    to_date('14-JAN-2009 07.20.54 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'IN' SPECIFIER from dual union all
select 1234 emp_number,    to_date('14-JAN-2009 11.47.45 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'OUT' SPECIFIER from dual union all
select 1234 emp_number,    to_date('14-JAN-2009 11.49.15 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'IN' SPECIFIER from dual union all
select 1234 emp_number,    to_date('14-JAN-2009 04.03.33 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'OUT' SPECIFIER from dual union all
select 1234 emp_number,    to_date('16-JAN-2009 07.18.55 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'IN' SPECIFIER from dual union all
select 1234 emp_number,    to_date('16-JAN-2009 02.29.19 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'OUT' SPECIFIER from dual union all
select 1234 emp_number,    to_date('16-JAN-2009 10.25.31 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'IN' SPECIFIER from dual union all
select 1234 emp_number,    to_date('17-JAN-2009 06.39.00 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'OUT' SPECIFIER from dual union all
select 1234 emp_number,    to_date('17-JAN-2009 09.00.16 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'IN' SPECIFIER from dual union all
select 1234 emp_number,    to_date('18-JAN-2009 06.20.24 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'OUT' SPECIFIER from dual union all
select 1234 emp_number,    to_date('18-JAN-2009 10.41.36 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'IN' SPECIFIER from dual union all
select 1234 emp_number,    to_date('19-JAN-2009 06.26.21 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'OUT' SPECIFIER from dual union all
select 2345 emp_number,    to_date('12-JAN-2009 06.50.41 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'IN' SPECIFIER from dual union all
select 2345 emp_number,    to_date('12-JAN-2009 08.11.01 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'OUT' SPECIFIER from dual union all
select 2345 emp_number,    to_date('12-JAN-2009 09.19.53 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'IN' SPECIFIER from dual union all
select 2345 emp_number,    to_date('12-JAN-2009 02.53.26 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'OUT' SPECIFIER from dual union all
select 2345 emp_number,    to_date('13-JAN-2009 11.09.36 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'IN' SPECIFIER from dual union all
select 2345 emp_number,    to_date('14-JAN-2009 08.25.13 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'OUT' SPECIFIER from dual union all
select 2345 emp_number,    to_date('14-JAN-2009 11.06.05 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'IN' SPECIFIER from dual union all
select 2345 emp_number,    to_date('15-JAN-2009 01.31.32 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'OUT' SPECIFIER from dual union all
select 2345 emp_number,    to_date('15-JAN-2009 01.38.39 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'IN' SPECIFIER from dual union all
select 2345 emp_number,    to_date('15-JAN-2009 02.46.43 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'OUT' SPECIFIER from dual union all
select 2345 emp_number,    to_date('15-JAN-2009 02.55.23 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'IN' SPECIFIER from dual union all
select 2345 emp_number,    to_date('15-JAN-2009 05.00.07 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'OUT' SPECIFIER from dual union all
select 2345 emp_number,    to_date('15-JAN-2009 05.07.28 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'IN' SPECIFIER from dual union all
select 2345 emp_number,    to_date('15-JAN-2009 05.32.41 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'OUT' SPECIFIER from dual union all
select 2345 emp_number,    to_date('15-JAN-2009 05.41.22 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'IN' SPECIFIER from dual union all
select 2345 emp_number,    to_date('15-JAN-2009 07.46.22 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'OUT' SPECIFIER from dual union all
select 2345 emp_number,    to_date('15-JAN-2009 11.22.34 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'IN' SPECIFIER from dual union all
select 2345 emp_number,    to_date('15-JAN-2009 11.37.01 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'OUT' SPECIFIER from dual union all
select 2345 emp_number,    to_date('15-JAN-2009 11.43.49 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'IN' SPECIFIER from dual union all
select 2345 emp_number,    to_date('15-JAN-2009 11.51.02 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'OUT' SPECIFIER from dual union all
select 2345 emp_number,    to_date('15-JAN-2009 11.59.58 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'IN' SPECIFIER from dual union all
select 2345 emp_number,    to_date('16-JAN-2009 02.01.34 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'OUT' SPECIFIER from dual union all
select 2345 emp_number,    to_date('16-JAN-2009 02.08.18 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'IN' SPECIFIER from dual union all
select 2345 emp_number,    to_date('16-JAN-2009 02.35.13 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'OUT' SPECIFIER from dual union all
select 2345 emp_number,    to_date('16-JAN-2009 02.44.32 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'IN' SPECIFIER from dual union all
select 2345 emp_number,    to_date('16-JAN-2009 04.30.07 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'OUT' SPECIFIER from dual union all
select 2345 emp_number,    to_date('16-JAN-2009 04.37.14 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'IN' SPECIFIER from dual union all
select 2345 emp_number,    to_date('16-JAN-2009 05.10.55 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'OUT' SPECIFIER from dual union all
select 2345 emp_number,    to_date('16-JAN-2009 05.20.24 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'IN' SPECIFIER from dual union all
select 2345 emp_number,    to_date('16-JAN-2009 06.46.17 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'OUT' SPECIFIER from dual union all
select 2345 emp_number,    to_date('18-JAN-2009 03.06.33 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'IN' SPECIFIER from dual union all
select 2345 emp_number,    to_date('18-JAN-2009 05.31.43 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'OUT' SPECIFIER from dual union all
select 2345 emp_number,    to_date('18-JAN-2009 05.39.57 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'IN' SPECIFIER from dual union all
select 2345 emp_number,    to_date('18-JAN-2009 06.16.00 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'OUT' SPECIFIER from dual union all
select 2345 emp_number,    to_date('18-JAN-2009 08.51.06 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'IN' SPECIFIER from dual union all
select 2345 emp_number,    to_date('18-JAN-2009 11.10.15 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'OUT' SPECIFIER from dual union all
select 2345 emp_number,    to_date('19-JAN-2009 03.15.28 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'IN' SPECIFIER from dual union all
select 2345 emp_number,    to_date('19-JAN-2009 04.22.19 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'OUT' SPECIFIER from dual union all
select 2345 emp_number,    to_date('19-JAN-2009 05.17.54 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'IN' SPECIFIER from dual union all
select 2345 emp_number,    to_date('19-JAN-2009 05.19.45 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'OUT' SPECIFIER from dual union all
select 2345 emp_number,    to_date('19-JAN-2009 05.25.52 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'IN' SPECIFIER from dual union all
select 2345 emp_number,    to_date('19-JAN-2009 06.11.03 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'OUT' SPECIFIER from dual union all
select 2345 emp_number,    to_date('19-JAN-2009 09.37.27 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'IN' SPECIFIER from dual union all
select 2345 emp_number,    to_date('19-JAN-2009 10.30.48 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'OUT' SPECIFIER from dual union all
select 2345 emp_number,    to_date('20-JAN-2009 03.20.22 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'IN' SPECIFIER from dual union all
select 2345 emp_number,    to_date('20-JAN-2009 03.30.50 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'OUT' SPECIFIER from dual union all
select 2345 emp_number,    to_date('20-JAN-2009 03.44.53 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'IN' SPECIFIER from dual union all
select 2345 emp_number,    to_date('20-JAN-2009 04.12.29 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'OUT' SPECIFIER from dual union all
select 2345 emp_number,    to_date('20-JAN-2009 04.20.56 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'IN' SPECIFIER from dual union all
select 2345 emp_number,    to_date('20-JAN-2009 10.56.11 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT,    'OUT' SPECIFIER from dual;


Here is the query I used

Code: Select all

with
  date_query as (
    SELECT emp_number,
           day
    FROM   (SELECT distinct emp_number
            FROM   real_data
            --WHERE  emp_number IN (1234, 2345)
            )
            CROSS JOIN
           (SELECT 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,
    time_in_out,
           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 IN (1234, 2345)
and trunc(time_in_out)>=to_date('12-JAN-2009','DD-MON-YYYY') and
    trunc(time_in_out)<=to_date('20-JAN-2009','DD-MON-YYYY')
ORDER BY 1,2,3;


Guess we could get this time working.

Thanks a lot Tim, appreciated.

[Tim...]

Hi.

By problem with real data I assume you mean the blank rows are missing...

Youyve added this extra predicate on compared to my statement.

Code: Select all

and trunc(time_in_out)>=to_date('12-JAN-2009','DD-MON-YYYY') and
    trunc(time_in_out)<=to_date('20-JAN-2009','DD-MON-YYYY')

Considering you want blank rows for all the days without data, what do you think will happen if you start filtering on time_in_out which is blank on those days? Yes. That's right you will lose them!

You should control the number of days displayed by adjusting the values in the date_query.

Code: Select all

with
  date_query as (
    SELECT emp_number,
           day
    FROM   (SELECT distinct emp_number
            FROM   real_data
            WHERE  emp_number IN (1234, 2345)
            )
            CROSS JOIN
           (SELECT TO_DATE('12-JAN-2009', 'DD-MON-YYYY') + LEVEL - 1 AS day
            FROM   dual
            CONNECT BY LEVEL <= (TO_DATE('20-JAN-2009', 'DD-MON-YYYY') - TO_DATE('12-JAN-2009', 'DD-MON-YYYY') + 1))
  ),
  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,
    time_in_out,
           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
ORDER BY 1,2,3;

I've amended the query. All you should ever have to do is amend the WHERE clause and dates in the date_query portion of the query. You don't need additional WHERE clause predicates.

Cheers

Tim...

[ponic]

Thanks Time for pointing out. However when I ran your amended sql, my output is in correct. The date_query part is only change I guess.
Here is my output.

Code: Select all

EMP_NUMBER    DAY    EVENT_ORDER    TIME_IN    TIME_OUT
1234    12-JAN-2009 12.00.00 AM    12-JAN-2009 12.00.00 AM    NIL    NIL
1234    13-JAN-2009 12.00.00 AM    13-JAN-2009 12.00.00 AM    NIL    NIL
1234    14-JAN-2009 12.00.00 AM    14-JAN-2009 12.00.00 AM    NIL    NIL
1234    15-JAN-2009 12.00.00 AM    15-JAN-2009 12.00.00 AM    NIL    NIL
1234    16-JAN-2009 12.00.00 AM    16-JAN-2009 12.00.00 AM    NIL    NIL
1234    17-JAN-2009 12.00.00 AM    17-JAN-2009 12.00.00 AM    NIL    NIL
1234    18-JAN-2009 12.00.00 AM    18-JAN-2009 12.00.00 AM    NIL    NIL
1234    19-JAN-2009 12.00.00 AM    19-JAN-2009 07.06.00 AM    19-JAN-09 07.06.00 AM    NIL
1234    19-JAN-2009 12.00.00 AM    19-JAN-2009 08.50.40 AM    19-JAN-09 08.50.40 AM    NIL
1234    19-JAN-2009 12.00.00 AM    19-JAN-2009 10.07.18 AM    19-JAN-09 10.07.18 AM    NIL
1234    20-JAN-2009 12.00.00 AM    20-JAN-2009 12.00.00 AM    NIL    NIL


I ran first with two customers, having getting in correct results, I ran for a single customer and still results were in correct.
What could be the reason. Forgive for troubling you again.

Thanks

[Tim...]

Hi.

Please use "set tab off" before running your statement. That way the formatting will make sense when you put it in the code tag. Pretty hard to read what you posted.

Please, explain what areas of the data you consider wrong also. Do you mean that you have in records without corresponding out records? If so, then I think this is down to missing rows in your data. I don't see that with the test data you sent me.

Cheers

Tim...

[ponic]

Tim,

I will explain as why I am getting in-correct results.
Let me first show data for two customers.
Customer 1234

Code: Select all

EMP_NUMBER          TIME_IN_OUT       SPECIFIER
1234         12-JAN-2009 06.50.41 AM       IN
1234         12-JAN-2009 08.11.01 AM       OUT
1234         12-JAN-2009 09.19.53 AM       IN
1234         12-JAN-2009 02.53.26 PM       OUT
1234         13-JAN-2009 11.09.36 PM       IN
1234         14-JAN-2009 08.25.13 AM       OUT
1234         14-JAN-2009 11.06.05 PM       IN
1234         15-JAN-2009 01.31.32 AM       OUT
1234         15-JAN-2009 01.38.39 AM       IN
1234         15-JAN-2009 02.46.43 AM       OUT
1234         15-JAN-2009 02.55.23 AM       IN
1234         15-JAN-2009 05.00.07 AM       OUT
1234         15-JAN-2009 05.07.28 AM       IN
1234         15-JAN-2009 05.32.41 AM       OUT
1234         15-JAN-2009 05.41.22 AM       IN
1234         15-JAN-2009 07.46.22 AM       OUT
1234         15-JAN-2009 11.22.34 PM       IN
1234         15-JAN-2009 11.37.01 PM       OUT
1234         15-JAN-2009 11.43.49 PM       IN
1234         15-JAN-2009 11.51.02 PM       OUT
1234         15-JAN-2009 11.59.58 PM       IN
1234         16-JAN-2009 02.01.34 AM       OUT
1234         16-JAN-2009 02.08.18 AM       IN
1234         16-JAN-2009 02.35.13 AM       OUT
1234         16-JAN-2009 02.44.32 AM       IN
1234         16-JAN-2009 04.30.07 AM       OUT
1234         16-JAN-2009 04.37.14 AM       IN
1234         16-JAN-2009 05.10.55 AM       OUT
1234         16-JAN-2009 05.20.24 AM       IN
1234         16-JAN-2009 06.46.17 AM       OUT
1234         18-JAN-2009 03.06.33 PM       IN
1234         18-JAN-2009 05.31.43 PM       OUT
1234         18-JAN-2009 05.39.57 PM       IN
1234         18-JAN-2009 06.16.00 PM       OUT
1234         18-JAN-2009 08.51.06 PM       IN
1234         18-JAN-2009 11.10.15 PM       OUT
1234         19-JAN-2009 03.15.28 PM       IN
1234         19-JAN-2009 04.22.19 PM       OUT
1234         19-JAN-2009 05.17.54 PM       IN
1234         19-JAN-2009 05.19.45 PM       OUT
1234         19-JAN-2009 05.25.52 PM       IN
1234         19-JAN-2009 06.11.03 PM       OUT
1234         19-JAN-2009 09.37.27 PM       IN
1234         19-JAN-2009 10.30.48 PM       OUT
1234         20-JAN-2009 03.20.22 PM       IN
1234         20-JAN-2009 03.30.50 PM       OUT
1234         20-JAN-2009 03.44.53 PM       IN
1234         20-JAN-2009 04.12.29 PM       OUT
1234         20-JAN-2009 04.20.56 PM       IN
1234         20-JAN-2009 10.56.11 PM       OUT


Customer 2345

Code: Select all

EMP_NUMBER      TIME_IN_OUT       SPECIFIER
2345      13-JAN-2009 07.05.50 AM      IN
2345      13-JAN-2009 02.38.40 PM      OUT
2345      14-JAN-2009 07.20.54 AM      IN
2345      14-JAN-2009 11.47.45 AM      OUT
2345      14-JAN-2009 11.49.15 AM      IN
2345      14-JAN-2009 04.03.33 PM      OUT
2345      16-JAN-2009 07.18.55 AM      IN
2345      16-JAN-2009 02.29.19 PM      OUT
2345      16-JAN-2009 10.25.31 PM      IN
2345      17-JAN-2009 06.39.00 AM      OUT
2345      17-JAN-2009 09.00.16 PM      IN
2345      18-JAN-2009 06.20.24 AM      OUT
2345      18-JAN-2009 10.41.36 PM      IN
2345      19-JAN-2009 06.26.21 AM      OUT


Now when I execute with both customers I am getting the following output

For customer 1234

On 13-JAN-2009 there is a IN_TIME at 13-JAN-09 11.09.36 PM, but there is no OUT_TIME, ideally OUT_TIME should be
14-JAN-2009 08.25.13 AM
On 18-JAN-2009 there is an IN_TIME at 18-JAN-09 08.51.06 PM, but there is no OUT_TIME, ideally OUT_TIME should be
18-JAN-2009 11.10.15 PM

For customer 2345

On 14-JAN-2009, there is an IN_TIME at 14-JAN-2009 07.20.54 AM, but OUT_TIME is shown as 14-JAN-09 08.25.13 AM instead of
14-JAN-2009 11.47.45 AM
On 18-JAN-2009, there is an IN_TIME at 18-JAN-09 10.41.36 PM, but OUT_TIME is shown as 18-JAN-09 11.10.15 PM instead of
19-JAN-2009 06.26.21 AM

What could be the reason, is this because same date having multiple timings?

Thanks Tim for the patience.
Update 1
Data for 1234

Code: Select all

Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('12-JAN-2009 06.50.41 AM','DD-MON-RRRR HH.MI.SS AM'),'IN');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('12-JAN-2009 08.11.01 AM','DD-MON-RRRR HH.MI.SS AM'),'OUT');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('12-JAN-2009 09.19.53 AM','DD-MON-RRRR HH.MI.SS AM'),'IN');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('12-JAN-2009 02.53.26 PM','DD-MON-RRRR HH.MI.SS AM'),'OUT');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('13-JAN-2009 11.09.36 PM','DD-MON-RRRR HH.MI.SS AM'),'IN');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('14-JAN-2009 08.25.13 AM','DD-MON-RRRR HH.MI.SS AM'),'OUT');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('14-JAN-2009 11.06.05 PM','DD-MON-RRRR HH.MI.SS AM'),'IN');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('15-JAN-2009 01.31.32 AM','DD-MON-RRRR HH.MI.SS AM'),'OUT');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('15-JAN-2009 01.38.39 AM','DD-MON-RRRR HH.MI.SS AM'),'IN');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('15-JAN-2009 02.46.43 AM','DD-MON-RRRR HH.MI.SS AM'),'OUT');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('15-JAN-2009 02.55.23 AM','DD-MON-RRRR HH.MI.SS AM'),'IN');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('15-JAN-2009 05.00.07 AM','DD-MON-RRRR HH.MI.SS AM'),'OUT');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('15-JAN-2009 05.07.28 AM','DD-MON-RRRR HH.MI.SS AM'),'IN');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('15-JAN-2009 05.32.41 AM','DD-MON-RRRR HH.MI.SS AM'),'OUT');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('15-JAN-2009 05.41.22 AM','DD-MON-RRRR HH.MI.SS AM'),'IN');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('15-JAN-2009 07.46.22 AM','DD-MON-RRRR HH.MI.SS AM'),'OUT');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('15-JAN-2009 11.22.34 PM','DD-MON-RRRR HH.MI.SS AM'),'IN');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('15-JAN-2009 11.37.01 PM','DD-MON-RRRR HH.MI.SS AM'),'OUT');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('15-JAN-2009 11.43.49 PM','DD-MON-RRRR HH.MI.SS AM'),'IN');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('15-JAN-2009 11.51.02 PM','DD-MON-RRRR HH.MI.SS AM'),'OUT');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('15-JAN-2009 11.59.58 PM','DD-MON-RRRR HH.MI.SS AM'),'IN');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('16-JAN-2009 02.01.34 AM','DD-MON-RRRR HH.MI.SS AM'),'OUT');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('16-JAN-2009 02.08.18 AM','DD-MON-RRRR HH.MI.SS AM'),'IN');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('16-JAN-2009 02.35.13 AM','DD-MON-RRRR HH.MI.SS AM'),'OUT');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('16-JAN-2009 02.44.32 AM','DD-MON-RRRR HH.MI.SS AM'),'IN');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('16-JAN-2009 04.30.07 AM','DD-MON-RRRR HH.MI.SS AM'),'OUT');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('16-JAN-2009 04.37.14 AM','DD-MON-RRRR HH.MI.SS AM'),'IN');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('16-JAN-2009 05.10.55 AM','DD-MON-RRRR HH.MI.SS AM'),'OUT');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('16-JAN-2009 05.20.24 AM','DD-MON-RRRR HH.MI.SS AM'),'IN');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('16-JAN-2009 06.46.17 AM','DD-MON-RRRR HH.MI.SS AM'),'OUT');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('18-JAN-2009 03.06.33 PM','DD-MON-RRRR HH.MI.SS AM'),'IN');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('18-JAN-2009 05.31.43 PM','DD-MON-RRRR HH.MI.SS AM'),'OUT');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('18-JAN-2009 05.39.57 PM','DD-MON-RRRR HH.MI.SS AM'),'IN');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('18-JAN-2009 06.16.00 PM','DD-MON-RRRR HH.MI.SS AM'),'OUT');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('18-JAN-2009 08.51.06 PM','DD-MON-RRRR HH.MI.SS AM'),'IN');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('18-JAN-2009 11.10.15 PM','DD-MON-RRRR HH.MI.SS AM'),'OUT');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('19-JAN-2009 03.15.28 PM','DD-MON-RRRR HH.MI.SS AM'),'IN');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('19-JAN-2009 04.22.19 PM','DD-MON-RRRR HH.MI.SS AM'),'OUT');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('19-JAN-2009 05.17.54 PM','DD-MON-RRRR HH.MI.SS AM'),'IN');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('19-JAN-2009 05.19.45 PM','DD-MON-RRRR HH.MI.SS AM'),'OUT');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('19-JAN-2009 05.25.52 PM','DD-MON-RRRR HH.MI.SS AM'),'IN');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('19-JAN-2009 06.11.03 PM','DD-MON-RRRR HH.MI.SS AM'),'OUT');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('19-JAN-2009 09.37.27 PM','DD-MON-RRRR HH.MI.SS AM'),'IN');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('19-JAN-2009 10.30.48 PM','DD-MON-RRRR HH.MI.SS AM'),'OUT');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('20-JAN-2009 03.20.22 PM','DD-MON-RRRR HH.MI.SS AM'),'IN');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('20-JAN-2009 03.30.50 PM','DD-MON-RRRR HH.MI.SS AM'),'OUT');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('20-JAN-2009 03.44.53 PM','DD-MON-RRRR HH.MI.SS AM'),'IN');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('20-JAN-2009 04.12.29 PM','DD-MON-RRRR HH.MI.SS AM'),'OUT');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('20-JAN-2009 04.20.56 PM','DD-MON-RRRR HH.MI.SS AM'),'IN');
Insert into real_data (EMP_NUMBER,TIME_IN_OUT,SPECIFIER) values (1234,to_date('20-JAN-2009 10.56.11 PM','DD-MON-RRRR HH.MI.SS AM'),'OUT');


for 2345

Code: Select all

Insert into real_data (EMP_NUMBER,time_in_out,specifier) values ('2345',to_date('13-JAN-2009 07.05.50 AM','DD-MON-RRRR HH.MI.SS AM'),'IN');
Insert into real_data (EMP_NUMBER,time_in_out,specifier) values ('2345',to_date('13-JAN-2009 02.38.40 PM','DD-MON-RRRR HH.MI.SS AM'),'OUT');
Insert into real_data (EMP_NUMBER,time_in_out,specifier) values ('2345',to_date('14-JAN-2009 07.20.54 AM','DD-MON-RRRR HH.MI.SS AM'),'IN');
Insert into real_data (EMP_NUMBER,time_in_out,specifier) values ('2345',to_date('14-JAN-2009 11.47.45 AM','DD-MON-RRRR HH.MI.SS AM'),'OUT');
Insert into real_data (EMP_NUMBER,time_in_out,specifier) values ('2345',to_date('14-JAN-2009 11.49.15 AM','DD-MON-RRRR HH.MI.SS AM'),'IN');
Insert into real_data (EMP_NUMBER,time_in_out,specifier) values ('2345',to_date('14-JAN-2009 04.03.33 PM','DD-MON-RRRR HH.MI.SS AM'),'OUT');
Insert into real_data (EMP_NUMBER,time_in_out,specifier) values ('2345',to_date('16-JAN-2009 07.18.55 AM','DD-MON-RRRR HH.MI.SS AM'),'IN');
Insert into real_data (EMP_NUMBER,time_in_out,specifier) values ('2345',to_date('16-JAN-2009 02.29.19 PM','DD-MON-RRRR HH.MI.SS AM'),'OUT');
Insert into real_data (EMP_NUMBER,time_in_out,specifier) values ('2345',to_date('16-JAN-2009 10.25.31 PM','DD-MON-RRRR HH.MI.SS AM'),'IN');
Insert into real_data (EMP_NUMBER,time_in_out,specifier) values ('2345',to_date('17-JAN-2009 06.39.00 AM','DD-MON-RRRR HH.MI.SS AM'),'OUT');
Insert into real_data (EMP_NUMBER,time_in_out,specifier) values ('2345',to_date('17-JAN-2009 09.00.16 PM','DD-MON-RRRR HH.MI.SS AM'),'IN');
Insert into real_data (EMP_NUMBER,time_in_out,specifier) values ('2345',to_date('18-JAN-2009 06.20.24 AM','DD-MON-RRRR HH.MI.SS AM'),'OUT');
Insert into real_data (EMP_NUMBER,time_in_out,specifier) values ('2345',to_date('18-JAN-2009 10.41.36 PM','DD-MON-RRRR HH.MI.SS AM'),'IN');
Insert into real_data (EMP_NUMBER,time_in_out,specifier) values ('2345',to_date('19-JAN-2009 06.26.21 AM','DD-MON-RRRR HH.MI.SS AM'),'OUT');


Regards

[Tim...]

Hi.

I understand what you are saying. I can see the mistakes in the output. I can't see why it is happening. You should break the query down into it's constituent parts, adding one layer at a time and see if you can see where the pattern starts to break down. I had a quick look, but I can't see it.

The weird thing is, the dates being displayed don't seem to be present in the source data at all as far as I can see.

Cheers

Tim...

[Tim...]

Hi.

Try this:

Code: Select all

with
  date_query as (
    SELECT emp_number,
           day
    FROM   (SELECT distinct emp_number
            FROM   real_data
            WHERE  emp_number IN (1234, 2345)
            )
            CROSS JOIN
           (SELECT TO_DATE('12-JAN-2009', 'DD-MON-YYYY') + LEVEL - 1 AS day
            FROM   dual
            CONNECT BY LEVEL <= (TO_DATE('20-JAN-2009', 'DD-MON-YYYY') - TO_DATE('12-JAN-2009', 'DD-MON-YYYY') + 1))
    ORDER BY 1,2
  ),
  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 (PARTITION BY emp_number ORDER BY time_in_out) AS time_in_prev,
           LEAD(time_out, 1, NULL) OVER (PARTITION BY emp_number ORDER BY time_in_out) AS time_out_next
    FROM   pivot_query
    ORDER by 1,2
  ),
  final_query as (
    SELECT aq.emp_number,
           aq.time_in_out,
           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))
           )
    ORDER BY 1,2
  )
SELECT dq.emp_number,
       dq.day,
       fq.time_in_out 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
ORDER BY 1,2,3;

I was missing the PARTITION BY clause in the analytic functions. I think some edge data was bleeding between customers.

I've got some stuff I need to catch up on over the weekend. If this isn't working it will probably be a couple of days before I can look at it again.

Cheers

Tim...

[ponic]

I've got some stuff I need to catch up on over the weekend. If this isn't working it will probably be a couple of days before I can look at it again.

Thanks a lot for this reply Tim, I really appreciate this.

I will try with your solution and will post my reply. Have a nice weekend.

Thanks

[ponic]

Hi Tim,

PARTITION BY which you added in your last post did solve the problem.

Data looks fine so far.

Thanks once again for the all the help,support and patience.

Much appreciated.

Regards

[Tim...]

OK.