September 18, 2008 at 10:13 am
I am trying to get a record with 1 item number and the highest fifo layer number. Here is my code
SELECT item_id as "Item ID",
(fifo_layer_number) as "FIFO Layer Number",
(item_desc) as "Item Description",
(inv_loc.location_id) as "Location ID",
(default_purchasing_unit) as "Purchasing UOM",
(qty_on_hand/item_uom.unit_size) as "Purchase UOM Qty On Hand",
(qty_on_hand) as "SKU Qty On Hand",
--(fifo_layer_number) as "FIFO Layer Number",
--max(fifo_layer_number) as "FIFO Layer Number",
(fifo_layer_qty) as "FIFO Layer Qty",
(fifo_layers.cost) as "FIFO Layer Cost",
(inventory_supplier.cost) as "Supplier Cost"
FROM inv_mast
INNER JOIN inv_loc ON inv_mast.inv_mast_uid = inv_loc.inv_mast_uid
INNER JOIN item_uom ON inv_mast.inv_mast_uid = item_uom.inv_mast_uid and inv_mast.default_purchasing_unit = item_uom.unit_of_measure
INNER JOIN fifo_layers ON inv_mast.inv_mast_uid = fifo_layers.inv_mast_uid AND inv_loc.location_id = fifo_layers.location_id
INNER JOIN inventory_supplier ON inv_mast.inv_mast_uid = inventory_supplier.inv_mast_uid
INNER JOIN inventory_supplier_x_loc ON inventory_supplier.inventory_supplier_uid = inventory_supplier_x_loc.inventory_supplier_uid AND
inv_loc.location_id = inventory_supplier_x_loc.location_id AND primary_supplier = 'Y'
where qty_on_hand > 1 and inventory_supplier.cost > (fifo_layers.cost * 1.1)
order by item_id , fifo_layer_number desc
Can you help?
Thanks guys, you;re the best.
Emil
September 18, 2008 at 10:44 am
Try using MAX and Group By for this.
SELECT
item_id as "Item ID",
( fifo_layer_number ) as "FIFO Layer Number",
( item_desc ) as "Item Description",
( inv_loc.location_id ) as "Location ID",
( default_purchasing_unit ) as "Purchasing UOM",
( qty_on_hand / item_uom.unit_size ) as "Purchase UOM Qty On Hand",
( qty_on_hand ) as "SKU Qty On Hand",
max(fifo_layer_number) as "FIFO Layer Number",
( fifo_layer_qty ) as "FIFO Layer Qty",
( fifo_layers.cost ) as "FIFO Layer Cost",
( inventory_supplier.cost ) as "Supplier Cost"
FROM
inv_mast
INNER JOIN inv_loc ON inv_mast.inv_mast_uid = inv_loc.inv_mast_uid
INNER JOIN item_uom ON inv_mast.inv_mast_uid = item_uom.inv_mast_uid
and inv_mast.default_purchasing_unit = item_uom.unit_of_measure
INNER JOIN fifo_layers ON inv_mast.inv_mast_uid = fifo_layers.inv_mast_uid
AND inv_loc.location_id = fifo_layers.location_id
INNER JOIN inventory_supplier ON inv_mast.inv_mast_uid = inventory_supplier.inv_mast_uid
INNER JOIN inventory_supplier_x_loc ON inventory_supplier.inventory_supplier_uid = inventory_supplier_x_loc.inventory_supplier_uid
AND inv_loc.location_id = inventory_supplier_x_loc.location_id
AND primary_supplier = 'Y'
where
qty_on_hand > 1
and inventory_supplier.cost > ( fifo_layers.cost * 1.1 )
Group by
tem_id,
( fifo_layer_number ),
( item_desc ),
( inv_loc.location_id ),
( default_purchasing_unit ),
( qty_on_hand / item_uom.unit_size ),
( qty_on_hand ),
( fifo_layer_qty ),
( fifo_layers.cost ),
( inventory_supplier.cost )
order by
item_id,
fifo_layer_number desc
September 18, 2008 at 12:02 pm
Sorry, it didn't work. Still get:
item 1d fifo layer id
000497932
000496078
000491696
Any other suggestions?
September 18, 2008 at 12:19 pm
Please see this article on how to get better results from these forums: http://www.sqlservercentral.com/articles/Best+Practices/61537/
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply