Seeking help with a join? union? query...

[HeavyPet]

well, i want to select rows by date from a file. but I want in particular one sum of values from the rows that fall WITHIN a supplied date range, and a second sum of values from the rows that have dates FOR ALL TIME UP TO the second date in the date range.


the former, by itself, might be:

  SELECT id, value RangedValue
    FROM myFile
   WHERE date >= [lower date range value]
     AND date <= [higher date range value]

and the latter, by itself, might be:

  SELECT id, value AllTimeValue
    FROM myFile
   WHERE date <= [higher date range value]

but I need to grab the two separate sums (RangedValue and AllTimeValue) using one SQL statement.


I'm thinking that the UNION might work, but my preliminary results are taking a huge amount of time, and apparently smegging up the (rather stupid, external) report generator to boot.
If you like the UNION idea, please give me an example.

I should mention that the report generator at very least can do the (summing) part. I could do the summing at either the SQL level or the report level. I should also mention that although I only talk about the one file here (myFile), in fact I need to join to and pull values from its "parent" file, although I don't think that that should change my fundamental problem.

Any ideas?

Cheers in advance!

 

[PHV]

To speed up the queries you may consider creating an index on the date column.
The JOIN seems obvious:
SELECT id, value, "RangedValue"
FROM myFile
WHERE date >= [lower date range value]
AND date <= [higher date range value]
UNION
SELECT id, value, "AllTimeValue"
FROM myFile
WHERE date <= [higher date range value]
You encounter some problem if the real column names are reserved words like date or value.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[swampBoogie]

Using " as string delimiter is not ANSI SQL compliant.