Sigh....for whatever reason, whenever I try to do "Select Top 100 Percent * from..." in T-SQL, I can never get the placement of the ending parentheses correct.
Please review my query below to let me know where my syntax is wrong.
Thank you!
John
Select [Item Number], [Item Description], [Location Code], [Base U of M], [QTY On Hand], [QTY On Order], [QTY Available], [Replenishment Method], [Order Policy], [Fixed Order Qty],[Order Point Qty], [Order Up To Level], SUM([Recent Consumption]), SUM([Average Consumption]), [Buyer ID], [Planner ID], [PRODUCT LINE], [Item Class Code] from (select TOP 100 PERCENT * from (--not 70; inventory adjustments; 3 months (Recent Consumption)select IV00102.ITEMNMBR AS [Item Number], IV00101.ITEMDESC as [Item Description], IV00102.LOCNCODE as [Location Code], IV40201.BASEUOFM as [Base U of M], IV00102.QTYONHND as [QTY On Hand], IV00102.QTYONORD as [QTY On Order],IV00102.QTYONHND - IV00102.ATYALLOC as [QTY Available],CASE WHEN IV00102.REPLENISHMENTMETHOD = 0 THEN ''WHEN IV00102.REPLENISHMENTMETHOD = 1 THEN 'Make'WHEN IV00102.REPLENISHMENTMETHOD = 2 THEN 'Buy'WHEN IV00102.REPLENISHMENTMETHOD = 3 THEN 'Make or Buy' END as [Replenishment Method], CASE WHEN IV00102.ORDERPOLICY = 1 THEN 'Not Planned'WHEN IV00102.ORDERPOLICY = 2 THEN 'Lot for Lot'WHEN IV00102.ORDERPOLICY = 3 THEN 'Fixed Order Quantity'WHEN IV00102.ORDERPOLICY = 4 THEN 'Period Order Quantity'WHEN IV00102.ORDERPOLICY = 5 THEN 'Order Point'WHEN IV00102.ORDERPOLICY = 6 THEN 'Manually Planned'END as [Order Policy],IV00102.FXDORDRQTY AS [Fixed Order Qty], IV00102.ORDRPNTQTY as [Order Point Qty], IV00102.ORDRUPTOLVL AS [Order Up To Level],COALESCE(SUM(IV30300.EXTDCOST), 0) as [Recent Consumption],0 as [Average Consumption],IV00102.BUYERID as [Buyer ID], IV00102.PLANNERID as [Planner ID],IV00101.USCATVLS_5 AS [PRODUCT LINE], IV00101.ITMCLSCD as [Item Class Code]from IV00102INNER JOIN IV00101 ON IV00102.ITEMNMBR = IV00101.ITEMNMBRINNER JOIN IV40201 ON IV00101.UOMSCHDL = IV40201.UOMSCHDLLEFT OUTER JOIN IV30300 ON IV00102.ITEMNMBR = IV30300.ITEMNMBR where IV00102.LOCNCODE = '70' and IV00102.ORDERPOLICY IN (2, 3, 4, 5, 6) and LEFT(IV00102.ITEMNMBR, 3) <> '70-' and IV30300.DOCDATE > DATEADD(m,-3,getdate())GROUP BY IV00102.ITEMNMBR, IV00101.ITEMDESC, IV00102.LOCNCODE, IV40201.BASEUOFM, IV00102.QTYONHND, IV00102.QTYONORD, IV00102.ATYALLOC, IV00102.REPLENISHMENTMETHOD, IV00102.ORDERPOLICY, IV00102.FXDORDRQTY,IV00102.ORDRPNTQTY, IV00102.ORDRUPTOLVL, IV00102.BUYERID, IV00102.PLANNERID, IV00101.USCATVLS_5, IV00101.ITMCLSCDUNION--not 70; inventory adjustments; 12 months/4 (Average Consumption)select IV00102.ITEMNMBR AS [Item Number], IV00101.ITEMDESC as [Item Description], IV00102.LOCNCODE as [Location Code], IV40201.BASEUOFM as [Base U of M], IV00102.QTYONHND as [QTY On Hand], IV00102.QTYONORD as [QTY On Order],IV00102.QTYONHND - IV00102.ATYALLOC as [QTY Available],CASE WHEN IV00102.REPLENISHMENTMETHOD = 0 THEN ''WHEN IV00102.REPLENISHMENTMETHOD = 1 THEN 'Make'WHEN IV00102.REPLENISHMENTMETHOD = 2 THEN 'Buy'WHEN IV00102.REPLENISHMENTMETHOD = 3 THEN 'Make or Buy' END as [Replenishment Method], CASE WHEN IV00102.ORDERPOLICY = 1 THEN 'Not Planned'WHEN IV00102.ORDERPOLICY = 2 THEN 'Lot for Lot'WHEN IV00102.ORDERPOLICY = 3 THEN 'Fixed Order Quantity'WHEN IV00102.ORDERPOLICY = 4 THEN 'Period Order Quantity'WHEN IV00102.ORDERPOLICY = 5 THEN 'Order Point'WHEN IV00102.ORDERPOLICY = 6 THEN 'Manually Planned'END as [Order Policy],IV00102.FXDORDRQTY AS [Fixed Order Qty], IV00102.ORDRPNTQTY as [Order Point Qty], IV00102.ORDRUPTOLVL AS [Order Up To Level],0 as [Recent Consumption],COALESCE(SUM(IV30300.EXTDCOST), 0)/4 as [Average Consumption],IV00102.BUYERID as [Buyer ID], IV00102.PLANNERID as [Planner ID],IV00101.USCATVLS_5 AS [PRODUCT LINE], IV00101.ITMCLSCD as [Item Class Code]from IV00102INNER JOIN IV00101 ON IV00102.ITEMNMBR = IV00101.ITEMNMBRINNER JOIN IV40201 ON IV00101.UOMSCHDL = IV40201.UOMSCHDLLEFT OUTER JOIN IV30300 ON IV00102.ITEMNMBR = IV30300.ITEMNMBR where IV00102.LOCNCODE = '70' and IV00102.ORDERPOLICY IN (2, 3, 4, 5, 6) and LEFT(IV00102.ITEMNMBR, 3) <> '70-' and IV30300.DOCDATE > DATEADD(m,-12,getdate())GROUP BY IV00102.ITEMNMBR, IV00101.ITEMDESC, IV00102.LOCNCODE, IV40201.BASEUOFM, IV00102.QTYONHND, IV00102.QTYONORD, IV00102.ATYALLOC, IV00102.REPLENISHMENTMETHOD, IV00102.ORDERPOLICY, IV00102.FXDORDRQTY,IV00102.ORDRPNTQTY, IV00102.ORDRUPTOLVL, IV00102.BUYERID, IV00102.PLANNERID, IV00101.USCATVLS_5, IV00101.ITMCLSCDUNION--70; inventory adjustments; 3 months (Recent Consumption)select IV00102.ITEMNMBR AS [Item Number], IV00101.ITEMDESC as [Item Description], IV00102.LOCNCODE as [Location Code], IV40201.BASEUOFM as [Base U of M], IV00102.QTYONHND as [QTY On Hand], IV00102.QTYONORD as [QTY On Order],IV00102.QTYONHND - IV00102.ATYALLOC as [QTY Available],CASE WHEN IV00102.REPLENISHMENTMETHOD = 0 THEN ''WHEN IV00102.REPLENISHMENTMETHOD = 1 THEN 'Make'WHEN IV00102.REPLENISHMENTMETHOD = 2 THEN 'Buy'WHEN IV00102.REPLENISHMENTMETHOD = 3 THEN 'Make or Buy' END as [Replenishment Method], CASE WHEN IV00102.ORDERPOLICY = 1 THEN 'Not Planned'WHEN IV00102.ORDERPOLICY = 2 THEN 'Lot for Lot'WHEN IV00102.ORDERPOLICY = 3 THEN 'Fixed Order Quantity'WHEN IV00102.ORDERPOLICY = 4 THEN 'Period Order Quantity'WHEN IV00102.ORDERPOLICY = 5 THEN 'Order Point'WHEN IV00102.ORDERPOLICY = 6 THEN 'Manually Planned'END as [Order Policy],IV00102.FXDORDRQTY AS [Fixed Order Qty], IV00102.ORDRPNTQTY as [Order Point Qty], IV00102.ORDRUPTOLVL AS [Order Up To Level],COALESCE(SUM(IV30300.EXTDCOST), 0) as [Recent Consumption],0 as [Average Consumption],IV00102.BUYERID as [Buyer ID], IV00102.PLANNERID as [Planner ID],IV00101.USCATVLS_5 AS [PRODUCT LINE], IV00101.ITMCLSCD as [Item Class Code]from IV00102INNER JOIN IV00101 ON IV00102.ITEMNMBR = IV00101.ITEMNMBRINNER JOIN IV40201 ON IV00101.UOMSCHDL = IV40201.UOMSCHDLLEFT OUTER JOIN IV30300 ON IV00102.ITEMNMBR = IV30300.ITEMNMBR where IV00102.LOCNCODE = '70' and IV00102.ORDERPOLICY IN (2, 3, 4, 5, 6) and LEFT(IV00102.ITEMNMBR, 3) = '70-' and IV30300.DOCDATE > DATEADD(m,-3,getdate())GROUP BY IV00102.ITEMNMBR, IV00101.ITEMDESC, IV00102.LOCNCODE, IV40201.BASEUOFM, IV00102.QTYONHND, IV00102.QTYONORD, IV00102.ATYALLOC, IV00102.REPLENISHMENTMETHOD, IV00102.ORDERPOLICY, IV00102.FXDORDRQTY,IV00102.ORDRPNTQTY, IV00102.ORDRUPTOLVL, IV00102.BUYERID, IV00102.PLANNERID, IV00101.USCATVLS_5, IV00101.ITMCLSCDUNION--70; inventory adjustments; 12 months/4 (Average Consumption)select IV00102.ITEMNMBR AS [Item Number], IV00101.ITEMDESC as [Item Description], IV00102.LOCNCODE as [Location Code], IV40201.BASEUOFM as [Base U of M], IV00102.QTYONHND as [QTY On Hand], IV00102.QTYONORD as [QTY On Order],IV00102.QTYONHND - IV00102.ATYALLOC as [QTY Available],CASE WHEN IV00102.REPLENISHMENTMETHOD = 0 THEN ''WHEN IV00102.REPLENISHMENTMETHOD = 1 THEN 'Make'WHEN IV00102.REPLENISHMENTMETHOD = 2 THEN 'Buy'WHEN IV00102.REPLENISHMENTMETHOD = 3 THEN 'Make or Buy' END as [Replenishment Method], CASE WHEN IV00102.ORDERPOLICY = 1 THEN 'Not Planned'WHEN IV00102.ORDERPOLICY = 2 THEN 'Lot for Lot'WHEN IV00102.ORDERPOLICY = 3 THEN 'Fixed Order Quantity'WHEN IV00102.ORDERPOLICY = 4 THEN 'Period Order Quantity'WHEN IV00102.ORDERPOLICY = 5 THEN 'Order Point'WHEN IV00102.ORDERPOLICY = 6 THEN 'Manually Planned'END as [Order Policy],IV00102.FXDORDRQTY AS [Fixed Order Qty], IV00102.ORDRPNTQTY as [Order Point Qty], IV00102.ORDRUPTOLVL AS [Order Up To Level],0 as [Recent Consumption],COALESCE(SUM(IV30300.EXTDCOST), 0)/4 as [Average Consumption],IV00102.BUYERID as [Buyer ID], IV00102.PLANNERID as [Planner ID],IV00101.USCATVLS_5 AS [PRODUCT LINE], IV00101.ITMCLSCD as [Item Class Code]from IV00102INNER JOIN IV00101 ON IV00102.ITEMNMBR = IV00101.ITEMNMBRINNER JOIN IV40201 ON IV00101.UOMSCHDL = IV40201.UOMSCHDLLEFT OUTER JOIN IV30300 ON IV00102.ITEMNMBR = IV30300.ITEMNMBR where IV00102.LOCNCODE = '70' and IV00102.ORDERPOLICY IN (2, 3, 4, 5, 6) and LEFT(IV00102.ITEMNMBR, 3) = '70-' and IV30300.DOCDATE > DATEADD(m,-12,getdate())GROUP BY IV00102.ITEMNMBR, IV00101.ITEMDESC, IV00102.LOCNCODE, IV40201.BASEUOFM, IV00102.QTYONHND, IV00102.QTYONORD, IV00102.ATYALLOC, IV00102.REPLENISHMENTMETHOD, IV00102.ORDERPOLICY, IV00102.FXDORDRQTY,IV00102.ORDRPNTQTY, IV00102.ORDRUPTOLVL, IV00102.BUYERID, IV00102.PLANNERID, IV00101.USCATVLS_5, IV00101.ITMCLSCDUNION--not 70; not inventory adjustments (sales); 3 months (Recent Consumption)select IV00102.ITEMNMBR AS [Item Number], IV00101.ITEMDESC as [Item Description], IV00102.LOCNCODE as [Location Code], IV40201.BASEUOFM as [Base U of M], IV00102.QTYONHND as [QTY On Hand], IV00102.QTYONORD as [QTY On Order],IV00102.QTYONHND - IV00102.ATYALLOC as [QTY Available],CASE WHEN IV00102.REPLENISHMENTMETHOD = 0 THEN ''WHEN IV00102.REPLENISHMENTMETHOD = 1 THEN 'Make'WHEN IV00102.REPLENISHMENTMETHOD = 2 THEN 'Buy'WHEN IV00102.REPLENISHMENTMETHOD = 3 THEN 'Make or Buy' END as [Replenishment Method], CASE WHEN IV00102.ORDERPOLICY = 1 THEN 'Not Planned'WHEN IV00102.ORDERPOLICY = 2 THEN 'Lot for Lot'WHEN IV00102.ORDERPOLICY = 3 THEN 'Fixed Order Quantity'WHEN IV00102.ORDERPOLICY = 4 THEN 'Period Order Quantity'WHEN IV00102.ORDERPOLICY = 5 THEN 'Order Point'WHEN IV00102.ORDERPOLICY = 6 THEN 'Manually Planned'END as [Order Policy],IV00102.FXDORDRQTY AS [Fixed Order Qty], IV00102.ORDRPNTQTY as [Order Point Qty], IV00102.ORDRUPTOLVL AS [Order Up To Level],COALESCE(SUM(SOP30300.XTNDPRCE), 0) as [Recent Consumption],0 as [Average Consumption],IV00102.BUYERID as [Buyer ID], IV00102.PLANNERID as [Planner ID],IV00101.USCATVLS_5 AS [PRODUCT LINE], IV00101.ITMCLSCD as [Item Class Code]from IV00102INNER JOIN IV00101 ON IV00102.ITEMNMBR = IV00101.ITEMNMBRINNER JOIN IV40201 ON IV00101.UOMSCHDL = IV40201.UOMSCHDLLEFT OUTER JOIN SOP30300 ON IV00102.ITEMNMBR = SOP30300.ITEMNMBR INNER JOIN SOP30200 ON SOP30300.SOPTYPE = SOP30200.SOPTYPE AND SOP30300.SOPNUMBE = SOP30200.SOPNUMBEwhere IV00102.LOCNCODE = '70' and IV00102.ORDERPOLICY IN (2, 3, 4, 5, 6) and LEFT(IV00102.ITEMNMBR, 3) <> '70-' and SOP30200.DOCDATE > DATEADD(m,-3,getdate())GROUP BY IV00102.ITEMNMBR, IV00101.ITEMDESC, IV00102.LOCNCODE, IV40201.BASEUOFM, IV00102.QTYONHND, IV00102.QTYONORD, IV00102.ATYALLOC, IV00102.REPLENISHMENTMETHOD, IV00102.ORDERPOLICY, IV00102.FXDORDRQTY,IV00102.ORDRPNTQTY, IV00102.ORDRUPTOLVL, IV00102.BUYERID, IV00102.PLANNERID, IV00101.USCATVLS_5, IV00101.ITMCLSCDUNION--not 70; not inventory adjustments (sales); 12 months/4 (Average Consumption)select IV00102.ITEMNMBR AS [Item Number], IV00101.ITEMDESC as [Item Description], IV00102.LOCNCODE as [Location Code], IV40201.BASEUOFM as [Base U of M], IV00102.QTYONHND as [QTY On Hand], IV00102.QTYONORD as [QTY On Order],IV00102.QTYONHND - IV00102.ATYALLOC as [QTY Available],CASE WHEN IV00102.REPLENISHMENTMETHOD = 0 THEN ''WHEN IV00102.REPLENISHMENTMETHOD = 1 THEN 'Make'WHEN IV00102.REPLENISHMENTMETHOD = 2 THEN 'Buy'WHEN IV00102.REPLENISHMENTMETHOD = 3 THEN 'Make or Buy' END as [Replenishment Method], CASE WHEN IV00102.ORDERPOLICY = 1 THEN 'Not Planned'WHEN IV00102.ORDERPOLICY = 2 THEN 'Lot for Lot'WHEN IV00102.ORDERPOLICY = 3 THEN 'Fixed Order Quantity'WHEN IV00102.ORDERPOLICY = 4 THEN 'Period Order Quantity'WHEN IV00102.ORDERPOLICY = 5 THEN 'Order Point'WHEN IV00102.ORDERPOLICY = 6 THEN 'Manually Planned'END as [Order Policy],IV00102.FXDORDRQTY AS [Fixed Order Qty], IV00102.ORDRPNTQTY as [Order Point Qty], IV00102.ORDRUPTOLVL AS [Order Up To Level],0 as [Recent Consumption],COALESCE(SUM(SOP30300.XTNDPRCE), 0)/4 as [Average Consumption],IV00102.BUYERID as [Buyer ID], IV00102.PLANNERID as [Planner ID],IV00101.USCATVLS_5 AS [PRODUCT LINE], IV00101.ITMCLSCD as [Item Class Code]from IV00102INNER JOIN IV00101 ON IV00102.ITEMNMBR = IV00101.ITEMNMBRINNER JOIN IV40201 ON IV00101.UOMSCHDL = IV40201.UOMSCHDLLEFT OUTER JOIN SOP30300 ON IV00102.ITEMNMBR = SOP30300.ITEMNMBR INNER JOIN SOP30200 ON SOP30300.SOPTYPE = SOP30200.SOPTYPE AND SOP30300.SOPNUMBE = SOP30200.SOPNUMBEwhere IV00102.LOCNCODE = '70' and IV00102.ORDERPOLICY IN (2, 3, 4, 5, 6) and LEFT(IV00102.ITEMNMBR, 3) <> '70-' and SOP30200.DOCDATE > DATEADD(m,-12,getdate())GROUP BY IV00102.ITEMNMBR, IV00101.ITEMDESC, IV00102.LOCNCODE, IV40201.BASEUOFM, IV00102.QTYONHND, IV00102.QTYONORD, IV00102.ATYALLOC, IV00102.REPLENISHMENTMETHOD, IV00102.ORDERPOLICY, IV00102.FXDORDRQTY,IV00102.ORDRPNTQTY, IV00102.ORDRUPTOLVL, IV00102.BUYERID, IV00102.PLANNERID, IV00101.USCATVLS_5, IV00101.ITMCLSCDUNION--70; not inventory adjustments (sales); 3 months (Recent Consumption)select IV00102.ITEMNMBR AS [Item Number], IV00101.ITEMDESC as [Item Description], IV00102.LOCNCODE as [Location Code], IV40201.BASEUOFM as [Base U of M], IV00102.QTYONHND as [QTY On Hand], IV00102.QTYONORD as [QTY On Order],IV00102.QTYONHND - IV00102.ATYALLOC as [QTY Available],CASE WHEN IV00102.REPLENISHMENTMETHOD = 0 THEN ''WHEN IV00102.REPLENISHMENTMETHOD = 1 THEN 'Make'WHEN IV00102.REPLENISHMENTMETHOD = 2 THEN 'Buy'WHEN IV00102.REPLENISHMENTMETHOD = 3 THEN 'Make or Buy' END as [Replenishment Method], CASE WHEN IV00102.ORDERPOLICY = 1 THEN 'Not Planned'WHEN IV00102.ORDERPOLICY = 2 THEN 'Lot for Lot'WHEN IV00102.ORDERPOLICY = 3 THEN 'Fixed Order Quantity'WHEN IV00102.ORDERPOLICY = 4 THEN 'Period Order Quantity'WHEN IV00102.ORDERPOLICY = 5 THEN 'Order Point'WHEN IV00102.ORDERPOLICY = 6 THEN 'Manually Planned'END as [Order Policy],IV00102.FXDORDRQTY AS [Fixed Order Qty], IV00102.ORDRPNTQTY as [Order Point Qty], IV00102.ORDRUPTOLVL AS [Order Up To Level],COALESCE(SUM(SOP30300.XTNDPRCE), 0) as [Recent Consumption],0 as [Average Consumption],IV00102.BUYERID as [Buyer ID], IV00102.PLANNERID as [Planner ID],IV00101.USCATVLS_5 AS [PRODUCT LINE], IV00101.ITMCLSCD as [Item Class Code]from IV00102INNER JOIN IV00101 ON IV00102.ITEMNMBR = IV00101.ITEMNMBRINNER JOIN IV40201 ON IV00101.UOMSCHDL = IV40201.UOMSCHDLLEFT OUTER JOIN SOP30300 ON IV00102.ITEMNMBR = SOP30300.ITEMNMBR INNER JOIN SOP30200 ON SOP30300.SOPTYPE = SOP30200.SOPTYPE AND SOP30300.SOPNUMBE = SOP30200.SOPNUMBEwhere IV00102.LOCNCODE = '70' and IV00102.ORDERPOLICY IN (2, 3, 4, 5, 6) and LEFT(IV00102.ITEMNMBR, 3) = '70-' and SOP30200.DOCDATE > DATEADD(m,-3,getdate())GROUP BY IV00102.ITEMNMBR, IV00101.ITEMDESC, IV00102.LOCNCODE, IV40201.BASEUOFM, IV00102.QTYONHND, IV00102.QTYONORD, IV00102.ATYALLOC, IV00102.REPLENISHMENTMETHOD, IV00102.ORDERPOLICY, IV00102.FXDORDRQTY,IV00102.ORDRPNTQTY, IV00102.ORDRUPTOLVL, IV00102.BUYERID, IV00102.PLANNERID, IV00101.USCATVLS_5, IV00101.ITMCLSCDUNION--70; not inventory adjustments (sales); 12 months/4 (Average Consumption)select IV00102.ITEMNMBR AS [Item Number], IV00101.ITEMDESC as [Item Description], IV00102.LOCNCODE as [Location Code], IV40201.BASEUOFM as [Base U of M], IV00102.QTYONHND as [QTY On Hand], IV00102.QTYONORD as [QTY On Order],IV00102.QTYONHND - IV00102.ATYALLOC as [QTY Available],CASE WHEN IV00102.REPLENISHMENTMETHOD = 0 THEN ''WHEN IV00102.REPLENISHMENTMETHOD = 1 THEN 'Make'WHEN IV00102.REPLENISHMENTMETHOD = 2 THEN 'Buy'WHEN IV00102.REPLENISHMENTMETHOD = 3 THEN 'Make or Buy' END as [Replenishment Method], CASE WHEN IV00102.ORDERPOLICY = 1 THEN 'Not Planned'WHEN IV00102.ORDERPOLICY = 2 THEN 'Lot for Lot'WHEN IV00102.ORDERPOLICY = 3 THEN 'Fixed Order Quantity'WHEN IV00102.ORDERPOLICY = 4 THEN 'Period Order Quantity'WHEN IV00102.ORDERPOLICY = 5 THEN 'Order Point'WHEN IV00102.ORDERPOLICY = 6 THEN 'Manually Planned'END as [Order Policy],IV00102.FXDORDRQTY AS [Fixed Order Qty], IV00102.ORDRPNTQTY as [Order Point Qty], IV00102.ORDRUPTOLVL AS [Order Up To Level],0 as [Recent Consumption],COALESCE(SUM(SOP30300.XTNDPRCE), 0)/4 as [Average Consumption],IV00102.BUYERID as [Buyer ID], IV00102.PLANNERID as [Planner ID],IV00101.USCATVLS_5 AS [PRODUCT LINE], IV00101.ITMCLSCD as [Item Class Code]from IV00102INNER JOIN IV00101 ON IV00102.ITEMNMBR = IV00101.ITEMNMBRINNER JOIN IV40201 ON IV00101.UOMSCHDL = IV40201.UOMSCHDLLEFT OUTER JOIN SOP30300 ON IV00102.ITEMNMBR = SOP30300.ITEMNMBR INNER JOIN SOP30200 ON SOP30300.SOPTYPE = SOP30200.SOPTYPE AND SOP30300.SOPNUMBE = SOP30200.SOPNUMBEwhere IV00102.LOCNCODE = '70' and IV00102.ORDERPOLICY IN (2, 3, 4, 5, 6) and LEFT(IV00102.ITEMNMBR, 3) = '70-' and SOP30200.DOCDATE > DATEADD(m,-12,getdate())GROUP BY IV00102.ITEMNMBR, IV00101.ITEMDESC, IV00102.LOCNCODE, IV40201.BASEUOFM, IV00102.QTYONHND, IV00102.QTYONORD, IV00102.ATYALLOC, IV00102.REPLENISHMENTMETHOD, IV00102.ORDERPOLICY, IV00102.FXDORDRQTY,IV00102.ORDRPNTQTY, IV00102.ORDRUPTOLVL, IV00102.BUYERID, IV00102.PLANNERID, IV00101.USCATVLS_5, IV00101.ITMCLSCD)as DATA) as REORDERPOINTANALYSISGROUP BY [Item Number], [Item Description], [Location Code], [Base U of M], [QTY On Hand], [QTY On Order], [QTY Available], [Replenishment Method], [Order Policy], [Fixed Order Qty],[Order Point Qty], [Order Up To Level], [Buyer ID], [Planner ID], [PRODUCT LINE], [Item Class Code]as REORDERPOINTANALYSIS2
Column name as col1 --- can use used in SELECT list to change the output . You cannot use that in Group by
Select [Item Number], [Item Description], [Location Code], [Base U of M], [QTY On Hand], [QTY On Order], [QTY Available], [Replenishment Method], [Order Policy], [Fixed Order Qty],[Order Point Qty], [Order Up To Level], SUM([Recent Consumption]), SUM([Average Consumption]), [Buyer ID], [Planner ID], [PRODUCT LINE], [Item Class Code] from (select TOP 100 PERCENT * from (--not 70; inventory adjustments; 3 months (Recent Consumption)select IV00102.ITEMNMBR AS [Item Number], IV00101.ITEMDESC as [Item Description], IV00102.LOCNCODE as [Location Code], IV40201.BASEUOFM as [Base U of M], IV00102.QTYONHND as [QTY On Hand], IV00102.QTYONORD as [QTY On Order],IV00102.QTYONHND - IV00102.ATYALLOC as [QTY Available],CASE WHEN IV00102.REPLENISHMENTMETHOD = 0 THEN ''WHEN IV00102.REPLENISHMENTMETHOD = 1 THEN 'Make'WHEN IV00102.REPLENISHMENTMETHOD = 2 THEN 'Buy'WHEN IV00102.REPLENISHMENTMETHOD = 3 THEN 'Make or Buy' END as [Replenishment Method], CASE WHEN IV00102.ORDERPOLICY = 1 THEN 'Not Planned'WHEN IV00102.ORDERPOLICY = 2 THEN 'Lot for Lot'WHEN IV00102.ORDERPOLICY = 3 THEN 'Fixed Order Quantity'WHEN IV00102.ORDERPOLICY = 4 THEN 'Period Order Quantity'WHEN IV00102.ORDERPOLICY = 5 THEN 'Order Point'WHEN IV00102.ORDERPOLICY = 6 THEN 'Manually Planned'END as [Order Policy],IV00102.FXDORDRQTY AS [Fixed Order Qty], IV00102.ORDRPNTQTY as [Order Point Qty], IV00102.ORDRUPTOLVL AS [Order Up To Level],COALESCE(SUM(IV30300.EXTDCOST), 0) as [Recent Consumption],0 as [Average Consumption],IV00102.BUYERID as [Buyer ID], IV00102.PLANNERID as [Planner ID],IV00101.USCATVLS_5 AS [PRODUCT LINE], IV00101.ITMCLSCD as [Item Class Code]from IV00102INNER JOIN IV00101 ON IV00102.ITEMNMBR = IV00101.ITEMNMBRINNER JOIN IV40201 ON IV00101.UOMSCHDL = IV40201.UOMSCHDLLEFT OUTER JOIN IV30300 ON IV00102.ITEMNMBR = IV30300.ITEMNMBR where IV00102.LOCNCODE = '70' and IV00102.ORDERPOLICY IN (2, 3, 4, 5, 6) and LEFT(IV00102.ITEMNMBR, 3) <> '70-' and IV30300.DOCDATE > DATEADD(m,-3,getdate())GROUP BY IV00102.ITEMNMBR, IV00101.ITEMDESC, IV00102.LOCNCODE, IV40201.BASEUOFM, IV00102.QTYONHND, IV00102.QTYONORD, IV00102.ATYALLOC, IV00102.REPLENISHMENTMETHOD, IV00102.ORDERPOLICY, IV00102.FXDORDRQTY,IV00102.ORDRPNTQTY, IV00102.ORDRUPTOLVL, IV00102.BUYERID, IV00102.PLANNERID, IV00101.USCATVLS_5, IV00101.ITMCLSCDUNION--not 70; inventory adjustments; 12 months/4 (Average Consumption)select IV00102.ITEMNMBR AS [Item Number], IV00101.ITEMDESC as [Item Description], IV00102.LOCNCODE as [Location Code], IV40201.BASEUOFM as [Base U of M], IV00102.QTYONHND as [QTY On Hand], IV00102.QTYONORD as [QTY On Order],IV00102.QTYONHND - IV00102.ATYALLOC as [QTY Available],CASE WHEN IV00102.REPLENISHMENTMETHOD = 0 THEN ''WHEN IV00102.REPLENISHMENTMETHOD = 1 THEN 'Make'WHEN IV00102.REPLENISHMENTMETHOD = 2 THEN 'Buy'WHEN IV00102.REPLENISHMENTMETHOD = 3 THEN 'Make or Buy' END as [Replenishment Method], CASE WHEN IV00102.ORDERPOLICY = 1 THEN 'Not Planned'WHEN IV00102.ORDERPOLICY = 2 THEN 'Lot for Lot'WHEN IV00102.ORDERPOLICY = 3 THEN 'Fixed Order Quantity'WHEN IV00102.ORDERPOLICY = 4 THEN 'Period Order Quantity'WHEN IV00102.ORDERPOLICY = 5 THEN 'Order Point'WHEN IV00102.ORDERPOLICY = 6 THEN 'Manually Planned'END as [Order Policy],IV00102.FXDORDRQTY AS [Fixed Order Qty], IV00102.ORDRPNTQTY as [Order Point Qty], IV00102.ORDRUPTOLVL AS [Order Up To Level],0 as [Recent Consumption],COALESCE(SUM(IV30300.EXTDCOST), 0)/4 as [Average Consumption],IV00102.BUYERID as [Buyer ID], IV00102.PLANNERID as [Planner ID],IV00101.USCATVLS_5 AS [PRODUCT LINE], IV00101.ITMCLSCD as [Item Class Code]from IV00102INNER JOIN IV00101 ON IV00102.ITEMNMBR = IV00101.ITEMNMBRINNER JOIN IV40201 ON IV00101.UOMSCHDL = IV40201.UOMSCHDLLEFT OUTER JOIN IV30300 ON IV00102.ITEMNMBR = IV30300.ITEMNMBR where IV00102.LOCNCODE = '70' and IV00102.ORDERPOLICY IN (2, 3, 4, 5, 6) and LEFT(IV00102.ITEMNMBR, 3) <> '70-' and IV30300.DOCDATE > DATEADD(m,-12,getdate())GROUP BY IV00102.ITEMNMBR, IV00101.ITEMDESC, IV00102.LOCNCODE, IV40201.BASEUOFM, IV00102.QTYONHND, IV00102.QTYONORD, IV00102.ATYALLOC, IV00102.REPLENISHMENTMETHOD, IV00102.ORDERPOLICY, IV00102.FXDORDRQTY,IV00102.ORDRPNTQTY, IV00102.ORDRUPTOLVL, IV00102.BUYERID, IV00102.PLANNERID, IV00101.USCATVLS_5, IV00101.ITMCLSCDUNION--70; inventory adjustments; 3 months (Recent Consumption)select IV00102.ITEMNMBR AS [Item Number], IV00101.ITEMDESC as [Item Description], IV00102.LOCNCODE as [Location Code], IV40201.BASEUOFM as [Base U of M], IV00102.QTYONHND as [QTY On Hand], IV00102.QTYONORD as [QTY On Order],IV00102.QTYONHND - IV00102.ATYALLOC as [QTY Available],CASE WHEN IV00102.REPLENISHMENTMETHOD = 0 THEN ''WHEN IV00102.REPLENISHMENTMETHOD = 1 THEN 'Make'WHEN IV00102.REPLENISHMENTMETHOD = 2 THEN 'Buy'WHEN IV00102.REPLENISHMENTMETHOD = 3 THEN 'Make or Buy' END as [Replenishment Method], CASE WHEN IV00102.ORDERPOLICY = 1 THEN 'Not Planned'WHEN IV00102.ORDERPOLICY = 2 THEN 'Lot for Lot'WHEN IV00102.ORDERPOLICY = 3 THEN 'Fixed Order Quantity'WHEN IV00102.ORDERPOLICY = 4 THEN 'Period Order Quantity'WHEN IV00102.ORDERPOLICY = 5 THEN 'Order Point'WHEN IV00102.ORDERPOLICY = 6 THEN 'Manually Planned'END as [Order Policy],IV00102.FXDORDRQTY AS [Fixed Order Qty], IV00102.ORDRPNTQTY as [Order Point Qty], IV00102.ORDRUPTOLVL AS [Order Up To Level],COALESCE(SUM(IV30300.EXTDCOST), 0) as [Recent Consumption],0 as [Average Consumption],IV00102.BUYERID as [Buyer ID], IV00102.PLANNERID as [Planner ID],IV00101.USCATVLS_5 AS [PRODUCT LINE], IV00101.ITMCLSCD as [Item Class Code]from IV00102INNER JOIN IV00101 ON IV00102.ITEMNMBR = IV00101.ITEMNMBRINNER JOIN IV40201 ON IV00101.UOMSCHDL = IV40201.UOMSCHDLLEFT OUTER JOIN IV30300 ON IV00102.ITEMNMBR = IV30300.ITEMNMBR where IV00102.LOCNCODE = '70' and IV00102.ORDERPOLICY IN (2, 3, 4, 5, 6) and LEFT(IV00102.ITEMNMBR, 3) = '70-' and IV30300.DOCDATE > DATEADD(m,-3,getdate())GROUP BY IV00102.ITEMNMBR, IV00101.ITEMDESC, IV00102.LOCNCODE, IV40201.BASEUOFM, IV00102.QTYONHND, IV00102.QTYONORD, IV00102.ATYALLOC, IV00102.REPLENISHMENTMETHOD, IV00102.ORDERPOLICY, IV00102.FXDORDRQTY,IV00102.ORDRPNTQTY, IV00102.ORDRUPTOLVL, IV00102.BUYERID, IV00102.PLANNERID, IV00101.USCATVLS_5, IV00101.ITMCLSCDUNION--70; inventory adjustments; 12 months/4 (Average Consumption)select IV00102.ITEMNMBR AS [Item Number], IV00101.ITEMDESC as [Item Description], IV00102.LOCNCODE as [Location Code], IV40201.BASEUOFM as [Base U of M], IV00102.QTYONHND as [QTY On Hand], IV00102.QTYONORD as [QTY On Order],IV00102.QTYONHND - IV00102.ATYALLOC as [QTY Available],CASE WHEN IV00102.REPLENISHMENTMETHOD = 0 THEN ''WHEN IV00102.REPLENISHMENTMETHOD = 1 THEN 'Make'WHEN IV00102.REPLENISHMENTMETHOD = 2 THEN 'Buy'WHEN IV00102.REPLENISHMENTMETHOD = 3 THEN 'Make or Buy' END as [Replenishment Method], CASE WHEN IV00102.ORDERPOLICY = 1 THEN 'Not Planned'WHEN IV00102.ORDERPOLICY = 2 THEN 'Lot for Lot'WHEN IV00102.ORDERPOLICY = 3 THEN 'Fixed Order Quantity'WHEN IV00102.ORDERPOLICY = 4 THEN 'Period Order Quantity'WHEN IV00102.ORDERPOLICY = 5 THEN 'Order Point'WHEN IV00102.ORDERPOLICY = 6 THEN 'Manually Planned'END as [Order Policy],IV00102.FXDORDRQTY AS [Fixed Order Qty], IV00102.ORDRPNTQTY as [Order Point Qty], IV00102.ORDRUPTOLVL AS [Order Up To Level],0 as [Recent Consumption],COALESCE(SUM(IV30300.EXTDCOST), 0)/4 as [Average Consumption],IV00102.BUYERID as [Buyer ID], IV00102.PLANNERID as [Planner ID],IV00101.USCATVLS_5 AS [PRODUCT LINE], IV00101.ITMCLSCD as [Item Class Code]from IV00102INNER JOIN IV00101 ON IV00102.ITEMNMBR = IV00101.ITEMNMBRINNER JOIN IV40201 ON IV00101.UOMSCHDL = IV40201.UOMSCHDLLEFT OUTER JOIN IV30300 ON IV00102.ITEMNMBR = IV30300.ITEMNMBR where IV00102.LOCNCODE = '70' and IV00102.ORDERPOLICY IN (2, 3, 4, 5, 6) and LEFT(IV00102.ITEMNMBR, 3) = '70-' and IV30300.DOCDATE > DATEADD(m,-12,getdate())GROUP BY IV00102.ITEMNMBR, IV00101.ITEMDESC, IV00102.LOCNCODE, IV40201.BASEUOFM, IV00102.QTYONHND, IV00102.QTYONORD, IV00102.ATYALLOC, IV00102.REPLENISHMENTMETHOD, IV00102.ORDERPOLICY, IV00102.FXDORDRQTY,IV00102.ORDRPNTQTY, IV00102.ORDRUPTOLVL, IV00102.BUYERID, IV00102.PLANNERID, IV00101.USCATVLS_5, IV00101.ITMCLSCDUNION--not 70; not inventory adjustments (sales); 3 months (Recent Consumption)select IV00102.ITEMNMBR AS [Item Number], IV00101.ITEMDESC as [Item Description], IV00102.LOCNCODE as [Location Code], IV40201.BASEUOFM as [Base U of M], IV00102.QTYONHND as [QTY On Hand], IV00102.QTYONORD as [QTY On Order],IV00102.QTYONHND - IV00102.ATYALLOC as [QTY Available],CASE WHEN IV00102.REPLENISHMENTMETHOD = 0 THEN ''WHEN IV00102.REPLENISHMENTMETHOD = 1 THEN 'Make'WHEN IV00102.REPLENISHMENTMETHOD = 2 THEN 'Buy'WHEN IV00102.REPLENISHMENTMETHOD = 3 THEN 'Make or Buy' END as [Replenishment Method], CASE WHEN IV00102.ORDERPOLICY = 1 THEN 'Not Planned'WHEN IV00102.ORDERPOLICY = 2 THEN 'Lot for Lot'WHEN IV00102.ORDERPOLICY = 3 THEN 'Fixed Order Quantity'WHEN IV00102.ORDERPOLICY = 4 THEN 'Period Order Quantity'WHEN IV00102.ORDERPOLICY = 5 THEN 'Order Point'WHEN IV00102.ORDERPOLICY = 6 THEN 'Manually Planned'END as [Order Policy],IV00102.FXDORDRQTY AS [Fixed Order Qty], IV00102.ORDRPNTQTY as [Order Point Qty], IV00102.ORDRUPTOLVL AS [Order Up To Level],COALESCE(SUM(SOP30300.XTNDPRCE), 0) as [Recent Consumption],0 as [Average Consumption],IV00102.BUYERID as [Buyer ID], IV00102.PLANNERID as [Planner ID],IV00101.USCATVLS_5 AS [PRODUCT LINE], IV00101.ITMCLSCD as [Item Class Code]from IV00102INNER JOIN IV00101 ON IV00102.ITEMNMBR = IV00101.ITEMNMBRINNER JOIN IV40201 ON IV00101.UOMSCHDL = IV40201.UOMSCHDLLEFT OUTER JOIN SOP30300 ON IV00102.ITEMNMBR = SOP30300.ITEMNMBR INNER JOIN SOP30200 ON SOP30300.SOPTYPE = SOP30200.SOPTYPE AND SOP30300.SOPNUMBE = SOP30200.SOPNUMBEwhere IV00102.LOCNCODE = '70' and IV00102.ORDERPOLICY IN (2, 3, 4, 5, 6) and LEFT(IV00102.ITEMNMBR, 3) <> '70-' and SOP30200.DOCDATE > DATEADD(m,-3,getdate())GROUP BY IV00102.ITEMNMBR, IV00101.ITEMDESC, IV00102.LOCNCODE, IV40201.BASEUOFM, IV00102.QTYONHND, IV00102.QTYONORD, IV00102.ATYALLOC, IV00102.REPLENISHMENTMETHOD, IV00102.ORDERPOLICY, IV00102.FXDORDRQTY,IV00102.ORDRPNTQTY, IV00102.ORDRUPTOLVL, IV00102.BUYERID, IV00102.PLANNERID, IV00101.USCATVLS_5, IV00101.ITMCLSCDUNION--not 70; not inventory adjustments (sales); 12 months/4 (Average Consumption)select IV00102.ITEMNMBR AS [Item Number], IV00101.ITEMDESC as [Item Description], IV00102.LOCNCODE as [Location Code], IV40201.BASEUOFM as [Base U of M], IV00102.QTYONHND as [QTY On Hand], IV00102.QTYONORD as [QTY On Order],IV00102.QTYONHND - IV00102.ATYALLOC as [QTY Available],CASE WHEN IV00102.REPLENISHMENTMETHOD = 0 THEN ''WHEN IV00102.REPLENISHMENTMETHOD = 1 THEN 'Make'WHEN IV00102.REPLENISHMENTMETHOD = 2 THEN 'Buy'WHEN IV00102.REPLENISHMENTMETHOD = 3 THEN 'Make or Buy' END as [Replenishment Method], CASE WHEN IV00102.ORDERPOLICY = 1 THEN 'Not Planned'WHEN IV00102.ORDERPOLICY = 2 THEN 'Lot for Lot'WHEN IV00102.ORDERPOLICY = 3 THEN 'Fixed Order Quantity'WHEN IV00102.ORDERPOLICY = 4 THEN 'Period Order Quantity'WHEN IV00102.ORDERPOLICY = 5 THEN 'Order Point'WHEN IV00102.ORDERPOLICY = 6 THEN 'Manually Planned'END as [Order Policy],IV00102.FXDORDRQTY AS [Fixed Order Qty], IV00102.ORDRPNTQTY as [Order Point Qty], IV00102.ORDRUPTOLVL AS [Order Up To Level],0 as [Recent Consumption],COALESCE(SUM(SOP30300.XTNDPRCE), 0)/4 as [Average Consumption],IV00102.BUYERID as [Buyer ID], IV00102.PLANNERID as [Planner ID],IV00101.USCATVLS_5 AS [PRODUCT LINE], IV00101.ITMCLSCD as [Item Class Code]from IV00102INNER JOIN IV00101 ON IV00102.ITEMNMBR = IV00101.ITEMNMBRINNER JOIN IV40201 ON IV00101.UOMSCHDL = IV40201.UOMSCHDLLEFT OUTER JOIN SOP30300 ON IV00102.ITEMNMBR = SOP30300.ITEMNMBR INNER JOIN SOP30200 ON SOP30300.SOPTYPE = SOP30200.SOPTYPE AND SOP30300.SOPNUMBE = SOP30200.SOPNUMBEwhere IV00102.LOCNCODE = '70' and IV00102.ORDERPOLICY IN (2, 3, 4, 5, 6) and LEFT(IV00102.ITEMNMBR, 3) <> '70-' and SOP30200.DOCDATE > DATEADD(m,-12,getdate())GROUP BY IV00102.ITEMNMBR, IV00101.ITEMDESC, IV00102.LOCNCODE, IV40201.BASEUOFM, IV00102.QTYONHND, IV00102.QTYONORD, IV00102.ATYALLOC, IV00102.REPLENISHMENTMETHOD, IV00102.ORDERPOLICY, IV00102.FXDORDRQTY,IV00102.ORDRPNTQTY, IV00102.ORDRUPTOLVL, IV00102.BUYERID, IV00102.PLANNERID, IV00101.USCATVLS_5, IV00101.ITMCLSCDUNION--70; not inventory adjustments (sales); 3 months (Recent Consumption)select IV00102.ITEMNMBR AS [Item Number], IV00101.ITEMDESC as [Item Description], IV00102.LOCNCODE as [Location Code], IV40201.BASEUOFM as [Base U of M], IV00102.QTYONHND as [QTY On Hand], IV00102.QTYONORD as [QTY On Order],IV00102.QTYONHND - IV00102.ATYALLOC as [QTY Available],CASE WHEN IV00102.REPLENISHMENTMETHOD = 0 THEN ''WHEN IV00102.REPLENISHMENTMETHOD = 1 THEN 'Make'WHEN IV00102.REPLENISHMENTMETHOD = 2 THEN 'Buy'WHEN IV00102.REPLENISHMENTMETHOD = 3 THEN 'Make or Buy' END as [Replenishment Method], CASE WHEN IV00102.ORDERPOLICY = 1 THEN 'Not Planned'WHEN IV00102.ORDERPOLICY = 2 THEN 'Lot for Lot'WHEN IV00102.ORDERPOLICY = 3 THEN 'Fixed Order Quantity'WHEN IV00102.ORDERPOLICY = 4 THEN 'Period Order Quantity'WHEN IV00102.ORDERPOLICY = 5 THEN 'Order Point'WHEN IV00102.ORDERPOLICY = 6 THEN 'Manually Planned'END as [Order Policy],IV00102.FXDORDRQTY AS [Fixed Order Qty], IV00102.ORDRPNTQTY as [Order Point Qty], IV00102.ORDRUPTOLVL AS [Order Up To Level],COALESCE(SUM(SOP30300.XTNDPRCE), 0) as [Recent Consumption],0 as [Average Consumption],IV00102.BUYERID as [Buyer ID], IV00102.PLANNERID as [Planner ID],IV00101.USCATVLS_5 AS [PRODUCT LINE], IV00101.ITMCLSCD as [Item Class Code]from IV00102INNER JOIN IV00101 ON IV00102.ITEMNMBR = IV00101.ITEMNMBRINNER JOIN IV40201 ON IV00101.UOMSCHDL = IV40201.UOMSCHDLLEFT OUTER JOIN SOP30300 ON IV00102.ITEMNMBR = SOP30300.ITEMNMBR INNER JOIN SOP30200 ON SOP30300.SOPTYPE = SOP30200.SOPTYPE AND SOP30300.SOPNUMBE = SOP30200.SOPNUMBEwhere IV00102.LOCNCODE = '70' and IV00102.ORDERPOLICY IN (2, 3, 4, 5, 6) and LEFT(IV00102.ITEMNMBR, 3) = '70-' and SOP30200.DOCDATE > DATEADD(m,-3,getdate())GROUP BY IV00102.ITEMNMBR, IV00101.ITEMDESC, IV00102.LOCNCODE, IV40201.BASEUOFM, IV00102.QTYONHND, IV00102.QTYONORD, IV00102.ATYALLOC, IV00102.REPLENISHMENTMETHOD, IV00102.ORDERPOLICY, IV00102.FXDORDRQTY,IV00102.ORDRPNTQTY, IV00102.ORDRUPTOLVL, IV00102.BUYERID, IV00102.PLANNERID, IV00101.USCATVLS_5, IV00101.ITMCLSCDUNION--70; not inventory adjustments (sales); 12 months/4 (Average Consumption)select IV00102.ITEMNMBR AS [Item Number], IV00101.ITEMDESC as [Item Description], IV00102.LOCNCODE as [Location Code], IV40201.BASEUOFM as [Base U of M], IV00102.QTYONHND as [QTY On Hand], IV00102.QTYONORD as [QTY On Order],IV00102.QTYONHND - IV00102.ATYALLOC as [QTY Available],CASE WHEN IV00102.REPLENISHMENTMETHOD = 0 THEN ''WHEN IV00102.REPLENISHMENTMETHOD = 1 THEN 'Make'WHEN IV00102.REPLENISHMENTMETHOD = 2 THEN 'Buy'WHEN IV00102.REPLENISHMENTMETHOD = 3 THEN 'Make or Buy' END as [Replenishment Method], CASE WHEN IV00102.ORDERPOLICY = 1 THEN 'Not Planned'WHEN IV00102.ORDERPOLICY = 2 THEN 'Lot for Lot'WHEN IV00102.ORDERPOLICY = 3 THEN 'Fixed Order Quantity'WHEN IV00102.ORDERPOLICY = 4 THEN 'Period Order Quantity'WHEN IV00102.ORDERPOLICY = 5 THEN 'Order Point'WHEN IV00102.ORDERPOLICY = 6 THEN 'Manually Planned'END as [Order Policy],IV00102.FXDORDRQTY AS [Fixed Order Qty], IV00102.ORDRPNTQTY as [Order Point Qty], IV00102.ORDRUPTOLVL AS [Order Up To Level],0 as [Recent Consumption],COALESCE(SUM(SOP30300.XTNDPRCE), 0)/4 as [Average Consumption],IV00102.BUYERID as [Buyer ID], IV00102.PLANNERID as [Planner ID],IV00101.USCATVLS_5 AS [PRODUCT LINE], IV00101.ITMCLSCD as [Item Class Code]from IV00102INNER JOIN IV00101 ON IV00102.ITEMNMBR = IV00101.ITEMNMBRINNER JOIN IV40201 ON IV00101.UOMSCHDL = IV40201.UOMSCHDLLEFT OUTER JOIN SOP30300 ON IV00102.ITEMNMBR = SOP30300.ITEMNMBR INNER JOIN SOP30200 ON SOP30300.SOPTYPE = SOP30200.SOPTYPE AND SOP30300.SOPNUMBE = SOP30200.SOPNUMBEwhere IV00102.LOCNCODE = '70' and IV00102.ORDERPOLICY IN (2, 3, 4, 5, 6) and LEFT(IV00102.ITEMNMBR, 3) = '70-' and SOP30200.DOCDATE > DATEADD(m,-12,getdate())GROUP BY IV00102.ITEMNMBR, IV00101.ITEMDESC, IV00102.LOCNCODE, IV40201.BASEUOFM, IV00102.QTYONHND, IV00102.QTYONORD, IV00102.ATYALLOC, IV00102.REPLENISHMENTMETHOD, IV00102.ORDERPOLICY, IV00102.FXDORDRQTY,IV00102.ORDRPNTQTY, IV00102.ORDRUPTOLVL, IV00102.BUYERID, IV00102.PLANNERID, IV00101.USCATVLS_5, IV00101.ITMCLSCD)as DATA) as REORDERPOINTANALYSISGROUP BY [Item Number], [Item Description], [Location Code], [Base U of M], [QTY On Hand], [QTY On Order], [QTY Available], [Replenishment Method], [Order Policy], [Fixed Order Qty],[Order Point Qty], [Order Up To Level], [Buyer ID], [Planner ID], [PRODUCT LINE], [Item Class Code]
Column name as col1 --- can use used in SELECT list to change the output . You cannot use that in Group by
Open in new window