Max and Min Date problems

  • Good afternooon all,

    IM having problems with what seems to be a simple query but is proving to be a pain....

    First off my datatable:

    Job RefBooked Date

    A10/10/2012

    A11/10/2012

    B10/10/2012

    C12/10/2012

    C13/10/2012

    C14/10/2012

    So my query i have written

    Select [Job Ref], MAX([Booked Date]),MIN([Booked Date])

    what i want to return is:

    Job RefMax DateMin Date

    A10/10/201211/10/2012

    B10/10/201210/10/2012

    C12/10/201214/10/2012

    But am at a loss as to why i am not getting the max and min but ony the min value....

    any ideas how i could solve this

    Thanks

  • Are you grouping by job ref?

  • I only found a problem with your query but it should throw an error and not incorrect results. The problem is the query is not complete.

    Could you post DDL?

    As a suggestion, don't use spaces for names of objects or columns in your database.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Are you getting any error messages? If all you are running is Select [Job Ref], MAX([Booked Date]),MIN([Booked Date])

    you should be getting an error. If so id like to point you to http://www.w3schools.com/sql/sql_select.asp your Query syntax is off.


    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

  • I don't know if I'm fully understanding, but are you looking for something like this? (Forgive me - I'm not totally sure on your date format so I switched it around).

    CREATE TABLE YourTable

    (

    JobRefVARCHAR(25) NOT NULL,

    BookedDate Date NOT NULL

    )

    ;

    INSERT INTO YourTable

    (

    JobRef,

    BookedDate

    )

    Values

    ('A', '2012-10-10'),

    ('A', '2012-10-11'),

    ('B', '2012-10-10'),

    ('C', '2012-10-12'),

    ('C', '2012-10-13'),

    ('C', '2012-10-14')

    SELECT JobRef, MAX(BookedDate) AS Max, MIN(BookedDate) AS Min

    FROM YourTable

    GROUP BY JobRef

    ;

    --clean up

    DROP TABLE YourTable

  • it seems you have DD/MM/YYYY date formate

    whats you column data type.

    Question:but why you want maximum date in Min(Booked Date)

    Like

    Job Ref Max Date Min Date

    C 12/10/2012 14/10/2012

    now 14/10/2012 is maximum then 12/10/2012

    can you give more focus on this.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply