Last Date not showing the last date

[Seguer]

Hi guys,

I'm using Last to pull the latest date and time (separate fields) in a Many table, linked to a One table.

1: Data_Tasks
Many: Data_Task_Update_History

What I'm finding is that it is finding the date/time of the last record ID in Data_Task_Update_History, rather than the Last Date.

I've discovered that the 'last record ID' is when the last "update" is on a date/time, when that ID is the largest.

Example:

TaskID  DateofUpdate
226     1/8/06
200     14/8/06
190     13/8/06

It will pull up the information for Task 226, instead of task 200.

Hope that made sense.

 

[mp9]

SELECT TaskID, Max(DateOfUpdate) AS MaxOfDateOfUpdate
FROM Data_Task_Update_History
GROUP BY TaskID;

http://www22.brinkster.com/accessory/

 

[Seguer]

I took a look at Max, but that won't work in getting me the time as well. Different dates will have different max times and such.

 

[mp9]

If DateOfUpdate is storing date and time then Max(DateOfUpdate) will return the latest time on the latest date.

If you have a time in a separate field we need to know about that - what is it called, where is it stored, etc - to be able to answer your query.

http://www22.brinkster.com/accessory/

 

[Golom]

If your dates and times are in different fields then

SELECT TaskID
     , (DateValue(DateOfUpdate) + TimeValue(TimeOfUpdate)) AS MaxDateTime

FROM Data_Task_Update_History

WHERE DateValue(DateOfUpdate) + TimeValue(TimeOfUpdate) = 
      (Select Max(DateValue(DateOfUpdate) + TimeValue(TimeOfUpdate)) 
       FROM Data_Task_Update_History)

 

[Seguer]

Thanks Golom, that works as I worded it. Unfortunately for me, I worded my problem badly How do I separate this out for each person?

In Data_Tasks I have an "Assigned To" field, and I need to get this max date/time for each person.

 

[Golom]

Like this ?

SELECT TaskID
     , [Assigned To]
     , (DateValue(DateOfUpdate) + TimeValue(TimeOfUpdate)) AS MaxDateTime

FROM Data_Task_Update_History H

WHERE DateValue(DateOfUpdate) + TimeValue(TimeOfUpdate) =
 
      (Select Max(DateValue(DateOfUpdate) + TimeValue(TimeOfUpdate)) 
       FROM Data_Task_Update_History X 
       WHERE X.[Assigned To] = H.[Assigned To])

 

[Seguer]

[Assigned To] is actually in a different table, "Data_Tasks" and I wasn't able to modify your SQL to incorporate that. Thanks for your efforts though!

The two tables can be joined by Data_Tasks.ID and Data_Task_Update_History.task_ID

 

[lespaul]

As shown in Thread701-1279071, example data and results go a LONG way in showing what you want done instead of trying to use words to explain it. You have some data, show us...you want some results, show the results based on the data.

It really does help.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[Seguer]

Sample data:

Data_Tasks

   [u]ID[/u]      [u]Assigned To[/u]   
  124         Bob
  125         Bob
  126         Bob
  127         Jim
  128        Sally
  129        Sally
  130         Jim
  131        Sally
  132         Bob
  133         Jim
  134        Jones
  135        Jones
  136        Jones

Data_Task_Update_History

   [u]updateID[/u]   [u]task_ID[/u]      [u]date_of_update[/u]   [u]time_of_update[/u]   
      1         124           21/05/06        9:01:34 AM
      2         125           21/05/06       10:11:21 AM
      3         126           21/05/06       11:30:02 AM
      4         127           21/05/06       12:01:49 PM
      5         128           21/05/06       12:03:10 PM
      6         129           21/05/06       12:05:34 PM
      7         130           21/05/06       12:14:00 PM
      8         131           21/05/06        1:25:34 PM
      9         132           21/05/06        1:37:53 PM
     10         133           21/05/06        1:59:23 PM
     11         134           21/05/06        2:07:30 PM
     12         135           21/05/06        3:11:39 PM
     13         136           22/05/06        9:07:50 AM

Sample Results

   [u]Assigned To[/u]   [u]date_of_update[/u]   [u]time_of_update[/u]   
      Bob           21/05/06        1:37:53 PM
      Jim           21/05/06        1:59:23 PM
     Sally          21/05/06        1:25:34 PM
     Jones          22/05/06        9:07:50 AM

 

[Golom]

Select T.[Assigned_To]
     , U.[Date_of_Update] 
     , U.[Time_of_Update]

From Data_Tasks T INNER JOIN Data_Task_Update_History U
     ON T.ID = U.Task_ID

Where DateValue(U.[Date_of_Update]) + TimeValue(U.[Time_of_Update]) = 
       (Select MAX(DateValue(X.[Date_of_Update]) + 
                   TimeValue(X.[Time_of_Update]))
        From Data_Task_Update_History X 
        Where X.Task_ID = T.ID)

Group By T.[Assigned_To]

 

[Seguer]

Thanks for the replay again Golom, I've been on holidays the past week which is why I haven't replied.

Using this SQL seems to get me almost accurate results, save that it returns more than one record for each person (Assigned To). This would seem to me that it is not correctly selecting the MAX() from the date range.

 

[Golom]

Are the Date and time values identical on the duplicate records? If so

Select DISTINCT
       T.[Assigned_To]
     , U.[Date_of_Update] 
     , U.[Time_of_Update]

From Data_Tasks T INNER JOIN Data_Task_Update_History U
     ON T.ID = U.Task_ID

Where DateValue(U.[Date_of_Update]) + TimeValue(U.[Time_of_Update]) = 
       (Select MAX(DateValue(X.[Date_of_Update]) + 
                   TimeValue(X.[Time_of_Update]))
        From Data_Task_Update_History X 
        Where X.Task_ID = T.ID)

 

[Seguer]

The extra records are not identical unfortunately, which is why I believe the problem lies in the MAX() aggregrate function.

It SHOULD be working, I just don't know why it isn't.

Here's some more sample data that your SQL returns:

Assigned To	Date_of_Update	Time_of_Update
David Yiu	8/22/2006	5:43:09 AM
David Yiu	9/19/2006	9:57:46 PM
David Yiu	10/3/2006	9:24:19 PM
Doug Scoular	8/9/2006	10:41:07 AM
Doug Scoular	9/19/2006	9:09:28 PM
Doug Scoular	9/19/2006	9:12:45 PM

(direct copy and paste)

 

[Seguer]

I solved it. In the end I used two queries;

The first pulls out the names and daterange (date + time)
The second runs on the first and pulls out the Max daterange for each person.

I think the problem was still in the ID relationship. Thanks for all your help Golom.