Calculation query

rapht · #1 (**permalink**) 07-03-08, 11:31

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 ?

r937 · #2 (**permalink**) 07-03-08, 15:50

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 )

rapht · #3 (**permalink**) 07-04-08, 09:32

Thanks !

Internet Services

Web Development

Digital Marketing

Consumer Tech