Query problem 1

[shams123]

Hi all,

I have 3 tables:

1. Orders (where all the order details are stored)

2. Status_Lookup (where all the possible statues are stored, like Received, Pending, Dispatched, Closed)

3. Order_Status (where every time the order status changes, its recorded).

E.g

Orders: Status_Lookup Order_Status

Order_Id Id Status Order_Id Id DateAdded
1 1 Received 1 1 1/1/2003
2 2 Pending 1 2 1/2/2003
3 3 Dispatched 2 2 3/2/2004
4 Closed 3 4 5/4/2005

Now I actually want to display individual orders with their current statuses. E.g

Order_Id Status Status_Date
1 Pending 1/2/2003
2 Pending 3/2/2003
3 Closed 5/4/2005

Any help over the query will be highly appreciated!

Thanks!

 

[r937]

select O.Order_Id
     , SL.Status
     , OS.DateAdded
  from Orders as O 
inner
  join Order_Status as OS
    on OS.Order_Id = O.Order_Id
   and OS.DateAdded =
     ( select max(DateAdded)
         from Order_Status
        where Order_Id = O.Order_Id )  
inner
  join Status_Lookup as SL
    on SL.Id = OS.Id

r937.com | rudy.ca

 

[shams123]

Thanks mate!!

 

[shams123]

Hi mate,

In the above scenario, if I want to display individual orders that were closed within the last 14 days. E.g

Order_Id Status Status_Date
2 Closed 4/10/2006

I would appreciate your help.

 

[r937]

select O.Order_Id
     , SL.Status
     , OS.DateAdded
  from Orders as O 
inner
  join Order_Status as OS
    on OS.Order_Id = O.Order_Id
   [COLOR=red]and OS.DateAdded 
       > current_date - interval '7' day [/color]
inner
  join Status_Lookup as SL
    on SL.Id = OS.Id 
   [COLOR=red]and SL.Status = 'Closed'[/color]

please be aware that you asked this question in the ANSI SQL forum, and so you got an ANSI SQL answer -- you will likely need to change the date calculation to use the specific syntax for your particular database system

r937.com | rudy.ca

 

[shams123]

Hi mate,

Thanks for the post. A couple of questions:

1. I can't seem to get the "interval" function working, errors out.

2. This wouldn't select the max statusdate, would it? like the one you posted earlier?

Thanks!

 

[r937]

1. please read what i said about ANSI SQL

2. no, it doesn't

r937.com | rudy.ca