T-SQL: Incorrect Syntax Near the Keyword 'as'
Hello:
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
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 IV00102
INNER JOIN IV00101
ON IV00102.ITEMNMBR = IV00101.ITEMNMBR
INNER JOIN IV40201
ON IV00101.UOMSCHDL = IV40201.UOMSCHDL
LEFT 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.ITMCLSCD
UNION
--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 IV00102
INNER JOIN IV00101
ON IV00102.ITEMNMBR = IV00101.ITEMNMBR
INNER JOIN IV40201
ON IV00101.UOMSCHDL = IV40201.UOMSCHDL
LEFT 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.ITMCLSCD
UNION
--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 IV00102
INNER JOIN IV00101
ON IV00102.ITEMNMBR = IV00101.ITEMNMBR
INNER JOIN IV40201
ON IV00101.UOMSCHDL = IV40201.UOMSCHDL
LEFT 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.ITMCLSCD
UNION
--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 IV00102
INNER JOIN IV00101
ON IV00102.ITEMNMBR = IV00101.ITEMNMBR
INNER JOIN IV40201
ON IV00101.UOMSCHDL = IV40201.UOMSCHDL
LEFT 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.ITMCLSCD
UNION
--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 IV00102
INNER JOIN IV00101
ON IV00102.ITEMNMBR = IV00101.ITEMNMBR
INNER JOIN IV40201
ON IV00101.UOMSCHDL = IV40201.UOMSCHDL
LEFT OUTER JOIN SOP30300
ON IV00102.ITEMNMBR = SOP30300.ITEMNMBR
INNER JOIN SOP30200
ON SOP30300.SOPTYPE = SOP30200.SOPTYPE AND SOP30300.SOPNUMBE = SOP30200.SOPNUMBE
where 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.ITMCLSCD
UNION
--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 IV00102
INNER JOIN IV00101
ON IV00102.ITEMNMBR = IV00101.ITEMNMBR
INNER JOIN IV40201
ON IV00101.UOMSCHDL = IV40201.UOMSCHDL
LEFT OUTER JOIN SOP30300
ON IV00102.ITEMNMBR = SOP30300.ITEMNMBR
INNER JOIN SOP30200
ON SOP30300.SOPTYPE = SOP30200.SOPTYPE AND SOP30300.SOPNUMBE = SOP30200.SOPNUMBE
where 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
UNION
--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 IV00102
INNER JOIN IV00101
ON IV00102.ITEMNMBR = IV00101.ITEMNMBR
INNER JOIN IV40201
ON IV00101.UOMSCHDL = IV40201.UOMSCHDL
LEFT OUTER JOIN SOP30300
ON IV00102.ITEMNMBR = SOP30300.ITEMNMBR
INNER JOIN SOP30200
ON SOP30300.SOPTYPE = SOP30200.SOPTYPE AND SOP30300.SOPNUMBE = SOP30200.SOPNUMBE
where 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.ITMCLSCD
UNION
--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 IV00102
INNER JOIN IV00101
ON IV00102.ITEMNMBR = IV00101.ITEMNMBR
INNER JOIN IV40201
ON IV00101.UOMSCHDL = IV40201.UOMSCHDL
LEFT OUTER JOIN SOP30300
ON IV00102.ITEMNMBR = SOP30300.ITEMNMBR
INNER JOIN SOP30200
ON SOP30300.SOPTYPE = SOP30200.SOPTYPE AND SOP30300.SOPNUMBE = SOP30200.SOPNUMBE
where 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 REORDERPOINTANALYSIS
GROUP 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
Who is Participating?
Please try this
Column name as col1 --- can use used in SELECT list to change the output . You cannot use that in Group by
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 IV00102
INNER JOIN IV00101
ON IV00102.ITEMNMBR = IV00101.ITEMNMBR
INNER JOIN IV40201
ON IV00101.UOMSCHDL = IV40201.UOMSCHDL
LEFT 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.ITMCLSCD
UNION
--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 IV00102
INNER JOIN IV00101
ON IV00102.ITEMNMBR = IV00101.ITEMNMBR
INNER JOIN IV40201
ON IV00101.UOMSCHDL = IV40201.UOMSCHDL
LEFT 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.ITMCLSCD
UNION
--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 IV00102
INNER JOIN IV00101
ON IV00102.ITEMNMBR = IV00101.ITEMNMBR
INNER JOIN IV40201
ON IV00101.UOMSCHDL = IV40201.UOMSCHDL
LEFT 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.ITMCLSCD
UNION
--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 IV00102
INNER JOIN IV00101
ON IV00102.ITEMNMBR = IV00101.ITEMNMBR
INNER JOIN IV40201
ON IV00101.UOMSCHDL = IV40201.UOMSCHDL
LEFT 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.ITMCLSCD
UNION
--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 IV00102
INNER JOIN IV00101
ON IV00102.ITEMNMBR = IV00101.ITEMNMBR
INNER JOIN IV40201
ON IV00101.UOMSCHDL = IV40201.UOMSCHDL
LEFT OUTER JOIN SOP30300
ON IV00102.ITEMNMBR = SOP30300.ITEMNMBR
INNER JOIN SOP30200
ON SOP30300.SOPTYPE = SOP30200.SOPTYPE AND SOP30300.SOPNUMBE = SOP30200.SOPNUMBE
where 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.ITMCLSCD
UNION
--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 IV00102
INNER JOIN IV00101
ON IV00102.ITEMNMBR = IV00101.ITEMNMBR
INNER JOIN IV40201
ON IV00101.UOMSCHDL = IV40201.UOMSCHDL
LEFT OUTER JOIN SOP30300
ON IV00102.ITEMNMBR = SOP30300.ITEMNMBR
INNER JOIN SOP30200
ON SOP30300.SOPTYPE = SOP30200.SOPTYPE AND SOP30300.SOPNUMBE = SOP30200.SOPNUMBE
where 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
UNION
--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 IV00102
INNER JOIN IV00101
ON IV00102.ITEMNMBR = IV00101.ITEMNMBR
INNER JOIN IV40201
ON IV00101.UOMSCHDL = IV40201.UOMSCHDL
LEFT OUTER JOIN SOP30300
ON IV00102.ITEMNMBR = SOP30300.ITEMNMBR
INNER JOIN SOP30200
ON SOP30300.SOPTYPE = SOP30200.SOPTYPE AND SOP30300.SOPNUMBE = SOP30200.SOPNUMBE
where 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.ITMCLSCD
UNION
--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 IV00102
INNER JOIN IV00101
ON IV00102.ITEMNMBR = IV00101.ITEMNMBR
INNER JOIN IV40201
ON IV00101.UOMSCHDL = IV40201.UOMSCHDL
LEFT OUTER JOIN SOP30300
ON IV00102.ITEMNMBR = SOP30300.ITEMNMBR
INNER JOIN SOP30200
ON SOP30300.SOPTYPE = SOP30200.SOPTYPE AND SOP30300.SOPNUMBE = SOP30200.SOPNUMBE
where 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 REORDERPOINTANALYSIS
GROUP 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]
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
All Courses
From novice to tech pro — start learning today.
using as keyword we change the name of the column in the select list , so in group by it is not allowed.