Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2009
    Posts
    19

    Unanswered: Selecting ultimo dates of a month

    Hi there,

    I have a lot of daily series, with observations on each day. Now I wish to create new tables, containing the same data, but only the last observation of each month. Is there some way to select this ultimo date within SQL, so as to select the 31st when this is the last date, the 30st when this is the last date etc?

    My code is quite simple, looks like this:

    -------------------

    Select *
    into ER_CAD_M
    from ER_CAD
    where ObsDateCAD = ???

    -------------------

    If someone can help me out on this, I would be really grateful!

    Best regards,

    Martin Falch

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    WHERE DAY(DATEADD(DAY,1,ObsDateCAD)) = 1

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2009
    Posts
    19
    Arh thanks alot, that was a really smart way to do it!

    - However, I'm quite dumb for not including this info, but unfortunately the dataset is slightly "flawed" in the sense that it doesn't always have the 1st as the first date of a new month, sometimes the first date is the second or the third - meaning that some months are left out via that method.

    Problem is that adding an "or" and then the same code = 2, will in alot of cases give me the primo of the month as well.

    Do you have an idea of how to fix this issue?

    Again, thanks a ton for the fast reply!

    - Martin

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by MartinFalch View Post
    However, I'm quite dumb for not including this info, but unfortunately the dataset is slightly "flawed" in the sense that it doesn't always have the 1st as the first date of a new month, sometimes the first date is the second or the third - meaning that some months are left out via that method.
    this makes no difference to my solution

    however, if you are saying that, for example, in a given month the observations only go up to the 23rd of the month, and you want that one as the last one, then my solution will not work

    which version of sql server are you on?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2009
    Posts
    19
    Hmm seems you are right, it's probably not due to that since it works in some cases, even through the above flaw is there. However, it seems to leave out specific months, 3-4 pr. year.

    Can it be because the last date of these months in the dataset don't correspond to the actual last date of these years? Meaning that if the code expects the 31st to be the last date and it's the 30st in the dataset, then the month is not included?

    Thanks again for your time!

    - Martin

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    An NZDF solution might be:
    Code:
    SELECT *
       FROM #ER_CAD AS a
       WHERE a.ObsDateCAD = (SELECT Max(z.ObsDateCAD)
          FROM #ER_CAD AS z
          WHERE  DateAdd(m, DateDiff(m, 0, a.ObsDateCAD), 0) <= z.OBSDateCAD
             AND z.ObsDateCAD < DateAdd(m, 1 + DateDiff(m, 0, a.ObsDateCAD), 0))
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Jul 2009
    Posts
    19
    While I'm not completely certain how that code works, it seems to do the job - I think all the right observations are selected now !

    Thanks alot for your time and help both of you!

    Best regards,

    Martin Falch

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Good enough!

    Note that the code I posted will perform better if there is an index that starts with ObsDateCAD (has that as the leftmost column). It also doesn't deal with "ties", so if you get two "last" entries with exactly the same ObsDateCAD value in your table, both will be returned in the result set.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Jul 2009
    Posts
    19
    Well those shouldn't be issues, ObsDateCAD is the leftmost column and there shouldn't be any cases of identical dates for this index Again, thanks a lot!

Posting Permissions

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