Tim... wrote: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.
Cheers
Tim...
Tim,
Is it possible to remove this part from the query?
- Code: Select all
with
date_query as
As I am planning to generalize this query and make a view for this,remove hard coded dates and employees.
