SQL Logic Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Webbiz - 03 Jul 2009 21:16 GMT

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).

In other words, I need 40 records starting with the 2009-03-03 record,
as I'll be getting the POINTS values within those records to place in
the Grid in Row 1, starting at 0 to 39.

Then I need 40 records starting with 2008-11-05 record, as these will
be placed in row 2 of my Grid.

40 records starting from 2008-07-10 record, as these will be placed in
row 3 of my Grid.

And so forth.

When done, I'll then be adding up the POINTS of each column on the
Grid and placing the total at the bottom.

========================

Perhaps to make this clearer, let's say that you have a GRID with 40
columns, and these columns are labeled with calendar dates starting
with today's date (weekends are removed).

So the header row, where you have the current 40 dates listed, is our
Reference time period.

Staring from our Reference time period as represented by the header
dates, we want to go backwards in time to find the most recent 40 date
(records) to use to fill in the next row in the Grid. Then we go back
some more and find the next 40 date (record) segment to extract and
place in the next row of our Grid, and so-forth.

Well, those 15 records I mentioned earlier contain the first date of
those most recent 40 records that I wish to extract from the dataset
and place in the Grid.

Hope that makes it clearer.

====================

Well, I've solved the problem of finding the 15 records that start
each 40 record section of data I need. I just don't know the best way
to grab those 15 sets of 40 records most efficiently.

Hope my long winded post didn't make this more complicated than I
intended. :-0

Thanks.

Webbiz

Reply to this Message

Schmidt - 03 Jul 2009 23:37 GMT

> I'm trying to formulate a SQL statement that will fulfill what I need
> to accomplish.
[quoted text clipped - 46 lines]
> 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

Reply to this Message

Webbiz - 04 Jul 2009 00:08 GMT

Hello Olaf. :-)

>Then just reduce your above query to what you really want
>(the starting dates) in a first step:
[quoted text clipped - 14 lines]
> 'SumArr now contains what you wanted to achieve IMO
>End Sub

Thanks, but this isn't what I'm trying to do. The only need I have of
the 15 returned records is to get the dates themselves, as each date
represents the start of each 40 record datasets.

By using these 15 dates, I need to search the data, find the date,
then read in 40 records (including the record of that date). I need to
do this for all 15 dates.

Each of these 15 datasets of 40 records will be plotted on a Grid,
already setup with columns 0 to 39. Therefore, there will be 15 rows
that will be occupied with these 15 sets of 40 records.

I need this for DISPLAY purposes. Once completed, I can then simply
add up these columns on a new row below these 15, just a like a
spreadsheet.

So the question might come down to this: Once I have the 15 dates,
which I've figured out how to get, should I run a new query to get the
40 records that start at each of those dates one at a time? Or is it
possible to return a recordset that would grab all 15 sets of 40 recs
at once?

If possible to do all at once, I would suppose that it would then be a
matter of just looping through 1 recordset once only, skipping to the
next row of the Grid after each read in of 40 records.

What do you think?

Thanks!

Webbiz

>Private Sub AddColumns(sDate As String, SumArr())
>Dim RsCols as cRecordset, i As Long
[quoted text clipped - 12 lines]
>
>Olaf

Reply to this Message

Webbiz - 04 Jul 2009 15:47 GMT

>Hello Olaf. :-)
>
[quoted text clipped - 65 lines]
>>
>>Olaf

I've decided to go about this in a different way.

From a For..Loop 0 to 39, I'll grab a single columns worth of data to
fill the grid row 1 to 15. This appears to work just fine.

:-)

Webbiz

Reply to this Message

Schmidt - 04 Jul 2009 22:27 GMT

> I've decided to go about this in a different way.
>
> From a For..Loop 0 to 39, I'll grab a single columns worth of
> data to fill the grid row 1 to 15. This appears to work just fine.

Ok - that would also be an approach, to "transpose" all that -
probably does not make that much difference - if you do it
15 times, delivering 40 "Column"-Values - or if you're doing
it 40 times, delivering 15 "Row" values - maybe the overhead
in my posted routine is a bit smaller, since it is "asking" the
DB-engine only 15 times about a properly filled up (with
40 entries) recordset.

With your approach you will request the engine 40 times,
to deliver a 15-entry-recordset - but in that range it is really
not that important, which way you go at it - you'd probably
see a difference, if we'd talk about 1500 on 4000 (or
the other way round 4000 on 1500) - but with such a small
"result-matrix" it is really not that important, which way you
prefer.

Olaf

Reply to this Message

Webbiz - 05 Jul 2009 04:47 GMT

>> I've decided to go about this in a different way.
>>
[quoted text clipped - 17 lines]
>
>Olaf

It's hard to get around the speed thing when you have to fill up those
grid rows. If all I needed to do was to add up the values, than it
would be a completely different thing altogether.

:-)

Reply to this Message

Schmidt - 04 Jul 2009 22:20 GMT

> >Then just reduce your above query to what you really want
> >(the starting dates) in a first step:
[quoted text clipped - 16 lines]
>
> Thanks, but this isn't what I'm trying to do.

Ok, let's see...;-)

> The only need I have of the 15 returned records is to
> get the dates themselves, as each date
> represents the start of each 40 record datasets.

Yes, that's what is done in the routine above.

> By using these 15 dates, I need to search the data, find the date,
> then read in 40 records (including the record of that date).
> I need to do this for all 15 dates.

And that's exactly what the routine above ensures - it
is calling the soub-routine below 15 times then.

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

In each of these 15 rounds the above routine delivers
your "40 following entries", using your starting-
Date (passed as a parameter) - and including the
data for this starting date as the first of the 40 entries
too.

> Each of these 15 datasets of 40 records will be plotted on
> a Grid, ...

In the routine above I just cumulated the 15*40 values
in an appropriate ColumnSum-Array - but enhancing
the very same loop (which currently only ensures the up-
suming), to additionally fill in a grid of 15x40-layout -
or to render these points-data "live" is no problem IMO.

> I need this for DISPLAY purposes.

No problem, as said.

> Once completed, I can then simply add up these columns
> on a new row below these 15, just a like a spreadsheet.

It's up to you, what you do within the loop then - and into
what array you fill in your 15x40 entries we achieve with the
construct above. In my example I've only used the Sum-
"vector" for the final results of your (0 to 39) column-sums -
but you can of course also put your values not (only) into an
vector-like-array, but into a properly dimensioned one
(0 to 14, 0 to 39) - and do your upsuming later on on that
larger array (rendering your values beforehand in your GUI
from that larger array).

> If possible to do all at once, I would suppose that it would
> then be a matter of just looping through 1 recordset once
> only, skipping to the next row of the Grid after each read
> in of 40 records.

It would also be possible, to define a really large SQL-String
for that - and deliver all your data at once - but the code to
create that large SQL-String would need more lines of code
than the solution that works with these separated calls (as shown).

Olaf

Reply to this Message

Webbiz - 05 Jul 2009 05:03 GMT

>> >Then just reduce your above query to what you really want
>> >(the starting dates) in a first step:
[quoted text clipped - 14 lines]
>> > 'SumArr now contains what you wanted to achieve IMO
>> >End Sub

What threw me off was the 'column sums" and "SumArr" wording. Since
the task is mostly to fill a 15 x 40 grid more than it is to 'sum' up
anything, I assumed it wasn't geared to what I wanted to do.

>Private Sub AddColumns(sDate As String, SumArr())
>Dim RsCols as cRecordset, i As Long
[quoted text clipped - 6 lines]
> Next i
>End Sub

>> If possible to do all at once, I would suppose that it would
>> then be a matter of just looping through 1 recordset once
[quoted text clipped - 6 lines]
>
>Olaf

You're right. I seemed to miss the rest of the code somehow. Good
thing it wasn't a snake it would have bit my face. :-0

The above is a better fit for filling up the 15 x 40. However, I came
to find out that some of my 40 date sets are not 'apples to apples'
for comparison, and so doing the one-column at a time turned out to be
what I needed as it will skip over a set if there isn't a match in one
or more of the sets. Anyway, don't fret the details as it gets into my
algorithm...

Thanks a bunch. I'm learning. :-)

Webbiz

Reply to this Message

VB Forum / Database Access / July 2009

SQL Logic Question