July 2, 2008 at 7:57 am
I've got a table with a record for each day of the year. Each record has just a few fields. Does anyone have a simple way of creating a view to return each record that is the last day of the month? Should I just have a separate table with records for only the last day of each month and do a join to that?
Thanks,
David
July 2, 2008 at 2:28 pm
Try this:
declare @records table (date datetime, id int, amount int)
insert @records values('30 Jan 2008', 1, 2)
insert @records values('31 Jan 2008', 1, 3)
insert @records values('30 Jan 2008', 2, 2)
insert @records values('31 Jan 2008', 2, 4)
insert @records values('29 Jan 2008', 3, 4)
insert @records values('30 Jan 2008', 3, 5)
insert @records values('30 Mar 2008', 1, 9)
insert @records values('31 Mar 2008', 1, 8)
--This returns the latest row in a given month, even if it isn't on the last day
select r.date, r.id, r.amount
from
@records r
inner join (select max(date) max_date, id
from @records
group by id, datepart(year, date), datepart(month, date)) max_r
on r.date = max_r.max_date
and r.id = max_r.id
--This returns only rows on the last day
select r.date, r.id, r.amount
from
@records r
inner join (select max(date) max_date
from @records
group by datepart(year, date), datepart(month, date)) max_r
on r.date = max_r.max_date
July 2, 2008 at 8:34 pm
Doing the date calculations in the WHERE clause would get you the records that are on the last day of the month for which it falls in.
CREATE TABLE Table1 (col1 INT IDENTITY(1,1), col2 DATETIME)
SELECT col1, col2
FROM Table1
WHERE CONVERT(CHAR(8), col2,112) = CONVERT(CHAR(8),DATEADD(MM,DATEDIFF(MM,-1, col2),-1),112)
July 2, 2008 at 9:25 pm
Thanks Todd, that's pretty much what I was looking for but didn't really know how to best formulate the select.
David
July 6, 2008 at 9:39 am
Hi,
Are you looking for last day of the month in genral or last day of the month with in the table? By looking at your description, it looks like you are looking for last day of the month with in table.
Thanks -- Vj
July 6, 2008 at 10:00 pm
It's a whole lot easier than you think...
SELECT Date-1
FROM Calendar
WHERE Day(Date) = 1
--Jeff Moden
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply