07-30-04, 12:55 #1Registered User
- Join Date
- Dec 2003
Unanswered: Looking for help with an SQL query.
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
WHERE date >= [lower date range value]
AND date <= [higher date range value]
and the latter, by itself, might be:
SELECT id, value AllTimeValue
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
Cheers in advance!
07-30-04, 14:45 #2Registered User
- Join Date
- Jun 2003
- West Palm Beach, FL
You could try something like this:
Select Id , Sum(Value) Alltimevalue , Sum(Case When Date >= [Lower Date Range Value] Then Value Else 0 End) As Rangedvalue From Myfile Where Date <= [Higher Date Range Value];The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb