I need help for a query

[zekmek]

Hello everybody,

Here is my problem:
I've got 2 tables : BO_BOOKING and BO_WORKFLOW.

desc BO_BOOKING
=>>> BOOKING_ID (VARCHAR) primary key

desc BO_WORKFLOW
=>>> WORKFLOW_ID (VARCHAR) primary key
BOOKING_ID (VARCHAR) foreign key
WORKFLOW_DATE (DATE)

These two tables are linked by a 1,n relation. This means that a reservation from BO_BOOKING can appear a lot of times in BO_WORKFLOW.

Example of what can BO_WORKFLOW contain:

WORFLOW_ID BOOKING_ID WORKFLOW_DATE WORKFLOW_STATUS
PKGE-1705_16864 PKGE-1705 23/02/2004 19:01 0
PKGE-1705_35785 PKGE-1705 23/02/2004 19:08 0
PKGE-1705_15410 PKGE-1705 24/02/2004 09:40 0
PKGE-1705_20739 PKGE-1705 24/02/2004 09:45 1
PKGE-1709_23111 PKGE-1709 24/02/2004 09:51 0
PKGE-1710_13787 PKGE-1710 24/02/2004 09:59 0
PKGE-1712_64868 PKGE-1712 24/02/2004 10:20 0
PKGE-12345_6202 PKGE-1234 11/03/2004 15:59 1
PKGE-12345_2665 PKGE-1234 11/03/2004 16:04 2
PKGE-12345_8611 PKGE-1234 11/03/2004 16:39 0
PKGE-12345_6509 PKGE-1234 11/03/2004 16:52 2
PKGE-12345_9529 PKGE-1234 11/03/2004 14:52 1
PKGE-12345_6138 PKGE-1234 11/03/2004 15:25 2
PKGE-12345_9012 PKGE-1234 11/03/2004 15:25 2
PKGE-12345_8759 PKGE-1234 11/03/2004 15:25 2
PKGE-12345_1273 PKGE-1234 11/03/2004 15:25 2
PKGE-12345_7782 PKGE-1234 11/03/2004 15:25 2
PKGE-12345_7898 PKGE-1234 11/03/2004 15:25 0

And now what i need to obtai is the list of all the BOOKING_ID where the WORKFLOW_STATUS = 0 AND only for the more recent date when the BOOKING_ID appears several times...

So according to this example, i want:
PKGE-1705_1541 PKGE-1705 24/02/2004 09:40 0
PKGE-1709_2311 PKGE-1709 24/02/2004 09:51 0
PKGE-1710_1378 PKGE-1710 24/02/2004 09:59 0
PKGE-1712_6486 PKGE-1712 24/02/2004 10:20 0
PKGE-12345_789 PKGE-1234 11/03/2004 15:25 0

Voila...

Merci beaucoup

 

[SantaMufasa]

Zekmek,

We are not quite ready for the "Voila" yet...Your result specifications seem either not to match your specifications or are ambiguous. Here are my puzzlements:

Given your original sample table values, why does the output show this row:
PKGE-12345_789 PKGE-1234 11/03/2004 15:25 0

instead of this row:
PKGE-12345_861 PKGE-1234 11/03/2004 16:39 0

Also, Why is the last digit missing from each display value of "WORFLOW_ID" output?

Presuming that your sample output is incorrect for PKGE-1234, here are code and results of the code for your request:

Col a heading “WORKFLOW_ID” format a14
Col b heading “BOOKING_ID” format a10
Col c heading “WORKFLOW_DATE” format a16
Col d heading “WORKFLOW|STATUS” format 9
Select	substr(workflow_id,1,14) a
	,booking_id b
	,to_char(workflow_date,’dd/mm/yyyy hh24:mi’) c
	,workflow_status d
from bo_workflow
where (workflow_date,booking_id) in 
(select max(workflow_date),booking_id
from bo_workflow
where workflow_status = 0
group by booking_id);

                                           WORKFLOW
WORKFLOW_ID    BOOKING_ID WORKFLOW_DATE      STATUS
-------------- ---------- ---------------- --------
PKGE-1705_1541 PKGE-1705  24/02/2004 09:40        0
PKGE-1709_2311 PKGE-1709  24/02/2004 09:51        0
PKGE-1710_1378 PKGE-1710  24/02/2004 09:59        0
PKGE-1712_6486 PKGE-1712  24/02/2004 10:20        0
PKGE-12345_861 PKGE-1234  11/03/2004 16:39        0

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:11 (13Mar04) UTC (aka "GMT" and "Zulu"), 10:11 (13Mar04) Mountain Time)