Highest date on a table for every 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 2000 Forums

SQL Server Development (2000)

Highest date on a table for every month

Author

Topic

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-02-03 : 11:57:23

I have a table with dates.

For a single month of rows on the table, the highest (last) day may not be the last calendar day of the month (ie it may be the friday before etc.)

What query would return the highest date on the table for every month?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-02-03 : 11:59:46

Something like this:


select year(date), month(date), max(day(date))
from table
group by year(date), month(date)

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-03 : 12:30:55

or
select mth = convert(varchar(6),dte,112), lastdte = max(dte)
from tbl
group by convert(varchar(6),dte,112)
order by convert(varchar(6),dte,112)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-02-03 : 12:39:55

I came up with this, seems to work:
SELECT
B.DATE_

FROM
PRCIDX B

WHERE B.CODE = 249
AND MONTH(B.DATE_) <> MONTH(DATEADD(DD,10,B.DATE_))
AND B.DATE_ = (
SELECT
MAX(BB.DATE_)
FROM
PRCIDX BB
WHERE
B.CODE = BB.CODE
AND MONTH(B.DATE_) = MONTH(BB.DATE_)
AND YEAR(B.DATE_) = YEAR(BB.DATE_)
)

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-03 : 13:34:05

same as
SELECT
B.DATE_ = max(B.DATE_)
FROM
PRCIDX B
WHERE B.CODE = 249
group by MONTH(B.DATE_), YEAR(B.DATE_)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-02-06 : 10:09:28

Thanks nr, much more elegant (and way way faster!). : )
I'm using this:

SELECT
MAX(B.DATE_) AS LAST_OF_MONTH
FROM
PRCIDX B
WHERE
B.CODE = 249
GROUP BY
MONTH(B.DATE_)
, YEAR(B.DATE_)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources