Max Date Query

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
ourguruAsked:
Who is Participating?
 
BrandonGalderisiCommented:
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
 
chapmandewCommented:
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
 
BrandonGalderisiCommented:
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
BrandonGalderisiCommented:
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
 
ourguruAuthor Commented:
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
 
BrandonGalderisiCommented:
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
 
ourguruAuthor Commented:
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
 
BrandonGalderisiCommented:
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
 
ourguruAuthor Commented:
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
 
BrandonGalderisiCommented:
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
 
ourguruAuthor Commented:
Thank you so much, working great!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.