CODEWITHSUNDEEP
sqlsqlite
Smith
Smith

Reputation: 5961

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

Upvotes: 1

Views: 20

Answers (1)

CL.
CL.

Reputation: 180020

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;

Upvotes: 1

Related Questions