Solved

Max Date Query

Posted on 2008-10-10
12
419 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

773 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