Re: SQL Logic Question

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance



"Webbiz" <nospam@xxxxxxxxxxxxxxxx> schrieb im Newsbeitrag
news:gbms459lp44b9d5fji2uecghhu3ba543q6@xxxxxxxxxx
I'm trying to formulate a SQL statement that will fulfill what I need
to accomplish.

Here is what I have:

Table: dtaTable
Recordset: dtaRS

Fields:
Date String
Color String
Offset Long

I have a GRID with 40 columns, 0 to 39.

Now, I have a COLOR and OFFSET that is used as the reference.

Starting from the end of the dataset (working backwards), I need to
find the last 15 records where COLOR and OFFSET match my reference.

To do this, I have the following SQL Statement figured out:

"SELECT Date, Color, Offset FROM dtaTable " & _
"WHERE Color = '" & sColor & "' AND Offset = " & lngOffset1 & _
" ORDER BY Date DESC LIMIT 15"

This successfully creates a recordset that provides the Date, Color
and Offset that matches my sColor and lngOffset variable values,
starting from the end of the dataset backwards, limited to only 15
records max.

Now here comes the tricky part.

The 15 records returned contain the STARTING DATES for each set of 40
records I need to pull out of dtaTable.

Example: Suppose the resulting 15 records of Dates returned are the
following:

2009-03-03
2008-11-05
2008-07-10
2008-03-14
...

As you can see, they are returned from most recent and going backwards
in time from there.

These dates are the start of each 40 record sections I need to grab
from the main dtaTable (or dtaRS).

Then just reduce your above query to what you really want
(the starting dates) in a first step:

Sub CalculateColumnSums()
Dim RsDates as cRecordset, SQL as String, i As Long
SQL = "SELECT Date FROM dtaTable WHERE Color = '" & _
sColor & "' AND Offset = " & lngOffset1 & _
" ORDER BY Date DESC LIMIT 15"
Set RsDates = Cnn.OpenRecordset(SQL)

'now perform a simple loop (since these are only 15 records):
ReDim SumArr(0 to 39) '<-- adjust to the correct PointType here
For i = 0 to RsDates .RecordCount - 1
AddColumns(RsDates.ValueMatrix(i, 0), SumArr())
Next i

'SumArr now contains what you wanted to achieve IMO
End Sub

Private Sub AddColumns(sDate As String, SumArr())
Dim RsCols as cRecordset, i As Long
SQL = "SELECT Point FROM dtaTable WHERE Date >= '" & _
sDate & "' ORDER BY Date LIMIT 40"
Set RsCols = Cnn.OpenRecordset(SQL)

For i = 0 to RsCols.RecordCount - 1
SumArr(i) = SumArr(i) + RsCols.ValueMatrix(i, 0)
Next i
End Sub

You can speedup the performance of both used query-types
by a great amount (if needed at all), if you define an index on
the Date-Column.

Olaf


.

Relevant Pages

  • Re: Generic programming in C
    ... int main ... char buf, ... proper string and the assignment might even be off the end of the array. ... strcpy (offset, (offset + findlen)); ...
    (comp.lang.c)
  • Microsoft Word Autocorrects
    ... read MS Word autocorrects file. ... private static final String AMERICAN_AUTOCORRECTS = ... int offset = 0; ... // bypass possibly multiple spacer 0s. ...
    (comp.lang.java.programmer)
  • Re: assert "foo"[3] != "foo"[3,1]...revisited
    ... string, ... cases, if an offset is negative, it is counted from the end of str. ... However, as element referencing is currently implemented, the ...
    (comp.lang.ruby)
  • Re: works on console but not when writing to a string or file
    ... I would think that what's strange is that your code doesn't work, not that some other code does. ... System.Console.Write(Encoding.ASCII.GetString(data, offset, ... in the outputdata string the last packets gets chopped off. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: How do I find strings with wildcards?
    ... offset in a string. ... set match = match + 1 ...
    (comp.lang.pascal.delphi.misc)