CODEWITHSUNDEEP
sql-server-2008-r2
Guna
Guna

Reputation: 59

Adding calculation in where condition

I have the following code 

Select MR.MRPRoductId,MR.PartNo, E.MaterialName as Producttomake,EP.MaterialName as NeededProduct,(MR.ApprovedQty*PCM.NeededQuantity-isnull(SUM(ISS.IssuedQty),0))as Needed,PCM.NeededProductPartNo,
EP.UOM,ISNULL(SM.Quantity,0)as Available from Material_Requisition MR 
join Productconversion_master PCM on MR.PartNo=PCM.ConvertedProductPartNo 
Left join ProductConversion_IssueSlip ISS on PCM.NeededProductPartNo=ISS.IssuedPartNo
join ElectricalProducts E on MR.PartNo=E.PartNo join ElectricalProducts EP on PCM.NeededProductPartNo=EP.PartNo 
Left join StockMaster SM  on PCM.NeededProductPartNo=SM.PartNo and SM.LocationId='LOC_001' where MR.MRID='TFIPL/MR/CON/NR/2013-14/00001' 
 group by EP.MaterialName,MR.MRPRoductId,MR.PartNo,E.MaterialName,
MR.ApprovedQty,PCM.NeededQuantity,PCM.NeededProductPartNo,EP.UOM,SM.Quantity

the result of this query as follows

Result

i want to select only Needed>0 .how to add it in where condition

Upvotes: 0

Views: 23

Answers (1)

gvee
gvee

Reputation: 17161

Two options.

Option 1 - simplest in my opinion

SELECT list
     , of
     , columns
FROM   (
        <your query goes here>
       ) As a_subquery
WHERE  needed > 0

Option 2

<your_query goes here>
HAVING (MR.ApprovedQty*PCM.NeededQuantity-isnull(SUM(ISS.IssuedQty),0)) > 0

You can't use a WHERE condition in this scenario because it is applying to an aggregate. The equivalent to this for aggregates is the HAVING clause (which is placed after GROUP BY)

Upvotes: 1

Related Questions