Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    Unanswered: Finding Earliest Date

    I'm trying to be able to find the earliest date in a list from my query, I've tried using the min, max and first & last within the query builder....

    This below is a sample of the query showing how people can log on to the system more than once in the day. How can I show the list where there is only the first entry into the system on a given day?

    id engineer_displayname logged_on
    14 Steve Tate 05/06/2008 07:00:30
    15 David Ling 05/06/2008 07:28:10
    20 Mark Irvine 05/06/2008 15:01:32
    30 Phil Calverly 05/06/2008 07:48:06
    45 Keith Evelyn 05/06/2008 08:28:45
    45 Keith Evelyn 05/06/2008 12:13:01
    45 Keith Evelyn 05/06/2008 12:17:10
    45 Keith Evelyn 05/06/2008 12:17:44
    51 Jerry Kuryliw 05/06/2008 06:16:44
    51 Jerry Kuryliw 05/06/2008 15:56:40
    54 Colin Eades 05/06/2008 17:47:09
    56 Simon Michael Day 05/06/2008 06:44:56
    56 Simon Michael Day 05/06/2008 10:35:35
    58 Mick Wright 05/06/2008 07:23:40
    63 Steve Whiteley 05/06/2008 18:05:08

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT Min(logged_on)
    FROM   mytable
    WHERE  logged_on > [given date]
    ??
    Code:
    SELECT a.id
         , a.engineer_display_name
         , b.early_log_on
    FROM   mytable a
     INNER
      JOIN (
            SELECT Min(logged_on) As [early_log_on]
            FROM   mytable
            WHERE  logged_on > [given date]
           ) As [b]
        ON a.logged_on = b.early_log_on
    ???
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Thanks, I tried this but it only brings back 1 record and that was the person who logged on the earliest, I am looking to find in earliest log on time for each person. For example person 45 has logged on 4 times during the day I want to only show this time for him
    45 Keith Evelyn 05/06/2008 08:28:45 and so on for the other guys

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT id 
         , engineer_displayname 
         , MIN(logged_on) AS first_logged_on
      FROM daTable
    GROUP
        BY id 
         , engineer_displayname
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Thanks again, I tried it and the same results appear.

    Attached is copy of the original query, which has 51rows.

    EngineerID 45 has 4 entries from 8am to 12 noon, I want to be able to see that this engineer has logged on at 8am on that day, and the same goes for anyone else that has more than 1 entry.
    Attached Files Attached Files
    • File Type: txt a.txt (1.2 KB, 43 views)

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Actually, based on your initial question
    Quote Originally Posted by JezLisle
    How can I show the list where there is only the first entry into the system on a given day?
    My solution should be correct

    After changing your mind about what the question is
    Quote Originally Posted by JezLisle
    I am looking to find in earliest log on time for each person
    Then Rudy's solution is correct.

    You then change your requirement again
    Quote Originally Posted by JezLisle
    I want to be able to see that this engineer has logged on at 8am on that day, and the same goes for anyone else that has more than 1 entry.
    You want a query that is grouped by person, with a HAVING clause based on the Count() of the aggregation.
    George
    Home | Blog

  7. #7
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    I appologise, maybe I didnt make myself clear enough....

    how do you mean using the HAVING clause, I've not used that in the past so unsure about it

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It is like the where clause but it is applied to the results of aggregate functions (COUNT(), SUM(), MAX() etc).

    http://www.w3schools.com/sql/sql_groupby.asp

  9. #9
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Thanks, for the help I've sussed it

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Care to share your solution with others?
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •