CREATE PROCEDURE SP_SLOWMOVING_PARTS
  @STARTDATE AS NVARCHAR(10)
, @ENDDATE AS NVARCHAR(10)
, @STARTID AS NVARCHAR(20)
, @ENDID AS NVARCHAR(20)

AS

/*----------------------------
CHECK TO MAKE SURE TABLE EXIST 
OR NOT
-----------------------------*/
IF EXISTS(SELECT NAME FROM tempdb.dbo.sysobjects
	WHERE NAME LIKE '#TEMP%')
	DROP TABLE #TEMP1
GO
	
IF EXISTS(SELECT NAME FROM tempdb.dbo.sysobjects
	WHERE NAME LIKE '#TEMP%')
	DROP TABLE #TEMP2
GO

/*-----------------------------
CREATE TABLE
------------------------------*/
CREATE TABLE #TEMP1(
	QTY DECIMAL(14,4),
	PART_ID VARCHAR(30),
	WAREHOUSE_ID VARCHAR(15),
	DESCRIPTION VARCHAR(80),
	WEIGHT DECIMAL(14,4),
	WEITHG_UM VARCHAR(15),
	QTY_ON_HAND DECIMAL(14,4)
	)

CREATE TABLE #TEMP2(
	ID VARCHAR(30),
	UNIT_MATERIAL_COST DECIMAL(15,6),
	UNIT_LABOR_COST DECIMAL(15,6),
	UNIT_BURDEN_COST DECIMAL(15,6),
	UNIT_SERVICE_COST DECIMAL(15,6),
	QTY_ON_HAND DECIMAL(14,4),
	TRANSACTION_DATE DATETIME
	)
/*-----------------------------
INSERT INTO TABLE
-----------------------------*/
BEGIN
INSERT INTO #TEMP1(
	QTY,
	PART_ID,
	WAREHOUSE_ID,
	DESCRIPTION,
	WEIGHT,
	WEITHG_UM,
	QTY_ON_HAND
	)

SELECT SUM(IT.QTY) AS QTY, IT.PART_ID,
  IT.WAREHOUSE_ID, P.DESCRIPTION, P.WEIGHT,
  P.WEIGHT_UM, SLOWPARTS.QTY_ON_HAND
FROM INVENTORY_TRANS IT INNER JOIN PART P ON
	P.ID = IT.PART_ID 

	LEFT OUTER JOIN
	(SELECT P.ID, P.DESCRIPTION, P.STOCK_UM, P.WEIGHT,
  		P.WEIGHT_UM, P.QTY_ON_HAND
	FROM 	PART P
	WHERE 	(P.ID BETWEEN @STARTID + '%' AND @ENDID + '%')
  		AND P.QTY_ON_HAND > 0) SLOWPARTS ON IT.PART_ID = SLOWPARTS.ID

WHERE ((part_id BETWEEN @STARTID + '%' AND @ENDID + '%')  
   AND type = 'I'
   AND class = 'R'
   AND transaction_date BETWEEN @STARTDATE AND @ENDDATE)
GROUP BY QTY, IT.PART_ID, IT.WAREHOUSE_ID,
   P.DESCRIPTION, P.WEIGHT, P.WEIGHT_UM, SLOWPARTS.QTY_ON_HAND
ORDER BY IT.PART_ID
END

BEGIN
INSERT INTO #TEMP2(
	ID,
	UNIT_MATERIAL_COST,
	UNIT_LABOR_COST,
	UNIT_BURDEN_COST,
	UNIT_SERVICE_COST,
	QTY_ON_HAND,
	TRANSACTION_DATE
	)

SELECT P.ID, P.UNIT_MATERIAL_COST, P.UNIT_LABOR_COST,
  	P.UNIT_BURDEN_COST, P.UNIT_SERVICE_COST, P.QTY_ON_HAND,
  	MAX(IT.TRANSACTION_DATE)
FROM PART P INNER JOIN INVENTORY_TRANS IT
	ON P.ID = IT.PART_ID
GROUP BY P.ID, P.QTY_ON_HAND, P.UNIT_MATERIAL_COST,
  	P.UNIT_LABOR_COST, P.UNIT_BURDEN_COST, P.UNIT_SERVICE_COST
ORDER BY P.ID
END

/*------------------------------------
SELECT THE RECORDS
------------------------------------*/

SELECT *
FROM #TEMP1 T1 LEFT OUTER JOIN #TEMP2 T2
	ON T1.PART_ID = T2.ID

/*----------------------------
CHECK TO MAKE SURE TABLE EXIST 
OR NOT
-----------------------------*/
IF EXISTS(SELECT NAME FROM tempdb.dbo.sysobjects
	WHERE NAME LIKE '#TEMP%')
	DROP TABLE #TEMP1
GO
	
IF EXISTS(SELECT NAME FROM tempdb.dbo.sysobjects
	WHERE NAME LIKE '#TEMP%')
	DROP TABLE #TEMP2
GO