SQL Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Query

Author

Topic

actuary
Starting Member

23 Posts

Posted - 2006-10-05 : 08:15:31

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/9/2005
1 4 4/10/2006

I want to display all the orders that were closed in the last 14 days (I have to select the maximum dateadded like if an order was opened after closing, I can't show that). E.g

Order_Id Status Status_Date
1 Closed 4/10/2006

Any help would be appreciated.

Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-05 : 08:35:14

select o.* from orders o
where o.orderid in (select os.orderid from order_status os where os.status = 'closed' and os.status_date >= dateadd(day, -14, getdate())

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-05 : 11:39:38

or

select o.* from orders o
inner join order_status os
on o.orderid =os.orderid
where os.status = 'closed' and
os.status_date >= dateadd(day, -14, getdate())

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources