Query for only Highest dates in a month over 1 yr.

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

Query for only Highest dates in a month over 1 yr.

Author

Topic

janis332
Starting Member

2 Posts

Posted - 2008-12-24 : 11:18:35

Hello all :) I've googled my questions many times already but there is no answer:(

I do have a database with stock quotes over 1 year, priced everyday so = 360 days. Now i need to have a query which always gets the highest date in a month over 1 year. The reason for this because sometimes day 31. is a sunday and there is no quote. so therefore i would need day 29.
so at the end i need 12 quotes (for each month) out of the 360 days.

Would be wonderful if there is a solution. Thank you very much. regards jan

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-12-24 : 11:27:44

[code]
create function HighestDayOfMonth(@m int, @y int)
returns int
as
BEGIN
declare @d varchar(10)
set @d = cast(@y as varchar(4))+'/'+cast(@m as varchar(2))+'/01'
return day(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,@d)+1, 0)))
END
[/code]

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-12-24 : 11:28:58

Get a list of highest day of 2008:

select number, dbo.highestdayofmonth(number,2008)
from master..spt_values
where type = 'P' and number between 1 and 12

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-12-24 : 11:36:03

This will give you the date of the last quote for each month.


select
	FirstDayOfMonth =
		dateadd(month,datediff(month,0,a.QuoteDate),0),
	LastQuoteDate =
		max(a.QuoteDate)
from
	MyTable a
group by
	-- First day of month
	dateadd(month,datediff(month,0,a.QuoteDate),0)
order by
	-- First day of month
	dateadd(month,datediff(month,0,a.QuoteDate),0)

CODO ERGO SUM

janis332
Starting Member

2 Posts

Posted - 2008-12-24 : 11:57:47

wow. thank you will try it as soon as im back in the office. thank you :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources