| Collecting data from 2 tables - please help [message #18430] |
Wed, 30 January 2002 23:16  |
Fiesel
Messages: 3
Registered: January 2002
|
Junior Member |
|
|
Hi,
i hope someone is able to help me.
I have to tables, one containing unique records for item data, the other contains the
moves of the items into and out of the stores.
I need all items which are in store No 1 and quantity is 0 and the last
date of a move is more than 6 months ago, to find items which are no longer in use.
Table1 Store
Item_No
Store_No
Quantity
Table 2 Journal
Item_No
Moving_Date
Store_No
i tried it many different ways now, but it wont work...
select L.item_no, L.quantity from
(select max(J.moving_date) from journal J where (J.store_no = 1)
and (J.moving_date > add_months(sysdate,6))
group by J.item_no), Store L, Journal J
where (L.quantity = 0) and (l.store_no = 1) and (L.item_No = J.item_No);
|
|
|
|
|
|
| Re: Collecting data from 2 tables - please help [message #18440 is a reply to message #18430] |
Thu, 31 January 2002 03:02  |
Mike
Messages: 417
Registered: September 1998
|
Senior Member |
|
|
SELECT s.item_no
FROM
(SELECT j.item_no,
MAX(J.moving_date) last_move
FROM journal j
WHERE j.store_no = 1
GROUP BY j.item_no
) l,
store s
WHERE s.quantity = 0
AND s.store_no = 1
AND s.item_No = l.item_No
AND l.last_move < add_months(sysdate,-6);
I guess this is what you want
Mike
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
