One SQL Query for 3 set of results?

[Pozzi]

A brief overview of what I'm trying to achieve...

The tb_transactions table holds all transactions made by any operator. To track some time and attendance of operators there are two transactions that have Causal_ID's of 2 (Clock-In) and 3 (Clock-Out).

It is possible for an operator to carryout more than one of each type of transaction with a Causal_ID of 2 or 3 within the same trans_date.

I'm trying to put together an Excel spreadsheet that allows a manager to view the time and attendance of the operators.

The manager will want to do the following: -

1) Show period for an operator, showing the first Clock-In and Last Clock-Out by trans_date within the period.
2) as above but all opeators.
3) as 1 & 2 but just for the one day (can still use the period, although the period start & end the same).

I'm trying to get one SQL statement that can be used for all 3 of the above.

Any help or advice appreciated & thanks for taking time out to view.

Regards

 

[rcurva]

I don't exactly know the structure of your table but here's a guess from what you have said:

SELECT operator_id, MIN(trans_date), MAX(trans_date)
FROM tb_transactions
WHERE casual_id IN (2,3)
AND operator_id = NVLv_oper_id,operator_id)
AND TRUNC(trans_date) = NVLv_trans_dt, TRUNC(trans_date))
GROUP BY operator_id;

There are two variables introduced in this SQL for operator and transaction date. If you don't specify an operator, it will list all operators. Same with the trans_date, if not specified, it will consider all.

I did not actually consider here if you want to enter a begin and end date for the transactions.
Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War