Grouping Items with different Dates 1

[TechieTony]

Afternoon,

I am having trouble finishing this sql query for items in my database. I am trying to pull out some inventory so that my managers can see what they sold last.

When I run my query it shows the same part multiple times in my table. All of the fields in the query are the same for each individual item accept the date_created column which has different dates depending on when the item was sold. I would like to combine these results so that I only have 1 result per item with the most "current date" associated to it.

Here is a look at the data im working with:

ITEM QTY COST TYPE Date_Created
Part1 1 5.00 TR 3/21/2008 2:08:23 PM
Part1 1 5.00 TR 3/19/2008 2:08:23 PM
Part1 1 5.00 TR 2/17/2008 2:08:23 PM
Part2 3 7.00 TR 3/24/2008 2:08:23 PM
Part2 3 7.00 TR 2/22/2008 2:08:23 PM
Part2 3 7.00 TR 1/01/2008 2:08:23 PM
------------------------------------------------------------
What I really need this to look like is:

ITEM QTY COST TYPE Date_Created
Part1 1 5.00 TR 3/21/2008 2:08:23 PM
Part2 3 7.00 TR 3/24/2008 2:08:23 PM
------------------------------------------------------------
Here is my query:

SELECT DISTINCT
p21_item_location_view.qty_on_hand, p21_item_location_view.moving_average_cost, p21_item_location_view.qty_allocated, p21_item_tran_view.trans_type, p21_item_location_view.item_id, p21_item_location_view.item_desc, p21_item_tran_view.date_created, p21_item_location_view.supplier_name,p21_item_location_view.location_id, p21_item_location_view.primary_supplier

FROM
dbo.p21_item_location_view AS p21_item_location_view INNER JOIN dbo.p21_item_tran_view AS p21_item_tran_view ON p21_item_location_view.location_id = p21_item_tran_view.location_id AND p21_item_location_view.inv_mast_uid = p21_item_tran_view.inv_mast_uid INNER JOIN dbo.p21_view_inv_mast ON p21_item_tran_view.inv_mast_uid = dbo.p21_view_inv_mast.inv_mast_uid

WHERE
(p21_item_location_view.primary_supplier = 'Y') AND (p21_item_tran_view.trans_type = 'RECPT' OR p21_item_tran_view.trans_type = 'TR') AND (p21_item_location_view.location_id = 3000) AND (p21_item_location_view.qty_allocated > 0)
------------------------------------------------------

Any help is much appreciated

Antony

 

[RiverGuy]

I'm not sure which table and column refers to your ITEM, so I will give you a simple example which you can modify for your query. Basically, you need to get the latest Date_Created and use that to filter what comes from your base table. For example:

SELECT a.*
FROM YourTable a
INNER JOIN 
  (SELECT Item, MAX(Date_Created) AS MaxDate
   FROM YourTable b
   GROUP BY Item) c
ON a.Item = c.Item

 

[TechieTony]

River Guy...... Thx buddy I appreciate the info. Not nearly as hard as my brain made it out to be. SOLVED

Cheers!!!
Antony