Solved

Max Date Query

Posted on 2008-10-10
12
418 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
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 142

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
 
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

895 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now