Solved

Max Date Query

Posted on 2008-10-10
12
423 Views
Last Modified: 2012-05-05
I have the following Query.  It is returning a record for "I" and for "R" Classes, I only want one record, the max date, regardless of the Class.

SELECT     TOP (100) PERCENT PART_ID, MAX(TRANSACTION_DATE) AS LAST_TRANS_DATE, CLASS
FROM         dbo.INVENTORY_TRANS
GROUP BY PART_ID, CLASS
HAVING      (NOT (PART_ID IS NULL))
ORDER BY PART_ID
0
Comment
Question by:ourguru
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 22690615
SELECT     PART_ID, MAX(TRANSACTION_DATE) AS LAST_TRANS_DATE
FROM         dbo.INVENTORY_TRANS
WHERE (NOT (PART_ID IS NULL))
GROUP BY PART_ID
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22690630
This will give you the entire record for the max item per part_id.

select t.* from dbo.inventory_trans t
join (select part_id, max_transaction_date=max(transaction_date) from dbo.inventory_items group by part_id) tm
on t.part_id = tm.part_id
and t.transaction_Date = tm.transaction_date
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22690632
you mean:
SELECT t.PART_ID, t.TRANSACTION_DATE , t.CLASS 
  FROM dbo.INVENTORY_TRANS t
  WHERE t.PART_ID IS NOT NULL
    AND t.TRANSACTION_DATE = ( SELECT MAX(i.TRANSACTION_DATE) FROM dbo.INVENTORY_TRANS i WHERE i.PART_ID = t.PART_ID ) 
 ORDER BY PART_ID

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22690638
Actually... this one will... forgot to change tm.transaction_Date to tm.max_transaction_Date

select t.* from dbo.inventory_trans t
join (select part_id, max_transaction_date=max(transaction_date) from dbo.inventory_items group by part_id) tm
on t.part_id = tm.part_id
and t.transaction_Date = tm.max_transaction_date
0
 

Author Comment

by:ourguru
ID: 22701656
BrandonGalderisi,

Thank you, that will work.  However, I need to only include records from t.inventory_trans where the CLASS='I' or CLASS='R'.  This needs to be the max date of that query, only one record should be returned.

Thanks,
Steve
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22702190
Here you go.  I think I mistyped inventory_items on the derived table too so I fixed it here.

select t.* from dbo.inventory_trans t
join
(select part_id, max_transaction_date=max(transaction_date)
from dbo. inventory_trans
where class in ('I','R')
  group by part_id) tm

on t.part_id = tm.part_id
and t.transaction_Date = tm.max_transaction_date
0
 

Author Comment

by:ourguru
ID: 22702263
BrandonGalderisi:

Excellent, one more thing...

Since my application stores only the date in a date/time field all dates are as of 12:00:00 AM, so I am getting duplicates.  Anyway to only get one?

Thanks,
Steve
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22702309
What is your primary key in the inventory trans table.  I can change it to only return one inventory_trans, but you need to say what one you want to return if there are multiple transactions for a particular day.  So if there are 5 transactions for a day, what of those 5 do you want?  The first inserted (do you have an identity), the last inserted, any one?
0
 

Author Comment

by:ourguru
ID: 22702336
BrandonGalderisi:

The Primary Key is "transaction_id".  I don't care which one get's returned for a particular day, I am just looking to see when the last transaction happend for each part_id of the class "i or r" types.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22702590
Try this:

select t.* from dbo.inventory_trans t
join
(select part_id, transaction_date, transaction_id, row_number() over (partition by part_id order by transaction_date desc, transaction_id desc) rn
from dbo. inventory_trans
where class in ('I','R')
  group by part_id) tm

on t.transaction_id = tm.transaction_id
and tm.rn=1

0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 22702593
whoops, forgot to remove the group by
select t.* from dbo.inventory_trans t
join 
(select part_id, transaction_date, transaction_id, row_number() over (partition by part_id order by transaction_date desc, transaction_id desc) rn
from dbo. inventory_trans 
where class in ('I','R')
) tm
 
on t.transaction_id = tm.transaction_id
and tm.rn=1

Open in new window

0
 

Author Closing Comment

by:ourguru
ID: 31505191
Thank you so much, working great!
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question