Agregate Function in a WHERE Clause

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)

Agregate Function in a WHERE Clause

Author

Topic

CLages
Posting Yak Master

116 Posts

Posted - 2004-10-08 : 06:27:52

i have two tables PRODUCT, MOVIMENT
MOVIMENT TABLE HAS a lot of record of the same produts

I would like to update PRODUCT Table with the Latest record (mov_LastDate) for each Product in MOVIMENT Table

PRODUTCT

pro_code (key) pro_qty , pro_lastdade
123 ?? ??
567 ?? ??
..
..

MOVIMENT

mov_code(key) Docto mov_qty , pro_lastdade
123 1 12 12/10/04
123 13 67 09/09/04
123 12323 12 08/07/04
567 457 33 04/07/04
..
..

Upadate produto
set
pro_lastqty = mov_qty
pro_lastdate = mov_lastdate

i am really lost here

tks
Clages

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-08 : 06:42:29

will this do?


Update t1
set pro_lastqty = (select mov_qty from (select max(mov_qty) as mov_qty, pro_lastdate from MOVIMENT where t1.pro_code = mov_code group by pro_lastdate) t2),
    pro_lastdate = (select max(pro_lastdate) from MOVIMENT where t1.pro_code = mov_code)
from produto t1

Go with the flow & have fun! Else fight the flow

CLages
Posting Yak Master

116 Posts

Posted - 2004-10-08 : 11:15:22

tks, but i dont want the MAX(qty) i want the Qty ,LastDate from
the Lastdate record
if the LastDate Record has the qty = 678
i want this qty.

Tks
Clages

Update t1
set pro_lastqty = (select mov_qty from (select max(mov_qty) as mov_qty, pro_lastdate from MOVIMENT where t1.pro_code = mov_code group by pro_lastdate) t2),
pro_lastdate = (select max(pro_lastdate) from MOVIMENT where t1.pro_code = mov_code)
from produto t1

CLages
Posting Yak Master

116 Posts

Posted - 2004-10-08 : 12:13:38

CLages
Posting Yak Master

116 Posts

Posted - 2004-10-08 : 12:13:56

CLages
Posting Yak Master

116 Posts

Posted - 2004-10-08 : 12:14:56

this was the solution i did.
tks
Clages

Update produto2
set pro2_vlultcompra =
(
select top 1 mov_prunit from moviment
where mov_dtlcto <= getdate() and
mov_flag = 1 and
mov_deposito = pro2_deposito and
mov_codigo = pro2_codigo
order by mov_dtlcto DESC
),
pro2_dtultcompra =
(
select top 1 mov_dtlcto from moviment
where mov_dtlcto <= getdate() and
mov_flag = 1 and
mov_deposito = pro2_deposito and
mov_codigo = pro2_codigo
order by mov_dtlcto DESC
)
from moviment
where
mov_deposito = pro2_deposito and
mov_deposito = '100'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources