need help on 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

Thanks a lot ;-)

 

[byam]

try this:

select * from  BO_WORKFLOW
where WORKFLOW_STATUS = 0 
      and WORKFLOW_DATE > '10/03/2004' 
group by BOOKING_ID 
      having count(BOOKING_ID) > 0

 

[sedj]

select workflow_id, booking_id, max(workflow_date), workflow_status
from bo_workflow
group by booking_id;

 

[sedj]

Should be :

select workflow_id, booking_id, max(workflow_date), workflow_status
from bo_workflow 
where workflow_status = 0
group by booking_id;

I don't believe byam's example will return the max date every time though, as it relies on a where clause on workflow_date.

 

[byam]

You are right sedi, my example does not return the max date. I miss understood zekmek's question on the max date.