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 !

 

[zhavic]

Try this:

SELECT *
FROM BO_BOOKING
INNER JOIN BO_WORKFLOW ON BO_WORKFLOW.BOOKING_ID = BO_BOOKING.BOOKING_ID
WHERE WORFLOW_ID IN ( SELECT TOP 1 a.WORFLOW_ID FROM BO_WORKFLOW a
WHERE a.WORKFLOW_STATUS = 0 AND a.BOOKING_ID = BO_BOOKING.BOOKING_ID
ORDER BY a.WORKFLOW_DATE DESC )



Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[tektipdjango]

try this piece of code and let me know if it works (I have not SQL SERVER at the moment)

[b]SELECT[/b]
BOOKING_ID,
WORKFLOW_ID,
WORKFLOW_DATE
[b]FROM[/b]
(
  [b]SELECT[/b]
  BOOKING_ID [b]AS[/b] lastBOOKING_ID,
  [b]max[/b](WORKFLOW_DATE)[b]AS[/b] lastDATE
  [b]FROM[/b]
  BO_WORKFLOW
  [b]WHERE[/b] WORKFLOW_STATUS = 0
  [b]GROUP BY[/b] BOOKING_ID
)[b]AS[/b] lastWORKFLOW
[b]INNER JOIN[/b]
BO_WORKFLOW  
[b]ON[/b] (lastBOOKING_ID=BOOKING_ID) [b]and[/b] (lastDATE=WORKFLOW_DATE)

I think the speed of this query will be a lot quicker as there will be only 2 queries, the derived one and the main query.

On the other hand, the solution with the '[highlight]IN[/highlight]' statement launch a subQuery for each record of the main query

[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict

 

[obislavu]

Here is the query that will work in MSSQL and Oracle:

SELECT 
 a.WORKFLOW_ID
,a.BOOKING_ID
,a.WORKFLOW_DATE
,a.WORKFLOW_STATUS 
FROM
 BO_WORKFLOW as a
,(SELECT max(t2.WORKFLOW_DATE) as lastdate, t2.BOOKING_ID from BO_BOOKING as t1, BO_WORKFLOW as t2 where t1.BOOKING_ID = t2.BOOKING_ID and t2.WORKFLOW_STATUS = 0 group by t2.BOOKING_ID ) as b
WHERE a.BOOKING_ID = b.BOOKING_ID
   and b.lastdate = a.WORKFLOW_DATE

-obislavu-