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