November 29, 2005 at 5:10 pm
My data looks like this
SecurityIDDateClosePrice
102/11/20012.38
103/11/20012.36
104/11/20012.37
129/11/20012.15
130/11/20012.14
101/12/20012.14
102/12/20012.12
129/12/20012.35
130/12/20012.35
.
.
.
131/10/2005 2.20
130/11/20052.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
November 29, 2005 at 5:35 pm
why not join to an embedded query which gets max date for each month?
select key, yr, mo, value
from tablex x
JOIN (
select key, datepart( yy, datefield ) AS yr, datepart(mm, datefield) as Mo, MAX( datefield) as max_date...
group by key, datepart( yy, datefield ), datepart(mm, datefield)
) w ON w.key = x.key and w.mo = x.mo and w.yr = x.yr AND datefield = max_date
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply