January 18, 2009 at 2:07 pm
I am trying to get unique records for each item_id. Sample data is:
Order_no Item_id UOM Date_Created Commission_cost New_Sell_price2
1022220BS3399 CASE 2009-01-16 29.16000000040.094999
1022183BS3399 EACH 2009-01-15 2.6730000004.110000
1022143BS3399 EACH 2009-01-16 2.6730000003.820000
1022127BS3399 CASE 2009-01-14 29.16000000040.094999
1021697BS3399 EACH 2009-01-08 2.6730000004.620000
1021598BS3399 EACH 2009-01-07 2.6730000004.110000
1021514BS3399 CASE 2009-01-06 32.07600000044.280000
1021513BS3399 CASE 2009-01-06 32.07600000044.280000
1021512BS3399 EACH 2009-01-06 2.6730000003.690000
1021510BS3399 EACH 2009-01-06 2.6730000003.690000
I want the record with the highest Order_no for each Item_id for each UOM. In the sample data I only want the first 2 records. I created a view (script below) to join to my main query and I'm getting a zillion records! Can you help?
I have a script:
IF OBJECT_ID ('eek_custlist_special_view', 'V') IS NOT NULL
DROP VIEW eek_custlist_special_view ;
GO
create view eek_custlist_special_view
as
select distinct
l.order_no,
i.item_id,
l.unit_of_measure,
l.date_created,
l.commission_cost,
--was c_what_if
--New Sell Price2 This will be the displayed price all prices
CASE when ((100 * inventory_supplier.cost * 1.1) / (100.00000001 - case l.unit_price
WHEN 0 THEN
1 --avoid divide by 0 error
ELSE
(l.unit_price - l.commission_cost) / l.unit_price * 100
END ) ) / ( (i.purchase_pricing_unit_size / l.pricing_unit_size) ) > l.unit_price
then
((100 * inventory_supplier.cost * 1.1) / (100.00000001 -
CASE l.unit_price
WHEN 0 THEN
1 --avoid divide by 0 error
ELSE
(l.unit_price - l.commission_cost) / l.unit_price * 100
END ) ) / ( (i.purchase_pricing_unit_size / l.pricing_unit_size) )
else
l.unit_price
end New_Sell_Price2
FROM oe_line l
INNER JOIN oe_hdr h ON (h.order_no = l.order_no) AND (COALESCE(h.order_type, 0) <> 1706)
INNER JOIN inv_mast i ON (i.inv_mast_uid = l.inv_mast_uid)
INNER JOIN inv_loc il ON i.inv_mast_uid = il.inv_mast_uid and il.location_id = l.source_loc_id
inner join inventory_supplier on inventory_supplier.inv_mast_uid = i.inv_mast_uid
and inventory_supplier.supplier_id = il.primary_supplier_id
join oe_hdr_salesrep on oe_hdr_salesrep.order_number = h.order_no
and oe_hdr_salesrep.primary_salesrep = 'Y'
join contacts on contacts.id = oe_hdr_salesrep.salesrep_id
INNER JOIN address ON (address.id = h.customer_id)
where l.delete_flag <> 'y' and i.item_id like 'bs%'
and item_id = 'bs3399'
Any help greatly appreciated!
January 18, 2009 at 3:30 pm
Use something like this as the basis for the view.
SELECT *
FROM
(SELECT MAX(OrderNO) as MaxOrder, itemID, UOM FROM SomeTable GROUP BY ItemID, UOM) Qualifying
INNER JOIN SomeTable ON Qualifying.itemID = SomeTable.ItemID AND Qualifying.UOM = SomeTable.UOM AND Qualifying.MaxOrder = SomeTable.OrderNo
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply