Member Avatar for cyberdaemon

Good day,
I am having a probem with my sql. I want to create a formula for my pivot field.. here is my code...

SELECT
        *

FROM 
    (
        SELECT 
            REPORT.MATERIAL
            ,REPORT.Description
            ,REPORT.Material_Type
            ,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 = SUBSTRING(TYPES.TXTMATERIAL,13,18)

--WHERE REPORT.Custom_ATP_Quantity >  0 


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

my problem is that how can i multiply REPORT.Haulage_In_Transit to [IN TRANSIT] that is located in the pivot
can anyone has an idea about this..?

thanks

  • 2 Contributors
  • 2 Replies
  • 217 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by cyberdaemon
Member Avatar for BitBlt

You could always create a temp table to hold the results of your pivot, then join that back to your original table...if I understand what you're asking for, that is.

Member Avatar for cyberdaemon

anyways, i already done this, i just inserted another select at the top of the queries from thier i created the formula.

thanks BitBit for suggestion..

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.