Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2008
    Posts
    29

    Unanswered: Calculation query

    Hi all,

    I'd need help with the following problem:

    The 3 following table are necessary :
    - XLineSource (source_id, line_id, xc_base, xc_val)
    - Line (line_id, ... some other fields)
    - Cond (cond_id, line_id, cond_start, cond_coeff)

    Each Line entry can have many Cond entries, and each Line has one and only one XLineSource as source_id is fixed.

    The Cond entries for each line make up a scale, like :
    Code:
    cond_id | line_id | cond_start | cond_coeff
         1  |       1 |       1000 |        0.1
         2  |       1 |       2000 |        0.5
         3  |       1 |       5000 |        1.1
    I need to return cond_id, as well as return the result of cond_coeff * xc_val, having determined the right Cond using xc_base.
    For instance, in the case above:
    xc_base = 250 <=> cond_id = NULL <=> result = xc_val * 0
    xc_base = 2500 <=> cond_id = 2 <=> result = xc_val * 0.5
    xc_base = 6500 <=> cond_id = 3 <=> result = xc_val * 1.1

    Can this be done in one query ?
    Last edited by rapht; 07-03-08 at 12:36.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT Cond.cond_id
         , Cond.cond_coeff
         , XLineSource.xc_val
         , XLineSource.xc_val * COALESCE(Cond.cond_coeff,0) AS result
      FROM Line
    INNER
      JOIN XLineSource
        ON XLineSource.line_id = Line.line_id
    LEFT OUTER
      JOIN Cond 
        ON Cond.line_id = Line.line_id
       AND Cond.cond_start = 
           ( SELECT MAX(cond_start)
               FROM Cond
              WHERE line_id = Line.line_id
                AND cond_start <= XLineSource.xc_base )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2008
    Posts
    29
    Thanks !

Posting Permissions

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