with abc as ( select sum(taItemSalesPriceSet.coNormalPrice * taLinkDunCode_Item.coQty) as totallinesum from taDuncode LEFT OUTER JOIN taLinkDunCode_Item ON taDuncode.copkDunCodeNo=taLinkDunCode_Item.copfkDunCodeNo LEFT OUTER JOIN taItemSalesPriceSet ON taLinkDunCode_Item.copfkItemNo = taItemSalesPriceSet.copfkItemNo where copkduncodeno='1'), aaa as ( select case when abc.totallinesum <> 0 then (taDuncode.coDiscAmt* 100)/abc.totallinesum else 0 end as discperc from taDuncode, abc where copkduncodeno='1' ), xyz as( select (((((taItemSalesPriceSet.coNormalPrice * taLinkDunCode_Item.coQty) - ((taItemSalesPriceSet.coNormalPrice * taLinkDunCode_Item.coQty)*aaa.discperc/100)) - case when covatefftax <> 0 then (((taItemSalesPriceSet.coNormalPrice * taLinkDunCode_Item.coQty) - ((taItemSalesPriceSet.coNormalPrice * taLinkDunCode_Item.coQty)*aaa.discperc/100))/((100 / covatefftax) + 1)) else 0 end)-(talinkitem_supplier.coNetPurchasePrice *taLinkDunCode_Item.coQty))) as one, (((taItemSalesPriceSet.coNormalPrice * taLinkDunCode_Item.coQty) - ((taItemSalesPriceSet.coNormalPrice * taLinkDunCode_Item.coQty)*aaa.discperc/100)) - ((case when covatefftax <> 0 then ((taItemSalesPriceSet.coNormalPrice * taLinkDunCode_Item.coQty) - ((taItemSalesPriceSet.coNormalPrice * taLinkDunCode_Item.coQty)*aaa.discperc/100))/((100 / covatefftax) + 1) else 0 end))) as two from taDuncode LEFT OUTER JOIN taLinkDunCode_Item ON taDuncode.copkDunCodeNo=taLinkDunCode_Item.copfkDunCodeNo LEFT OUTER JOIN taItemSalesPriceSet ON taLinkDunCode_Item.copfkItemNo = taItemSalesPriceSet.copfkItemNo inner JOIN TaItems ON taLinkDunCode_Item.copfkItemNo = TaItems.copkItemNo inner JOIN taVatCode ON TaItems.cofkVatCode = taVatCode.copkvatcode inner JOIN talinkitem_supplier ON taLinkDunCode_Item.copfkItemNo = talinkitem_supplier.copfkItemNo inner join talinkitem_profile on talinkitem_supplier.copfkItemNo = talinkitem_profile.copfkItemNo and talinkitem_supplier.copfkSupProfileNo=1 and talinkitem_supplier.copfkSupProfileNo = talinkitem_profile.copfkProfileNo and talinkitem_supplier.copfkSupplierNo=taLinkItem_Profile.cofkActiveSupplierNo , abc, aaa where copkduncodeno='1' ) (select case when sum(two)<>0 then convert(decimal(20,2),(sum(one)/sum(two))*100) else 0 end as gpp from xyz)
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)