How to calculate the a third column based on the values of two previous columns?

My sample data is as follows:

Table T1:

+------+-------+
| Item | Order |
+------+-------+
| A    |    30 |
| B    |     3 |
| C    |    15 |
| A    |    10 |
| B    |     2 |
| C    |    15 |
+------+-------+

​x
 
+------+-------+| Item | Order |+------+-------+| A    |    30 || B    |     3 || C    |    15 || A    |    10 || B    |     2 || C    |    15 |+------+-------+​

Table T2:

+------+-------+----------+--------+
| Item | Stock | Released | Packed |
+------+-------+----------+--------+
| A    |    30 |       10 |      0 |
| B    |    20 |        0 |      5 |
| C    |    10 |        5 |      5 |
+------+-------+----------+--------+

 
+------+-------+----------+--------+| Item | Stock | Released | Packed |+------+-------+----------+--------+| A    |    30 |       10 |      0 || B    |    20 |        0 |      5 || C    |    10 |        5 |      5 |+------+-------+----------+--------+​

Now, my requirement is to fetch the data in the following form:

+------+-------+-----------+----------------+
| Item | Order | Available | Availability % |
+------+-------+-----------+----------------+
| A    |    40 |        20 |          50.00 |
| B    |     5 |        15 |         100.00 |
| C    |    30 |         0 |           0.00 |
+------+-------+-----------+----------------+

 
+------+-------+-----------+----------------+| Item | Order | Available | Availability % |+------+-------+-----------+----------------+| A    |    40 |        20 |          50.00 || B    |     5 |        15 |         100.00 || C    |    30 |         0 |           0.00 |+------+-------+-----------+----------------+​

I am able to get the data of the first three columns using:

SELECT
T1.Item AS Item, SUM(T1.Order) AS Order, T2.Stock - T2.Released - T2.Packed AS Available
FROM T1 INNER JOIN T2 ON T1.Item = T2.Item
GROUP BY T1.Item, T2.Stock, T2.Released, T2.Packed

 
SELECTT1.Item AS Item, SUM(T1.Order) AS Order, T2.Stock - T2.Released - T2.Packed AS AvailableFROM T1 INNER JOIN T2 ON T1.Item = T2.ItemGROUP BY T1.Item, T2.Stock, T2.Released, T2.Packed​

My question is: Is there a way to calculate the third column using the calculated values of columns 2 and 3 instead of writing down the entire formulas used to calculate those 2 columns? The reason is that the formula for calculating the third column is not small and uses the values of 2 and 3 multiple times.

Is there a way to do something like:

(CASE WHEN Available = 0 THEN 0
ELSE (CASE WHEN Available > Order THEN 100 ELSE Available/Order END) END) AS [Availability %]

 
(CASE WHEN Available = 0 THEN 0ELSE (CASE WHEN Available > Order THEN 100 ELSE Available/Order END) END) AS [Availability %]​

What would you suggest?

Note: Please ignore the syntax used in the CASE expressions used above, I have used it just to explain the formula.

Answer

by usuing sub-query you can do that

 with cte as

    (
    SELECT
    T1.Item AS Item, 
    SUM(T1.Order) AS Order,
   T2.Stock - T2.Released, T2.Packed AS Available
    FROM T1 INNER JOIN T2 ON T1.Item = T2.Item
    GROUP BY T1.Item, T2.Stock, T2.Released, T2.Packed
    ) select cte.*,
   (
    CASE WHEN Available = 0 THEN 0
    ELSE (CASE WHEN Available > Order THEN 100 ELSE 
    100/(Order/Available)*1.00 END
    ) END) AS [Availability %] from cte

 
 with cte as​    (    SELECT    T1.Item AS Item,     SUM(T1.Order) AS Order,   T2.Stock - T2.Released, T2.Packed AS Available    FROM T1 INNER JOIN T2 ON T1.Item = T2.Item    GROUP BY T1.Item, T2.Stock, T2.Released, T2.Packed    ) select cte.*,   (    CASE WHEN Available = 0 THEN 0    ELSE (CASE WHEN Available > Order THEN 100 ELSE     100/(Order/Available)*1.00 END    ) END) AS [Availability %] from cte​

Advertisement

Answer