Select Last Record of Each Month

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Select Last Record of Each Month

Author

Topic

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2014-08-05 : 05:34:55

Hi, I am wondering any how possible to retrieve the last day record of each month in my table.

Date Name
2014-07-31 A
2014-07-30 B
2014-07-29 C
2014-06-30 D
2014-06-29 E
2014-06-28 F
2014-05-31 G
2014-05-30 H

My result should only retrieve the records for the last day of each month.

2014-07-31 A
2014-06-30 D
2014-05-31 G

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-08-05 : 05:58:14

CREATE TABLE #LastDayOfMonth
(ID int identity(1,1), SomeDate datetime)

INSERT INTO #LastDayOfMonth
VALUES('7/1/2014'),('6/12/2014'),('7/16/2014'),('7/31/2014'),('7/1/2014'),('5/31/2014'),('4/30/2014'),('5/15/2014')

SELECT * FROM #LastDayOfMonth
WHERE DAY(DATEADD(dd,1,SomeDate)) = 1

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-08-05 : 13:00:41

[code]

SELECT Date, Name
FROM schemaname.tablename
WHERE
MONTH(Date) <> MONTH(Date + 1)

[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources