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

Select Max Time and Max Date from table where time and date are in separate columns

Thank you for looking at my question,

I have a table, Table X, of transactions orderer by date and time. I want to select the most recent transaction.

The transaction date (TransDate) is the date only eg 15/03/2012

The transaction time (TransTime) shows date and time in the format 01/01/1900 12:43:29

Currently I obtain the most recent transaction by first querying Table X for Max(TransDate)

I use a second query to get the latest time for the most recent date

SELECT Max(TransTime) FROM qryTableX_MaxDate INNER JOIN TableX ON qryTableX_MaxDate.MaxOfTRAN_DATE = TableX.TransDate
GROUP BY qryTableX_MaxDate.MaxOfTransDate

How do I do this in one query?
0
Crxfrd
Asked:
Crxfrd
  • 2
  • 2
  • 2
  • +1
1 Solution
 
armchair_scouseCommented:
Could you join the two fields together, e.g. take the TransDate and add the last 8 chars of TransTime, and then do a max on that? For example:

TransDate 15/03/2012
TransTime 01/01/1900 12:43:29

CombinedDate: CDate(TransDate & " " & Right(TransTime, 8))

and then do a Max on CombinedDate?
0
 
CluskittCommented:
I'm not sure I understand your question correctly, but maybe something like this:
SELECT Max(TransTime) FROM qryTableX_MaxDate WHERE qryTableX_MaxDate.MaxOfTRAN_DATE = (SELECT MAX(TransDate) FROM TableX)
0
 
CrxfrdAuthor Commented:
>Cluskitt,
Thanks for responding,

To try and clarify it, I want to get the most recent date and time from TableX where the date and time are in separate columns.

I tried a single query to select max(Date) and Max(Time) from TableX this gave me the maximum date value and the maximum time value but these aren't related to each other.

I need the max(Time) of max(Date) in TableX which I currently get using two queries and am asking can it be achieved using only one.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
CluskittCommented:
I see. What you want is:
SELECT MAX(Time) FROM TableX WHERE Date=(SELET MAX(Date) FROM TableX)
0
 
armchair_scouseCommented:
Crxfrd,

Did you try my idea?  I mocked up a table with separate date and time fields and tried it out to see if it would do the job, it seemed to do the trick, though I appreciate it is somewhat inelegant :o)

Update: Since posting this, I've just seen Mcluskitt's post, that looks a better option.  When you said do it in one query, I presumed you meant one select statement (i.e. no subqueries), hence my suggestion.
0
 
CrxfrdAuthor Commented:
THank you
0
 
Gustav BrockCIOCommented:
You could simply do:

SELECT TOP 1
  TransDate,
  TransTime
FROM
  TableX
ORDER BY
  TransDate DESC,
  TransTime DESC;

Or for a combined date/time:

SELECT TOP 1
  TransDate + DateAdd("d", -2, TransTime) As TransDateTime
FROM
  TableX
ORDER BY
  TransDate DESC,
  TransTime DESC;

/gustav
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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