February 9, 2012 at 12:43 pm
Hello everyone. With the code below I am trying to return ItemKey, shortdesc, ItemID and the last date (trandate) that there is an entry for.
Example: there could be an item with 20 entries for all of these, but I want to return the one entry with the lastest date.
Right now, I get multiple items, but all with the same date, today. It's returning the latest date and all of the items with a transaction on that date?
Thanks for any help.
Select a.ItemKey, a.trandate, b.shortdesc, c.ItemId
from timInvtTran a
join timItemDescription b on b.Itemkey = a.ItemKey
join timItem c on b.ItemKey = c.ItemKey
where a.trandate = (select MAX(trandate) from timInvtTran)
group by a.Itemkey, a.trandate, b.shortdesc, c.ItemId
order by a.ItemKey, a.trandate
February 9, 2012 at 1:27 pm
djustice 20821 (2/9/2012)
Hello everyone. With the code below I am trying to return ItemKey, shortdesc, ItemID and the last date (trandate) that there is an entry for.Example: there could be an item with 20 entries for all of these, but I want to return the one entry with the lastest date.
Right now, I get multiple items, but all with the same date, today. It's returning the latest date and all of the items with a transaction on that date?
Thanks for any help.
Select a.ItemKey, a.trandate, b.shortdesc, c.ItemId
from timInvtTran a
join timItemDescription b on b.Itemkey = a.ItemKey
join timItem c on b.ItemKey = c.ItemKey
where a.trandate = (select MAX(trandate) from timInvtTran)
group by a.Itemkey, a.trandate, b.shortdesc, c.ItemId
order by a.ItemKey, a.trandate
What it is returning is exactly what your query says to get.
I think what you want instead is this:
Select a.ItemKey, MAX(trandate) as trandate, b.shortdesc, c.ItemId
from timInvtTran a
join timItemDescription b on b.Itemkey = a.ItemKey
join timItem c on b.ItemKey = c.ItemKey
group by a.Itemkey, a.trandate, b.shortdesc, c.ItemId
order by a.ItemKey, a.trandate
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 9, 2012 at 1:40 pm
Thanks Sean!
This is definitely a step in the right direction. It now returns the data with all of the trandate's. So, each ItemKey is listed multiple times with different dates. Now I need to return only one line for each Item with the latest transaction date.
February 9, 2012 at 1:42 pm
Don't group by date.
group by a.Itemkey, a.trandate, b.shortdesc, c.ItemId
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 9, 2012 at 2:08 pm
Ahhh, that did it. Thanks so much!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply