• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 315
  • Last Modified:

SQL Select Statement with MAX

I need assistanace in combining this SQL Statement.
SQL A-

SELECT Transaction_DateTimestamp, Total_Hours, Date1, Employee_Name, Travel_Time from GrooveImports where Date1 >= '4/5/2012' and Date1 <= '4/8/2012' and Employee_Name = 'XYZ'

produces a list of employee dates and times with each record having a date timestamp of when the record was last modified. However, SQL A also retrieves ALL records between those dates, where I need to only return the MAX (DateTimestamp) of each day, so only the latest modified record is returned.

When I use the below statement for 1 day, this returns the latest record for that day.
So my question, is how can I return the latest record for each day when given a date range? (I am thinking it is a union, and/ or creating a temp table, but I am not the expert)


SELECT Transaction_DateTimestamp, Total_Hours, Date1, Employee_Name, Travel_Time from GrooveImports where Date1= '4/5/2012' AND  (GrooveImports.Transaction_DateTimeStamp =
                          (SELECT     MAX(Transaction_DateTimeStamp) AS Expr1
                            FROM          GrooveImports AS GrooveImports_1
                            WHERE      (Date1 = '4/4/2012')))
0
BKennedy2008
Asked:
BKennedy2008
  • 3
  • 2
1 Solution
 
wshark83Commented:
how about:

SELECT a.Transaction_DateTimestamp, a.Total_Hours, a.Date1, a.Employee_Name, a.Travel_Time
from GrooveImports a
join  (SELECT     date1, MAX(Transaction_DateTimeStamp) AS Expr1
         FROM          GrooveImports
         Group by date1) b
on a.date1 = b.date1
and a.Transaction_DateTimeStamp = b.expr1
0
 
BKennedy2008Author Commented:
Will try
0
 
sachinpatil10dCommented:
Try this

SELECT max(Transaction_DateTimestamp) DateTimestamp, Total_Hours, Date1, Employee_Name, Travel_Time from GrooveImports where Date1 >= '4/5/2012' and Date1 <= '4/8/2012' and Employee_Name = 'XYZ'
Group by  Total_Hours, Date1, Employee_Name, Travel_Time

Open in new window

0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
BKennedy2008Author Commented:
Sach- That produces nothing, I tried that one.

WSShark- yours almost does the trick, I just need to add somewhere the where:

where a.Date1 >= '4/5/2012' and a.Date1 <= '4/8/2012' and a.Employee_Name = 'xyz'
0
 
BKennedy2008Author Commented:
your the man WShark83, thanks for the quick response!


SELECT a.Transaction_DateTimestamp, a.Total_Hours, a.Date1, a.Employee_Name, a.Travel_Time
from GrooveImports a  
join  (SELECT     date1, MAX(Transaction_DateTimeStamp) AS Expr1
         FROM          GrooveImports
         Group by date1) b
on a.date1 = b.date1
and a.Transaction_DateTimeStamp = b.expr1
and a.employee_Name = 'xyz' and a.Date1 >='4/1/2012' and a.date1 <='4/6/2012'
0
 
wshark83Commented:
nps glad to be able to help.....
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now