Select Max(Date) and then Max(Time) for that date

[ChrisL]

Can someone please help me with this as I'm struggling to get my head around it.

I've got a rather large query here with a number of joins, the one I'm struggling with needs to link to a file which has separate date and time fields - I need to retrieve the latest record in this file for the other matching keys..

So for example, my file had a number of fields - I'll simplify a bit.

[ClientFile]
FirstName
LastName
Address

[PaymentFile]
FirstName
LastName
PayAmount
PayDate
PayTime

I want to link from ClientFile to PaymentFile so I would try something along the line of..

SELECT A.FirstName, A.LastName, B.PayDate, B.PayTime, B.PayAmount
FROM   ClientFile A Join PaymentFile B on A.LastName = B.LastName and
                                        A.FirstName = B.FirstName

But if my payment file had a number of entries for a client that would return all of those entries, I only want to return the most recent. I know I could use MAX(PayDate) but I'm struggling to get my head around how to get the may date and time to link my two files together.

Can anyone help?

** And no I can't just create a Timestamp field holding both values together - that would be the easy option but I'm stuck with the data I have got! **

[kitvb1]

Re: Select Max(Date) and then Max(Time) for that date

Should work... but is untested with your field names.

With LastPayment as
(
SELECT A.FirstName, A.LastName, max(B.PayDate) as PayDate , max(B.PayTime) as PayTime
FROM   ClientFile A 
Join PaymentFile B on  A.LastName = B.LastName 
                  and A.FirstName = B.FirstName
group by A.FirstName, A.LastName
)
select c.FirstName, c.LastName, c.PayDate, c.PayTime, d.PayAmount 
from LastPayment c 
Join PaymentFile d on  c.LastName = d.LastName 
                  and c.FirstName = d.FirstName

[ChrisL]

Re: Select Max(Date) and then Max(Time) for that date

Thanks for this kit, my SQL isn't great but wouldn't this give me the max of each value?

Ie..

Row 1: 10/09/04 11:00:00
Row 2: 10/09/04 12:35:21
Row 3: 10/09/03 15:00:00

Wouldn't it return 10/09/04 as the max date and 15:00:00 as the max time?

I actually want to retrieve the values from the 10/09/04 12:35:21 line

[kitvb1]

Re: Select Max(Date) and then Max(Time) for that date

Don't worry... my SQL isn't great either. --> (blind leading the blind )
Test it using navigator...
works a treat on the 2 files I tested with ... but mine didn't have an amount (that's why I had to use a CTE).
still worked fine after adding it in tho.

[K2r400]

Re: Select Max(Date) and then Max(Time) for that date

Based on kitvb1's solution :

With LastPayment as 
(
SELECT A.FirstName, A.LastName, Max(TIMESTAMP(Paydate, Paytime)) as PayTimeStamp
FROM   ClientFile A 
Join PaymentFile B on  A.LastName = B.LastName 
                   and A.FirstName = B.FirstName
group by A.FirstName, A.LastName
)
select c.FirstName, c.LastName, d.PayDate, d.PayTime, d.PayAmount 
from LastPayment c 
Join PaymentFile d on  c.LastName 		= d.LastName 
                  and  c.FirstName 		= d.FirstName
		  and  Date(c.PayTimeStamp) 	= d.PayDate
		  and  Time(c.PayTimeStamp) 	= d.PayTime

[B.Hauser]

Re: Select Max(Date) and then Max(Time) for that date

You may try something like this:

with x as (Select FirstName, LastName, PayAmount, 
                  Max(Timestamp(PayDate, PayTime)) MaxTime
              from FileB
              Group by FirstName, LastName, PayAmount)
Select a.FirstName, a.LastName, Date(MaxTime), Time(MaxTime), PayAmount
From ClientFile a join x on     a.FirstName = x.FirstName
                            and a.LastName  = x.LastName

Birgitta

[ChrisL]

Re: Select Max(Date) and then Max(Time) for that date

Thanks everyone, I'll try them out and let you know which I go with