Help with this MonthEndDate Query

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

Other Forums

MS Access

Help with this MonthEndDate Query

Author

Topic

joblot1976
Starting Member

10 Posts

Posted - 2005-11-29 : 18:46:40

My data looks like this

SecurityID Date ClosePrice
1 02/11/2001 2.38
1 03/11/2001 2.36
1 04/11/2001 2.37
1 29/11/2001 2.15
1 30/11/2001 2.14
1 01/12/2001 2.14
1 02/12/2001 2.12
1 29/12/2001 2.35
1 30/12/2001 2.35
.
.
.
1 31/10/2005 2.20
1 30/11/2005 2.25

I want to write a query to find ClosePrice for each MonthEndDate between ranges of date StartDate - DEC-2001 to EndDate -

NOV-2005. In case ClosePrice is not available on the MonthEndDate say for instance in above data set 31/12/2001 then it

should report most recent ClosePrice available to 31/12/2001 which is 30/12/2001 in above.

I also want this query to report ClosePrice for each MonthEndDate + 1 month, + 5 month, + 6 month, -1 month, -2 month, -3

month… -12 month

Apparently I have written the following query which is called 15 times for each MonthEndDate from my program, which really

slows down things. Is there anyway I can write a single query which gives me results for various + & - months for each

MonthEndDate in one go. That way I be calling the query once for each MonthEndDate.

Dim TradeDate As Date = DateSerial(DateAdd(DateInterval.Month, p_intN, p_dteTradeDate).Year, _
DateAdd(DateInterval.Month, p_intN, p_dteTradeDate).Month, _
Date.DaysInMonth(DateAdd(DateInterval.Month, p_intN, p_dteTradeDate).Year, _
DateAdd(DateInterval.Month, p_intN, p_dteTradeDate).Month))

"SELECT TOP 1 A.CloseDiluted " & _
"FROM (tblSecurityDay AS A INNER JOIN tblSecurity AS B ON A.SecurityID = B.SecurityID) " & _
"INNER JOIN CompanyDetails AS C ON B.CompanyKey = C.CompanyKey AND B.SecurityCode = C.CompanyCode " & _
"WHERE C.CompanyCode = '" & p_strCompanyCode & "' And C.CompanyKey = " & p_intCompanyKey & " " & _
"AND [A.Date] <= #" & TradeDate.ToString("dd-MMM-yyyy") & "# ORDER BY [A.Date] DESC"

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources