"Rick Ratchford" <[email protected]>
schrieb im Newsbeitrag news:dbfb2606d0c1448b930064474496a...@dolphin...
> A while back, Igor gave me some help on pulling out mm/dd ranges (sets)
from
> my table.
>
> This is the code that does that.
>
> sSQL = "SELECT Date, Year, Month, Day, Open, High, Low, Close FROM ["
&
> gsTableName & "] " & _
> "WHERE ((Month - " & lngStartMth & ")*100 + (Day - " &
> lngStartDay & ") + 1300) % 1300 <= ((" & _
> lngEndMth - lngStartMth & ") * 100 + (" & lngEndDay -
> lngStartDay & ") + 1300) % 1300 " & _
> "ORDER BY Year, ((Month - " & lngStartMth & ")*100 + (Day - "
&
> lngStartDay & ") + 1300) % 1300"
>
> The only problem I have is that there are some stray rows that are not
> wanted.
Did you already tried the small function I've posted
into the VB-database-group (which was based on
Igors original suggestion)?
This also shows, how to work with a CommandObject -
(code is included again at the end of this post).
It enhances the *Where* clause (not the Order By),
to get rid of your "stray-rows" in the very first year
of a "year-crossing-set").
Function GetMonthDayRange(TableName As String, _
ByVal MStart&, ByVal DStart&, _
ByVal MEnd&, ByVal DEnd&, _
ByVal InclYearSort As Boolean) As cRecordset
Dim SQL As String
'a Cmd-SQL does *not* contain any direct "VarConcats", only @Placeholders
SQL = "SELECT Date,Month,Day,Open,High,Low,Close FROM @TblName" & _
" WHERE (((mon...@mstart)*100 + (d...@dstart)+ 1300)% 1300" & _
" <=(( @me...@mstart)*100 +(@de...@dstart)+ 1300)% 1300)" & _
" AND (Year > (Select Min(Year) From @TblName) " & _
" OR (Month*100+Day >= @mstart*1...@dstart)) "
'enhance the above SQL about the appropriate, different Order By Clauses
If InclYearSort Then
SQL = SQL & "ORDER BY Year, Month, Day"
Else
SQL = SQL & "ORDER BY ((mon...@mstart)*100+(d...@dstart)+ 1300)% 1300"
End If
With Cnn.CreateSelectCommand(SQL) 'create a Select-Command-Object
'now we replace the @PlaceHolders with the current Var-Content,
'which we've got passed in our Function-Parameters
.ReplColumnOrTableName !TblName, TableName
.SetInt32 !MStart, MStart
.SetInt32 !DStart, DStart
.SetInt32 !MEnd, MEnd
.SetInt32 !DEnd, DEnd
Set GetMonthDayRange = .Execute 'the Cmd.Execute returns a Recordset
End With
End Function
Olaf
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
