Solved

Max Date Query

Posted on 2008-10-10
12
422 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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Results to Excel File 18 89
Sql case statement to calculate totals 5 55
UPDATE JOIN multiple tables 5 35
Need multiple Group By's 8 55
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

739 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