Untitled

SELECT 
    AC.Warehouse,
    AC.COMPANY, 
    AC.ZONE AS Zone,
    AC.AISLE AS Aisle, 
    AC.BAY AS Bay, 
    AC.LEVEL AS 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, 
    LocationsWithInventory.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 'PKMS' 
            ELSE 'WMI' 
        END
    LEFT OUTER JOIN (
        SELECT COUNT(DISTINCT(AC.Location_Barcode)) LocationsWithInventory ,
AC.Warehouse, AC.COMPANY, AC.ZONE Zone,
AC.Assignment_Zone AssignmentZone, 
AC.MAX_VOLUME MaxVolume, 
AC.PICK_DETERMINATION_ZONE DETERMINATION_ZONE FROM "TL2"."BI_DATA"."ACTIVE_LOCATION" AC
where AC.WAREHOUSE = 'CRT' 
AND Actual_Inventory > 0
GROUP BY AC.Warehouse,AC.COMPANY, AC.ZONE, AC.Assignment_Zone , AC.MAX_VOLUME , AC.PICK_DETERMINATION_ZONE)