Results 1 to 3 of 3

Thread: Max Date

  1. #1
    Join Date
    Jan 2004
    Posts
    67

    Unanswered: Max Date

    How do i find out the max date of a particular month in a table.

    I have a records in the table like this

    DATE
    ------
    01 Jan 2004
    02 Jan 2004
    05 Jan 2004
    07 Jan 2004
    04 Feb 2004
    06 Feb 2004
    19 Feb 2004
    04 Mar 2004
    28 Mar 2004
    03 Apr 2004
    05 Apr 2004

    My output should be something like this
    OUTPUT
    ---------
    07 Jan 2004
    06 Feb 2004
    28 Mar 2004
    05 Apr 2004

    I want a SQL statement that can do this for me

    Thanks
    Shankar

  2. #2
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    Hehe, just answered you on SQLServerCentral

    select Max(datepart(dd,datecolumn)) as Day,datepart(mm,datecolumn) as month, datepart(yy,datecolumn) as Year
    from table
    group by datepart(mm,datecolumn),datepart(yy,datecolumn)

    HTH
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  3. #3
    Join Date
    Jan 2004
    Posts
    67

    Max Date

    thanks for your help,

    i got a kind off tricky requirement here.

    the query that i write has to return me 6 records always.

    The first record that will be displayed will be the initial record that is created. The last record in these 6 records will be the latest record that is created. so now my question is i want to retrieve the remaining 4 records.

    my conditions are like this.

    i want to display the maximum date of a particular month in the 4 records. now if this condition does not give me 4 records, then i need to
    pick up the current month's record also and make sure the count is equal to 4. What if i have only one record in the current month.
    then i need to go to current - 1 month and pick up that record.

    I need to keep doing this until i get the 4 records that i want.

    Let me give u the example for this.

    Lets take the example that i have given previously.

    DATE
    ------
    01 Jan 2004
    02 Jan 2004
    05 Jan 2004
    07 Jan 2004
    04 Feb 2004
    06 Feb 2004
    19 Feb 2004
    04 Mar 2004
    28 Mar 2004
    03 Apr 2004
    05 Apr 2004

    My output should be something like this

    01 Jan 2004 --> First Record that is created
    07 Jan 2004 --> Max of Jan
    06 Feb 2004 --> Max of Feb
    28 Mar 2004 --> Max of March
    03 Apr 2004 --> I choose this record becoz the count for max months
    is not equal to 4 and hence to make it 4 , i add this
    05 Apr 2004 --> The most recent record

    Hope this helps in understanding the requirement

    Thanks
    Shankar

Posting Permissions

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