Ask Experts Questions for FREE Help !
Ask
    stelbin's Avatar
    stelbin Posts: 1, Reputation: 1
    New Member
     
    #1

    Aug 5, 2009, 03:48 AM
    Get max of date field in a table for a particular month
    Hi,
    I have data in a table as follows:

    Key date

    101 4/25/2006
    101 4/26/2006
    101 4/27/2006
    101 4/28/2006
    101 5/25/2006
    101 5/26/2006
    101 5/30/2006


    I need the max of date for each month. So my output expected is:
    101 4/28/2006
    101 5/30/2006

    How can I get this? Please help
    ROLCAM's Avatar
    ROLCAM Posts: 1,420, Reputation: 23
    Ultra Member
     
    #2

    Aug 5, 2009, 05:09 AM

    FOR:-

    101 4/25/2006
    101 4/26/2006
    101 4/27/2006
    101 4/28/2006
    101 5/25/2006
    101 5/26/2006
    101 5/30/2006

    TRY:-

    101 60425
    101 60426
    101 60427
    101 60428
    101 60525
    101 60526
    101 60530

    This gives you the opportunity of SORTING.
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #3

    Aug 5, 2009, 05:16 AM
    Quote Originally Posted by ROLCAM View Post
    FOR:-

    101 4/25/2006


    TRY:-

    101 60425


    This gives you the opportunity of SORTING.

    Transposing data is not the best answer. Do you actually know anything about SQL?
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #4

    Aug 5, 2009, 05:22 AM

    What you need to do is use a subquery. You need to create a query that adds a column to identify the month. You then need to use the TOP 1 qualifier and sort by date in descending order and group by month. This query is then used as a subquery in an IN clause to restrict your result set.

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Landlord Requiring 2 month Vacating Notice on 3 month (month to month) lease [ 21 Answers ]

I am in a situation and am in need of some advice. I am currently renting an apartment in Minneapolis. I am doing an internship which only requires me to live here for 3 Months. I signed a lease with a complex on a month to month basis. I planned to Move out at the end of April, so on march 1 I...

What date do I use on cheque if the previous month end hasn't been closed off yet [ 3 Answers ]

What date do I use on cheque ex: if the date is Oct 5th (what date do I use on this cheque if the Sept month end isn't closed off yet).

Wraping Text on a date field [ 1 Answers ]

I have a date cell formatted - mm/yy "Hours" - When I set the format to wrap text, then reduce the column width (and increase the cell hight to reveal multiple rows of text) I get ### error in the cell. Is there any way to wrap a numeric field? Thanks in advance, Jim


View more questions Search