CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Mowgli
Mowgli

Reputation: 3512

how to perform this step in sql query?

I am currently doing this excel via VBA, but if I can get some help to do it with in query would be really save me lots of time.

This is 1 of like 5 things I need to do for this report I am working on.

Tables
I am reading from 3 different tables

TableA   TableB  TableC

Fields from tableA

OUT_ID

Fields from tableB

timestamp
event_type
worker
operator_id
new_date

Fields from tableC 

worker

Query

Select
       TableA.OUT_ID,
       TableB.timestamp,
       TableB.new_date,
       TableB.event_type,
       TableC.worker,
       TableB.operator_id

From TableA left outer join
        TableB 
           ON TableA.OUT_ID = TableB.OUT_ID and
                 TableB.event_type in ('WORKER_RET_CMT_CHANCE','RET_CHANCE','WORKER_STATUS_CHANCE') Left OUTER JOIN 
              TableC 
                ON TableB.worker = TableC.worker
 where  TableA.time_stamp > {?PickDate} //parameter in crystal report to pick date
    and TableA.time_stamp < {?RestDate}  //parameter in crystal report to pick date 
   order by TableA.OUT_ID, TableB.timestamp

Here is what I need to do.

In Event_type column if RET_CHANCE has WORKER_RET_COMMENT_CHANCE in next row then check if OUT_ID is same, if it is same than check if TIMESTAMP difference is less than 10 seconds, if all this is true than copy WORKER number from yellow cell to red cell.

Please see image blow and attached excel file for sample. Download excel file via dropbox

enter image description here

Upvotes: 2

Views: 134

Answers (2)

Conrad Frix
Conrad Frix

Reputation: 52645

Seeing that you have a "next row" requirement it seemss like a good time to use the LEAD function.

As the documentation states

LEAD is an analytic function. It provides access to more than one row of a table at the same time without a self join. Given a series of rows returned from a query and a position of the cursor, LEAD provides access to a row at a given physical offset beyond that position

So in order to get the "next event", "next timestamp" and "next worker" we need do do 

LEAD(timestamp) OVER (ORDER BY ???) next_timestamp, 
LEAD(EVENT_TYPE) OVER (ORDER BY ???) next_event,
LEAD(WORKER) OVER (ORDER BY ??) NEXT_WORKER

The ORDER BY Clause here tells us what the "next row" is. Since you had 

order by TableA.OUT_ID, TableB.timestamp

We could do 

... OVER (ORDER BY OUT_ID, timestamp)

that but you also had the requirement "check if OUT_ID is the same". We can add the optional PARTITION BY clause which will ensure that the "next record" is used for the same OUT_ID

... OVER (PARTITION BY OUT_ID ORDER BY timestamp)

Once we have all the "Next Fields" we can you use a CASE statement to fullfill the remaining requirements

  • If the Event_type is RET_CHANCE
  • And the Event Type in the next row is WORKER_RET_COMMENT_CHANCE
  • And the TimeStamps from the two row is less then 10 seconds
  • Then use the worker from the next row

The following query is a demonstration of these ideas. 


WITH Data AS (
SELECT 
  OUT_ID,
  timestamp,
  LEAD(timestamp) OVER (PARTITION BY Out_ID ORDER BY TimeStamp) next_timestamp, 
  Event_type,
  LEAD(EVENT_TYPE) OVER (PARTITION BY Out_ID ORDER BY TimeStamp) next_event,
  WORKER,
  LEAD(WORKER) OVER (PARTITION BY Out_ID ORDER BY TimeStamp) NEXT_WORKER
FROM TableB
ORDER BY 
   OUT_ID,
   timestamp
)
SELECT 

  OUT_ID,
  timestamp,
  next_timestamp, 
  Event_type,
  next_event,
  WORKER,
  NEXT_WORKER,
( CAST( next_timestamp AS DATE ) - CAST( timestamp AS DATE ) ) * 86400 DIFF,
  CASE WHEN 
        EVENT_TYPE ='RET_CHANCE'
        AND next_event = 'WORKER_STATUS_CHANCE'
        AND ( CAST( next_timestamp AS DATE ) - CAST( timestamp AS DATE ) ) * 86400  < 10 
       THEN NEXT_WORKER
       ELSE WORKER
   END as CALC_WORKER
FROM 
  data

Notes:

  • You don't need to use the with clause here but it would make the CASE statement unreadable.
  • I left in all the NEXT_* fields so that you can see how the query works.
  • Using Jeffrey Kemp's answer for calculating the seconds difference

DEMO

Upvotes: 1

PM 77-1
PM 77-1

Reputation: 13334

This is not a complete answer but just an attempt to clarify the task. It requires some code that will not be very readable in comments. So (everybody) please bear with me.

Will the following SELF JOIN create the correct pairing of the records:

FROM View1 v1
JOIN View1 v2 ON v1.out_id = v2.out_id AND 
                 v1.event_type = 'RET_CHANCE' AND v2.event_type = 'WORKER_RET_COMMENT_CHANCE' AND              
                 ABS(EXTRACT(SECOND FROM v1.timestamp) - EXTRACT(SECOND FROM v2.timestamp)) < 10

Upvotes: 1

Related Questions