CODEWITHSUNDEEP
sqlsql-server
WishIWasACoder
WishIWasACoder

Reputation: 79

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.

Upvotes: 4

Views: 154

Answers (4)

Helgato
Helgato

Reputation: 151

I think that your result table have some mistakes.. But you can have the required result by typing:

Select final_tab.Item,
final_tab.ordered,
final_tab.Available,
CASE WHEN final_tab.Available = 0 THEN 0
 ELSE 
 (CASE WHEN final_tab.Available > final_tab.ordered THEN 100 
       ELSE convert(float,final_tab.Available)/convert(float,final_tab.ordered)*100 END)
END AS [Availability %]
from
(Select tab1.Item,tab1.ordered,
       (Table_2.Stock-Table_2.Released-Table_2.Packed)as Available 
from 
  ( SELECT Item,sum([Order]) as ordered
    FROM Table_1
    group by Item )as tab1
left join 
   Table_2
on tab1.Item=Table_2.Item)as final_tab

Upvotes: 1

Pawel Czapski
Pawel Czapski

Reputation: 1864

You can try to play with below as well, make sure you have tested output.

declare @t1 table ([item] char(1), [order] int)

insert into @t1
values ('A', 30),
    ('B', 3),
    ('C', 30),
    ('A', 15),
    ('A', 10),
    ('B', 2),
    ('C', 15)

declare @t2 table ([item] char(1), [stock] int, [released] int, [packed] int)

insert into @t2 
values ('A',30,10,0),
    ('B',20,0,5),
    ('C',10,5,5)


SELECT
    T1.Item AS Item, 
    SUM(T1.[Order]) AS [Order], 
    T2.Stock - T2.Released as Available, 
    case when SUM(T1.[Order]) < (T2.Stock - T2.Released) then 100
        else  cast(cast((T2.Stock - T2.Released) as decimal) / cast(SUM(T1.[Order]) as decimal) * 100 as decimal(4,2))
        end AS AvailablePercentage
FROM @T1 t1 
    INNER JOIN @T2 t2 ON T1.Item = T2.Item
GROUP BY T1.Item, T2.Stock, T2.Released, T2.Packed

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

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

Upvotes: 1

GuidoG
GuidoG

Reputation: 12014

If you dont wont to use a CTE you can do it like this

declare @t1 table (item varchar(1), orderqty int)
declare @t2 table (item varchar(1), stock int, released int, packed int)

insert into @t1 values ('A', 30), ('B', 3), ('C', 15), ('A', 10), ('B', 2), ('C', 15)
insert into @t2 values ('A', 30, 10, 0), ('B', 20, 0, 5), ('C', 10, 5, 5)

select q.Item,
       q.orderqty, 
       q.available,
       case when q.available = 0 then 0
            when q.available > q.orderqty then 100
            else 100 / (q.orderqty / q.available) -- or whatever formula you need
       end as [availability %] 
from   ( select t1.Item,
                sum(t1.orderqty) as orderqty,     
                t2.Stock - t2.Released - t2.Packed as available
         from   @t1 t1
           left outer join @t2 t2 on t1.Item = t2.Item
         group by t1.Item, t2.Stock, t2.Released, t2.Packed
       ) q

The result is

Item    orderqty    available   availability %  
----    --------    ---------   --------------  
A       40          20           50 
B        5          15          100 
C       30           0            0

Upvotes: 1

Related Questions