Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Posts
    5

    Question 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
    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!

    -Glenn

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    You could try something like this:
    Code:
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •