View to get last day of month

Question

Post reply

View to get last day of month

RaiderX

Say Hey Kid

Points: 678
More actions
July 2, 2008 at 7:57 am

#70338

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

Viewing 6 posts - 1 through 5 (of 5 total)

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

JeffB Hall of Fame Points: 3541 More actions · Answer 1

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

Todd Engen SSCrazy Eights Points: 9869 More actions · Answer 2

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)

RaiderX Say Hey Kid Points: 678 More actions · Answer 3

Thanks Todd, that's pretty much what I was looking for but didn't really know how to best formulate the select.

David

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 4

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

http://dotnetvj.blogspot.com

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

It's a whole lot easier than you think...

SELECT Date-1

FROM Calendar

WHERE Day(Date) = 1

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)