June 8, 2006 at 2:11 am
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!
June 8, 2006 at 2:22 am
-- 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"
June 8, 2006 at 2:36 am
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