DECLARE @current_year_and_period INT;
DECLARE @prev_3_months_year_and_period INT;
DECLARE @prev_6_months_year_and_period INT;
DECLARE @prev_9_months_year_and_period INT;
DECLARE @prev_12_months_year_and_period INT;
DECLARE @prev_24_months_year_and_period INT;


SET @current_year_and_period = CAST(LEFT(CONVERT(CHAR(8), DATEADD(MM, 0, getdate()),112), 6) AS INT);
SET @prev_3_months_year_and_period = CAST(LEFT(CONVERT(CHAR(8), DATEADD(MM, -3, getdate()),112),6) AS INT);
SET @prev_6_months_year_and_period = CAST(LEFT(CONVERT(CHAR(8), DATEADD(MM, -6, getdate()),112),6) AS INT);
SET @prev_9_months_year_and_period = CAST(LEFT(CONVERT(CHAR(8), DATEADD(MM, -9, getdate()),112),6) AS INT);
SET @prev_12_months_year_and_period = CAST(LEFT(CONVERT(CHAR(8), DATEADD(MM, -12, getdate()),112),6) AS INT);
SET @prev_24_months_year_and_period = CAST(LEFT(CONVERT(CHAR(8), DATEADD(MM, -24, getdate()),112),6) AS INT);

WITH inv_history AS (
	SELECT
	inv_value.company_id
	,inv_value.default_branch_id
	,inv_value.branch_description
	,inv_value.item_id
	,inv_value.[inv_mast_uid]
	,inv_value.[item_desc]
	,(inv_value.[qty_on_hand])
	,((inv_value.[qty_on_hand]- inv_loc.qty_allocated)) AS 'total_available'
	,inv_loc.moving_average_cost
	,(((inv_value.qty_on_hand - inv_value.special_layer_qty) * inv_value.[cost]) + inv_value.special_layer_value) AS extended_value
	,CAST(ROUND(inv_loc.qty_allocated * inv_loc.moving_average_cost,2)AS DECIMAL (38,2)) AS allocated_value
	,SUM
	(
	CASE
	WHEN [p21_inventory_usage_all_view].year_and_period BETWEEN @prev_3_months_year_and_period AND @current_year_and_period
	THEN [p21_inventory_usage_all_view].actual_usage
	ELSE CAST(0 AS DECIMAL(19, 9))
	END
	) AS previous_3_month_useage
	,SUM
	(
	CASE
	WHEN [p21_inventory_usage_all_view].year_and_period BETWEEN @prev_6_months_year_and_period AND @current_year_and_period
	THEN [p21_inventory_usage_all_view].actual_usage
	ELSE CAST(0 AS DECIMAL(19, 9))
	END
	) AS previous_6_month_useage
	,SUM
	(
	CASE
	WHEN [p21_inventory_usage_all_view].year_and_period BETWEEN @prev_9_months_year_and_period AND @current_year_and_period
	THEN [p21_inventory_usage_all_view].actual_usage
	ELSE CAST(0 AS DECIMAL(19, 9))
	END
	) AS previous_9_month_useage
	,SUM
	(
	CASE
	WHEN [p21_inventory_usage_all_view].year_and_period BETWEEN @prev_12_months_year_and_period AND @current_year_and_period
	THEN [p21_inventory_usage_all_view].actual_usage
	ELSE CAST(0 AS DECIMAL(19, 9))
	END
	) AS previous_12_month_useage
	,SUM
	(
	CASE
	WHEN [p21_inventory_usage_all_view].year_and_period BETWEEN @prev_24_months_year_and_period AND @current_year_and_period
	THEN [p21_inventory_usage_all_view].actual_usage
	ELSE CAST(0 AS DECIMAL(19, 9))
	END
	) AS previous_24_month_useage

	,p21_view_address.name AS supplier_name
	,inv_loc.last_purchase_date
	,p21_view_inv_mast.product_type
	,inv_loc.stockable
	,inv_loc.date_created
	,inv_loc.period_first_stocked
	,inv_loc.year_first_stocked
	,inv_loc.replenishment_method
	,inv_loc.inv_min
	,inv_loc.inv_max
	,class.class_description
	,CAST( LEFT(CONVERT(CHAR (8),(inv_loc.period_first_stocked+(inv_loc.year_first_stocked*100)),120),6) AS INT) AS first_stocked 
	,@current_year_and_period AS current_year_and_period
	,@prev_3_months_year_and_period AS prev_3_months_year_and_period


	FROM p21_view_inventory_value_report AS inv_value
	LEFT OUTER JOIN p21_view_inv_loc AS inv_loc ON (inv_loc.inv_mast_uid = inv_value.inv_mast_uid) AND (inv_loc.location_id = inv_value.location_id)

	LEFT OUTER JOIN [p21_inventory_usage_all_view] ON (inv_value.inv_mast_uid = [p21_inventory_usage_all_view].inv_mast_uid AND
	inv_value.location_id = [p21_inventory_usage_all_view].location_id)
	LEFT OUTER JOIN p21_view_inv_mast ON inv_value.inv_mast_uid = p21_view_inv_mast.inv_mast_uid
	LEFT OUTER JOIN p21_view_address ON inv_value.primary_supplier_id = p21_view_address.id
	LEFT OUTER JOIN p21_view_class AS class ON p21_view_inv_mast.class_id3 = class.class_id
	LEFT OUTER JOIN p21_view_inv_sub ON p21_view_inv_sub.inv_mast_uid = inv_value.inv_mast_uid


	GROUP BY
	inv_value.company_id
	,inv_value.[default_branch_id]
	,inv_value.[branch_description]
	,inv_value.[item_id]
	,inv_value.[inv_mast_uid]
	,inv_value.[item_desc]
	,(inv_value.[qty_on_hand])
	,((inv_value.[qty_on_hand]-inv_loc.qty_allocated))
	,inv_loc.moving_average_cost
	,(((inv_value.[qty_on_hand] - inv_value.special_layer_qty) * inv_value.[cost]) + inv_value.special_layer_value )
	,inv_loc.qty_allocated * inv_loc.moving_average_cost
	,inv_loc.stockable
	,(p21_view_inv_mast.product_type)
	,p21_view_address.name
	,inv_loc.last_purchase_date
	,inv_loc.date_created
	,inv_loc.period_first_stocked+inv_loc.year_first_stocked
	,inv_loc.period_first_stocked
	,inv_loc.year_first_stocked
	,inv_loc.replenishment_method
	,inv_loc.inv_min
	,inv_loc.inv_max
	,class.class_description
	),
	inventory_project AS (


	SELECT
	default_branch_id
	,branch_description
	,inv_mast_uid
	,item_id
	,item_desc
	,qty_on_hand
	,total_available
	,moving_average_cost
	,extended_value
	,allocated_value
	,supplier_name 
	,replenishment_method
	,inv_min
	,inv_max
	,previous_3_month_useage
	,previous_6_month_useage
	,previous_12_month_useage
	,previous_24_month_useage
	,class_description as equipment_flag
	,last_purchase_date
	,CASE
		WHEN date_created >= DATEADD(MM, -3, GETDATE()) AND previous_3_month_useage = 0
			OR previous_24_month_useage <=0 AND last_purchase_date >= @prev_3_months_year_and_period
		THEN 'F Fresh No Usage'
		WHEN previous_24_month_useage <= 0
		THEN 'E Really Dead (24)'
		WHEN previous_12_month_useage <= 0
		THEN 'D Dead (12)'
		WHEN (qty_on_hand > previous_3_month_useage) and (qty_on_hand <= previous_12_month_useage)
		THEN 'B Overstock (3-12)'
		WHEN qty_on_hand <= previous_3_month_useage
		THEN 'A Good (QOH less than 3)' 
		ELSE 'C Surplus (QOH > 12)'
	END AS [status]
	,CASE
		WHEN date_created >= DATEADD(MM, -3, GETDATE()) AND previous_3_month_useage = 0
			OR previous_24_month_useage <=0 AND last_purchase_date >= @prev_3_months_year_and_period
		THEN 0
		WHEN previous_24_month_useage = 0
		THEN 730 
		WHEN previous_12_month_useage = 0
		THEN 365 
		ELSE CAST(ROUND(((qty_on_hand * 365) / previous_12_month_useage),2) AS DECIMAl (38,2))
	END AS [DIO]
	,CASE
		WHEN previous_12_month_useage = 0
		THEN 0
		WHEN moving_average_cost= 0
		THEN 0
		WHEN extended_value = 0
		THEN 0 
		ELSE CAST(ROUND( ((previous_12_month_useage * moving_average_cost )/extended_value ),2) AS DECIMAL (38,2))

	END AS [Turn]
	,inv_history.date_created



	FROM inv_history
	) ,
	inv AS (
	Select DISTINCT
	inventory_project.*
	,CASE
	WHEN sub.inv_mast_uid = inventory_project.inv_mast_uid
	Then sub.sub_item_id
	Else 'no_sub'
	END as subsitute


	FROM inventory_project

	LEFT OUTER JOIN p21_view_inv_sub sub ON inventory_project.inv_mast_uid = sub.inv_mast_uid

	)
SELECT DISTINCT
inv.*
,CASE WHEN inv.subsitute <> 'no_sub' AND sub.sub_item_id IS NOT NULL THEN inv.status ELSE 'no_status' END sub_status


FROM
  inv
LEFT OUTER JOIN inv sub ON inv.inv_mast_uid = sub.sub_item_id