i am having the following tables in a inventory control application
items transaction table
trans_id integer (/* primary key */)
some of the items (cosmetic, medicines f.e) have lot numbers. for this purpose another transaction file exist for lots
lots tranasction file
lot_id integer (/* primary key */)
trans_id integer (/* foreign key between two tables*/)
i want to create a select that will return the following columns
lot val should be equal to trans_val * lot_qty / trans_qty. (trans_qty is always <> 0).
i want the select statement to be written in such a way so the sum of l lot_val columns for a specific trans_id to be exacty equal to trans_val.
i am using sql server 2000