sql formula

[cyberdaemon]

Good day,

I am having troulbe with my computation here. i want to compute for the Custom_Haulage_In_Transit but when i exec my sp the value of the Custom_Haulage_In_Transit is 0. can anyone help me with this please..

here is my code

SELECT      distinct    MATERIAL
                ,Description
                ,Material_Type
--              ,Requested_Delivery_Date
                ,ATP_check
                ,Delivering_Plant
                ,Order_Quantity
                ,Held_by_Finance
                ,Rejected_OOS
                ,Rejected_Others
                ,Open_Order_Quantity
                ,case when Order_Quantity_NKA is null 
                        then '0' 
                        else Order_Quantity_NKA 
                        end 
                as Order_Quantity_NKA

                ,case when Order_Quantity_RKA is null 
                        then '0' 
                        else Order_Quantity_RKA 
                        end 
                as Order_Quantity_RKA

                ,case when Order_Quantity_Others is null 
                        then '0' 
                        else Order_Quantity_Others 
                        end 
                as Order_Quantity_Others

                ,case when Stock is null 
                        then '0' 
                        else Stock 
                        end 
                as Stock

                ,case when DSD_Delivery is null 
                        then '0' 
                        else DSD_Delivery 
                        end 
                as DSD_Delivery

                ,case when OUtBound_Haulage is null 
                        then '0' 
                        else OUtBound_Haulage 
                        end 
                as OUtBound_Haulage

                ,case when Total_OutBound_items is null 
                        then '0' 
                        else Total_OutBound_items 
                        end 
                as Total_OutBound_items

                ,case when InBound_Haulage is null 
                        then '0' 
                        else InBound_Haulage 
                        end 
                as InBound_Haulage

                ,case when Haulage_In_Transit is null 
                        then '0' 
                        else Haulage_In_Transit 
                        end 
                as Haulage_In_Transit

                ,Process_Order

                ,case when Total_Inbound_Items is null 
                        then '0' 
                        else Total_Inbound_Items  
                        end 
                as Total_Inbound_Items 


                --pivot
                ,case when [IN TRANSIT] is null 
                        then '0' 
                        else [IN TRANSIT]  
                        end 
                as [IN TRANSIT] 

                ,case when HAULAGE is null 
                        then '0' 
                        else HAULAGE  
                        end 
                as HAULAGE

                ,case when PRODUCTION is null 
                        then '0' 
                        else PRODUCTION  
                        end 
                as PRODUCTION


                --computation
                ,  case when Custom_Haulage_In_Transit is null 
                        then '0' 
                        else  (Haulage_In_Transit * [IN TRANSIT])/100 
                        end 
                as Custom_Haulage_In_Transit


                ,case when Custom_Inbound_Haulage is null 
                        then '0' 
                        else ((InBound_Haulage - HAULAGE) / 100)
                        end 
                as Custom_Inbound_Haulage



                ,case when Custom_Process_Order is null 
                        then '0' 
                        else (Process_Order * PRODUCTION) / 100
                        end 
                as Custom_Process_Order  


                ,case when Custom_Total_Inbound  is null 
                        then '0' 
                        else ((Haulage_In_Transit  *  [IN TRANSIT])/100 + (InBound_Haulage - HAULAGE)/100 + (Process_Order * PRODUCTION) / 100) 
                        end 
                as Custom_Total_Inbound


                ,case when Custom_ATP_Quantity is null 
                        then '0' 
                        else ((Stock - Total_OutBound_items) + Custom_Total_Inbound) 
                        end 
                as Custom_ATP_Quantity
FROM(
    SELECT
             *  
    FROM 
        (
            SELECT 
                REPORT.MATERIAL
                ,REPORT.Description
                ,REPORT.Material_Type
--              ,REPORT.Requested_Delivery_Date
                ,REPORT.ATP_check
                ,REPORT.Delivering_Plant
                ,REPORT.Order_Quantity
                ,REPORT.Held_by_Finance
                ,REPORT.Rejected_OOS
                ,REPORT.Rejected_Others
                ,REPORT.Open_Order_Quantity
                ,REPORT.Order_Quantity_NKA
                ,REPORT.Order_Quantity_RKA
                ,REPORT.Order_Quantity_Others
                ,REPORT.Stock
                ,REPORT.DSD_Delivery
                ,REPORT.OUtBound_Haulage
                ,REPORT.Total_OutBound_items
                ,REPORT.InBound_Haulage 

                ,REPORT.Haulage_In_Transit
                ,REPORT.Process_Order
                ,REPORT.Total_Inbound_Items

                ,REPORT.Custom_Haulage_In_Transit
                ,REPORT.Custom_Inbound_Haulage
                ,REPORT.Custom_Process_Order
                ,REPORT.Custom_Total_Inbound
                ,REPORT.Custom_ATP_Quantity 

                ,TYPES.*

                FROM CO09_Report REPORT
                    INNER JOIN TBLTYPES TYPES
                        ON REPORT.MATERIAL = TYPES.TXTMATERIAL


        )AS T
    PIVOT (SUM(PERCENTAGE)
        FOR [TYPE] IN ([In Transit] , [HAULAGE], [PRODUCTION] ))
        AS pvt

) temp

i want to compute for the ff.. Custom_Haulage_In_Transit,Custom_Inbound_Haulage,Custom_Process,Custom_Total_Inbound and Custom_ATP_Quantity

the formula is already there

thanks

[adam_k]

I'll be honest, I didn't read everything. But what I did read, makes me think that you are replacing null with 0 in your columns but not in your formulas.

Also it would be easier to read and create to use isnull() instead of case.

To make it easier for you, I would replace NULLs (either with case or isnull()) in the T derived table. This way you don't need to change your formulas and add additional complexity there.

[cyberdaemon]

@adam k, thank for your immediate reply, it already tried it and it works very well, i thought that is null is same as isnull. now it's clear for me the difference.

by the way another query... how could i convert the custom_total_inbound into integer. because the result is decimal part.?

thank you in advance..

[adam_k]

I hope you've sorted the conversion - didn't login for a week or so, sorry about that.
If not you convert(int,custom_total_inbound) or convert(int, isnull(custom_total_inbound,0))