Good day,

I am having trouble with my condition, i dont know where should i put where in my query

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
                ,ISNULL(Open_Order_Quantity,0) AS Open_Order_Quantity


                ,ISNULL(Order_Quantity_NKA,0)as Order_Quantity_NKA
                ,ISNULL(Order_Quantity_RKA,0) AS Order_Quantity_RKA
                ,ISNULL(Order_Quantity_Others,0) AS Order_Quantity_Others
                ,ISNULL(Stock,0) AS Stock
                ,ISNULL(DSD_Delivery,0) AS DSD_Delivery
                ,ISNULL(OUtBound_Haulage,0) AS OUtBound_Haulage
                ,ISNULL(Total_OutBound_items,0) AS Total_OutBound_items
                ,ISNULL(InBound_Haulage,0) as InBound_Haulage
                ,ISNULL(Haulage_In_Transit,0) AS Haulage_In_Transit
                ,ISNULL(Process_Order,0) AS Process_Order
                ,ISNULL(Total_Inbound_Items,0) AS Total_Inbound_Items


                --pivot
                ,ISNULL([IN TRANSIT],0) as [IN TRANSIT]
                ,ISNULL(HAULAGE,0)as HAULAGE
                ,ISNULL(PRODUCTION,0) as PRODUCTION


                --computation

                ,(ISNULL(Haulage_In_Transit,0) * ISNULL([IN TRANSIT],0))/100 as Custom_Haulage_In_Transit
                ,CONVERT(INT,(ISNULL(InBound_Haulage,0)) - ISNULL(Haulage,0) / 100) as Custom_Inbound_Haulage
                ,CONVERT(INT,(ISNULL(Process_Order,0) * ISNULL(PRODUCTION,0))/100) as  Custom_Process_Order

                --computation for custom total inbound
                ,CONVERT(INT,(ISNULL(Custom_Haulage_In_Transit,0) + ISNULL(Custom_Inbound_Haulage,0) 
                + ISNULL(Custom_Process_Order,0))) as Custom_Total_Inbound


                --computation for Custom ATP Quantity
                ,((ISNULL(Stock,0) - ISNULL(Total_OutBound_items,0)) + ISNULL(Custom_Total_Inbound,0)) 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 filter my query, where should i put where condition in this query.

thanks

you can place where clause after your last line at the end or after line 92.

SELECT      distinct    MATERIAL
                ,Description
                ,Material_Type
--              ,Requested_Delivery_Date
                ,ATP_check
                ,Delivering_Plant
                ,Order_Quantity
                ,Held_by_Finance
                ,Rejected_OOS
                ,Rejected_Others
                ,ISNULL(Open_Order_Quantity,0) AS Open_Order_Quantity


                ,ISNULL(Order_Quantity_NKA,0)as Order_Quantity_NKA
                ,ISNULL(Order_Quantity_RKA,0) AS Order_Quantity_RKA
                ,ISNULL(Order_Quantity_Others,0) AS Order_Quantity_Others
                ,ISNULL(Stock,0) AS Stock
                ,ISNULL(DSD_Delivery,0) AS DSD_Delivery
                ,ISNULL(OUtBound_Haulage,0) AS OUtBound_Haulage
                ,ISNULL(Total_OutBound_items,0) AS Total_OutBound_items
                ,ISNULL(InBound_Haulage,0) as InBound_Haulage
                ,ISNULL(Haulage_In_Transit,0) AS Haulage_In_Transit
                ,ISNULL(Process_Order,0) AS Process_Order
                ,ISNULL(Total_Inbound_Items,0) AS Total_Inbound_Items


                --pivot
                ,ISNULL([IN TRANSIT],0) as [IN TRANSIT]
                ,ISNULL(HAULAGE,0)as HAULAGE
                ,ISNULL(PRODUCTION,0) as PRODUCTION


                --computation

                ,(ISNULL(Haulage_In_Transit,0) * ISNULL([IN TRANSIT],0))/100 as Custom_Haulage_In_Transit
                ,CONVERT(INT,(ISNULL(InBound_Haulage,0)) - ISNULL(Haulage,0) / 100) as Custom_Inbound_Haulage
                ,CONVERT(INT,(ISNULL(Process_Order,0) * ISNULL(PRODUCTION,0))/100) as  Custom_Process_Order

                --computation for custom total inbound
                ,CONVERT(INT,(ISNULL(Custom_Haulage_In_Transit,0) + ISNULL(Custom_Inbound_Haulage,0) 
                + ISNULL(Custom_Process_Order,0))) as Custom_Total_Inbound


                --computation for Custom ATP Quantity
                ,((ISNULL(Stock,0) - ISNULL(Total_OutBound_items,0)) + ISNULL(Custom_Total_Inbound,0)) 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

  -- add where clause


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

  -- add where clause

@IIM it did your suggestion but it returns nothing.. in the record that iam querying there must return.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.