Smith Smith - 7 months ago 11
SQL Question

Select row referring to another row conditionally sqlite

I have a table in which I want to calculate some row values based on another row value conditionally.

The table looks like this

LevelID StepID Amt Type BItem PItem
-------------------------------------------------
6 3 18000 Fixed BS
6 3 10 Percent BS UA
6 3 10 Percent BS TA
6 3 3.5 Percent BS Tx
7 3 24000 Fixed BS
7 3 10 Percent BS UA
7 3 10 Percent BS TA
7 3 3.5 Percent BS Tx


The aim is to calculate the Amt value of rows with
Type
= 'Percent' Where Bitem = PItem and LevelID and StepID are the same for both BItem and PItem

So a sample output would be

LevelID StepID Amt Type BItem PItem Total
----------------------------------------------------------
6 3 18000 Fixed BS 18,000
6 3 10 Percent BS UA 1,800
6 3 10 Percent BS TA 1,800
6 3 3.5 Percent BS Tx 630
7 3 24000 Fixed BS 24,000
7 3 10 Percent BS UA 2,400
7 3 10 Percent BS TA 2,400
7 3 3.5 Percent BS Tx 840


I have been trying different SQL statements, but haven't got any reasonable output yet

CL. CL.
Answer

To look up values, use a correlated subquery:

SELECT *,
       CASE Type
       WHEN 'Fixed' THEN Amt
       ELSE (SELECT B.Amt * ATable.Amt / 100
             FROM ATable AS B
             WHERE B.Type = 'Fixed'
               AND B.LevelID = ATable.LevelID
               AND B.StepID  = ATable.StepID
               AND B.PItem   = ATable.BItem)
       END AS Total
FROM ATable;
Comments