SELECT Market_Code , Original_Order_Amt_USD , Shipped_Amt_USD_Total , Plan_to_Ship_Amt_USD , Shipped_Amt_USD_Total / (Original_Order_Amt_USD - Original_Order_Amt_USD) * 100 AS Shipped_Percentage_Total , Invoiced_Amt_USD , Plan_to_Invoice_Order_Amt_USD_Tot , Release_Plan_to_Invoice_Order_Amt_USD_Tot , Release_Shipped_Amt_USD_Total , CASE WHEN Release_Plan_to_Invoice_Order_Amt_USD_Tot = 0 THEN 0 ELSE Shipped_Amt_USD_Total / Release_Plan_to_Invoice_Order_Amt_USD_Tot END AS Release_Shipped_Percentage , Plan_to_Invoice_Order_Amt_USD_Outstanding , Plan_to_Invoice_Order_Amt_USD_Overdue FROM (SELECT Market_Code , SUM(Original_Order_Amt_USD * price) AS Original_Order_Amt_USD , SUM(Shipped_Amt_USD_Total * price) AS Shipped_Amt_USD_Total , SUM(Plan_to_Ship_Amt_USD * price) AS Plan_to_Ship_Amt_USD , SUM(Plan_to_Invoice_Order_Amt_USD * price) AS Plan_to_Invoice_Order_Amt_USD_Tot , SUM(CASE WHEN Status = '1' AND dt1 < dt2 THEN Plan_to_Invoice_Order_Amt_USD END * price) AS Plan_to_Invoice_Order_Amt_USD_Overdue , SUM(CASE WHEN Status = '1' AND dt1 > dt2 THEN Plan_to_Invoice_Order_Amt_USD END * price) AS Plan_to_Invoice_Order_Amt_USD_Outstanding , SUM(CASE WHEN Status = '1' THEN Plan_to_Invoice_Order_Amt_USD END * price) AS Release_Plan_to_Invoice_Order_Amt_USD_Tot , SUM(CASE WHEN Status = '1' THEN Shipped_Amt_USD_Total END * price) AS Release_Shipped_Amt_USD_Total FROM (SELECT CASE CASE WHEN SUBSTRING(DIM_IN.[Dimension Value Code], 1, 2) = 'FM' THEN 'FM' ELSE SUBSTRING(DIM_IN.[Dimension Value Code], 1, 1) END WHEN 'A' THEN 'Alternative Energy' WHEN 'E' THEN 'Others - Non-Core' WHEN 'FM' THEN 'Magna Fluids' WHEN 'F' THEN 'Industrial MRO - Fluids' WHEN 'H' THEN 'Heavy Industries' WHEN 'I' THEN 'Infrastructure' WHEN 'M' THEN 'Marine' WHEN 'O' THEN 'OEM (Electronics/Appliances)' WHEN 'P' THEN 'Industrial MRO - Polymers' WHEN 'T' THEN 'Transportation' ELSE '--None--' END AS Market_Code , SL.QUANTITY AS Original_Order_Amt_USD , SL.[QTY_ SHIPPED (BASE)] AS Shipped_Amt_USD_Total , SL.[QTY_ TO SHIP] AS Plan_to_Ship_Amt_USD , SL.[Qty_ Invoiced (Base)] AS Invoiced_Amt_USD , SL.QUANTITY - SL.[Qty_ Invoiced (Base)] AS Plan_to_Invoice_Order_Amt_USD , SL.[UNIT PRICE] * CASE WHEN SH.[Currency Code] = '' THEN PEG.[EXCHANGE RATE] ELSE 1 END AS price , Status , CONVERT(VARCHAR(10), SH.[Requested Delivery Date], 23) dt1 , CONVERT(VARCHAR(10), getdate() , 23) dt2 FROM [ITW].[dbo].[ITW PP&F KOREA$SALES HEADER] AS SH INNER JOIN [ITW].[dbo].[ITW PP&F KOREA$SALES LINE] AS SL ON SH.NO_ = SL.[DOCUMENT NO_] LEFT OUTER JOIN [ITW].[dbo].[ITW PP&F KOREA$ITEM] AS IT ON SL.NO_ = IT.NO_ LEFT OUTER JOIN [ITW].[dbo].[ITW PP&F KOREA$DOCUMENT DIMENSION] AS DIM_IN ON DIM_IN.[DOCUMENT NO_] = SL.[DOCUMENT NO_] AND DIM_IN.[LINE NO_] = SL.[LINE NO_] AND DIM_IN.[DIMENSION CODE] = 'INDUSTRY' INNER JOIN [ITW].[dbo].[ITW PP&F KOREA$PEGGED RATE] AS PEG ON PEG.YEAR = SUBSTRING(CONVERT(VARCHAR, SH.[Posting Date], 112), 1, 4) WHERE SH.[DOCUMENT TYPE] = '1' AND SL.[Quantity (Base)] > SL.[Qty_ Invoiced (Base)] AND sh.No_ NOT IN ('SO013002046', 'SO013000387', 'SO013000810') ) a GROUP BY Market_Code ) a ;