Click here to Skip to main content
15,885,366 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
SQL
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)
Posted
Updated 25-Sep-14 2:33am
v2
Comments
PhilLenoir 25-Sep-14 8:53am    
Why, what's the issue?
Herman<T>.Instance 25-Sep-14 10:07am    
What does it do? What is the pain?
Kishore Pogaru 25-Sep-14 10:58am    
If you give more info on

1) What are you going to fetch with this query?
2) What is the issue with current query?
3) Do you have any performance issues with the current query?

We can help you

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900