Last Day - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Last Day

Author

Topic

rki1966
Starting Member

2 Posts

Posted - 2011-01-21 : 14:25:49

I am trying to query on the dates in the table that are the last day of each month. The last day of the month may not be in the table. Example 07/30/2010 is the last day in the table for July 2010, but 07/31/2010 is the last day of the month.
My query should give me 07/30/2010 data since it is the last day of the month in the table.

I just need a where clause that will only give me the data for the last day of each in the table.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-21 : 14:29:05

SELECT DATEADD(MONTH, DATEDIFF(MONTH, -1, Col1), -1)
FROM Table1

N 56°04'39.26"
E 12°55'05.63"

rki1966
Starting Member

2 Posts

Posted - 2011-01-21 : 14:35:54

That does not work because that gives me actual last day of the month. I need the last day of the the month on the table.

Select HistDate, Region
From Currency where HistDate = ?????

Results
HistDate Region
06/30/2010 USA
07/30/2010 USA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-21 : 15:27:13

SELECT HistDate, MAX(HistDate) OVER (PARTITION BY DATEDIFF(MONTH, 0, HistDate))
FROM Table1

SELECT HistDate, MAX(HistDate) OVER (PARTITION BY Region, DATEDIFF(MONTH, 0, HistDate))
FROM Table1

Use any of the two depending on your need.

N 56°04'39.26"
E 12°55'05.63"

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-01-21 : 17:40:10

I think this might be simpler using row_number() instead:

;WITH hist (Region, Period, HistDate, rn)
AS (
SELECT Region
, convert(char(6), HistDate, 112) As Period
, HistDate
, row_number() over(partition by region, convert(char(6), HistDate, 112) order by HistDate desc) As rn
FROM Currency
WHERE {other criteria here}
)
SELECT *
FROM hist
WHERE rn = 1;

This is untested, but it should get you close. I included the period in case you are crossing year boundaries.

Jeff

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2011-01-21 : 23:27:40

Select HistDate, Region
From Currency where HistDate in
(select max(HistDate) from Currency group by year(HistDate), month(HistDate))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources