How to extract the last record of the month

  • I have a table with daily data in it. There is one record per day but no record on weekends and on public holidays. How can I extract the last record of each month out of this table. Many thanks for any hints!

  • -- Populate test data

    declare @t table (aDate datetime, otherdata varchar(50))

    insert @t

    select '2006-01-01', 'a' union all

    select '2006-01-15', 'b' union all

    select '2006-02-01', 'c' union all

    select '2006-02-02', 'd' union all

    select '2006-02-03', 'e'

    -- Do the work efficient

    select t.aDate, t.OtherData from @t t

    inner join (

      select max(aDate) theLastDate

      from @t

      group by year(aDate), month(aDate)

      ) z on z.thelastdate = t.aDate

    order by t.aDate

    OUTPUT

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

    2006-01-15

    2006-02-03

     

    -- or  Do the work not so efficient

    select * from @t

    where aDate IN (

      select max(aDate)

      from @t

      group by year(aDate), month(aDate)

      )

    order by aDate

    OUTPUT

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

    2006-01-15

    2006-02-03


    N 56°04'39.16"
    E 12°55'05.25"

  • Great, works perfect - many thanks!!

Viewing 3 posts - 1 through 2 (of 2 total)

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