This is an extremely convoluted query.

However, breaking it up using a CTE, we can reduce the complexity a bit to something in the line of:

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

Another note:

Given you are using GROUP BY's in your queries, is the use of DISTINCT also required?