HostWeb Forums » Microsoft Databases » microsoft.public.sqlserver.programming » Grouping items with different values in 1 field
Topic: Grouping items with different values in 1 field
Hello All,
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 general 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: (I left the aliases out)
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
Replies below ↓
Replies
Re: Grouping items with different values in 1 field
SQL Server 6.5? SQL Server 2008? Something in between?
Also what answer do you want if your sample data looks like (note the tie
also):
Part1 1 5.00 TR 3/21/2008 2:08:23 PM
Part1 1 6.00 TR 3/19/2008 2:08:23 PM
Part1 5 4.00 TR 2/17/2008 2:08:23 PM
Part2 3 7.00 TR 3/24/2008 2:08:23 PM
Part2 2 7.00 TR 3/24/2008 2:08:23 PM
Part2 2 6.00 TR 2/22/2008 2:08:23 PM
Part2 3 8.00 TR 1/01/2008 2:08:23 PM
"Noncentz" <Noncentz@discussions.microsoft.com> wrote in message
news:2A91E165-B98C-4A4F-A789-3DBF3C915FC6@microsoft.com...
> Hello All,
>
> 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 general 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: (I left the aliases out)
>
> 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
Re: Grouping items with different values in 1 field
If the date for each item is unique, you can add a condition in WHERE:
AND p21_item_tran_view.date_created =
(SELECT MAX(X.date_created)
FROM p21_item_tran_view AS X
WHERE X.item_id = p21_item_location_view.item_id)
You may need to add transaction type and location to the WHERE conditions,
not sure about you data and relations.
HTH,
Plamen Ratchev
http://www.SQLStudio.com
RE: Grouping items with different values in 1 field
If I am understanding your issue correctly, try this
select ITEM,QTY,COST,TYPE,max(Date_Created)
from [your table]
group by ITEM,QTY,COST,TYPE
"Noncentz" wrote:
> Hello All,
>
> 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 general 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: (I left the aliases out)
>
> 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
Re: Grouping items with different values in 1 field
This is better:
AND p21_item_tran_view.date_created =
(SELECT MAX(X.date_created)
FROM dbo.p21_item_location_view AS Y
INNER JOIN dbo.p21_item_tran_view AS X
ON Y.location_id = X.location_id
AND Y.inv_mast_uid = X.inv_mast_uid
WHERE Y.item_id = p21_item_location_view.item_id)
HTH,
Plamen Ratchev
http://www.SQLStudio.com