Untitled

SELECT
AC.Warehouse,
AC.COMPANY,
AC.ZONE AS Zone,
AC.AISLE,
AC.BAY,
AC.LEVEL,
AC.Assignment_Zone AS AssignmentZone,
CASE
WHEN AC.Assignment_Zone LIKE '%SM' THEN 'Small'
WHEN AC.Assignment_Zone LIKE '%MD' THEN 'Medium'
WHEN AC.Assignment_Zone LIKE '%LG' THEN 'Large'
WHEN AC.Assignment_Zone LIKE '%XL' THEN 'Extra Large'
WHEN AC.Assignment_Zone LIKE '%LN' THEN 'Extra Long'
WHEN AC.Assignment_Zone LIKE '%HG' THEN 'Huge'
WHEN AC.Assignment_Zone LIKE '%GY' THEN 'Gaylord'
WHEN AC.Assignment_Zone LIKE '%PL' THEN 'Pallet'
END AS Size,
AC.MAX_VOLUME AS MaxVolume,
AC.Location_Barcode AS CONT,
COUNT(DISTINCT AC.Location_Barcode) AS TotalLocations,
COALESCE(LocationsWithInventory.LocationsWithInventory, 0) AS LocationsWithInventory,
SUM(CASE WHEN AC.Actual_Inventory = 0 THEN 1 ELSE 0 END) AS EmptyLocations,
CAST(DIV0(SUM(CASE WHEN AC.Actual_Inventory > 0 THEN 1 ELSE 0 END), CAST(COUNT(DISTINCT AC.Location_Barcode) AS DECIMAL(10,2))) AS DECIMAL(10,2)) AS PercentOccupiedByLocation,
CAST(SUM(AC.Location_Length * AC.Location_Width * AC.Location_Height) AS INT) AS LocationCapacityByVolume,
CAST(SUM(AC.Actual_Inventory * ST.UNIT_VOLUME) AS INT) AS VolumeOccupied,
CAST(DIV0(SUM(AC.Actual_Inventory * ST.UNIT_VOLUME), SUM(AC.Location_Length * AC.Location_Width * AC.Location_Height)) AS DECIMAL(10,2)) AS PercentOccupiedByVolume,
CAST(SUM(AC.Actual_Inventory) AS INT) AS Units,
AC.PICK_DETERMINATION_ZONE AS DETERMINATION_ZONE
FROM "TL2"."BI_DATA"."ACTIVE_LOCATION" AC
LEFT OUTER JOIN "TL2"."BI_DATA"."ITEM_MASTER_DETAIL" ST ON
ST.COMPANY = AC.COMPANY AND
ST.DIVISION = AC.DIVISION AND
ST.SKU_SEASON = AC.SKU_Season AND
ST.SKU_SEASON_YEAR = AC.SKU_Season_Year AND
ST.SKU_STYLE = AC.SKU_Style AND
ST.SKU_STYLE_SUFFIX = AC.SKU_Style_Suffix AND
ST.SKU_COLOR = AC.SKU_Color AND
ST.SKU_COLOR_SUFFIX = AC.SKU_Color_Suffix AND
ST.SKU_SEC_DIMENSION = AC.SKU_SEC_DIMENSION AND
ST.QUALITY = AC.SKU_Quality AND
ST.SKU_SIZE_RNGE_CODE = AC.SKU_Size_Range_Code AND
ST.SKU_SIZE_RELATIVE_POSITION_IN_TABLE = AC.SKU_Size_Relaive_Posn_In_Table AND
(CASE WHEN ST.WMI_DATE_TIME_LAST_MODIFIED IS NULL THEN 'PKMS' ELSE 'WMI' END) = (CASE WHEN AC.WMI_DATE_TIME_LAST_MODIFIED IS NULL THEN '