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 |
+------+-------+

Table T2:

+------+-------+----------+--------+
| 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 |
+------+-------+-----------+----------------+

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

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 %]

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

Advertisement

Answer