Untitled

Select AC.Warehouse,AC.COMPANY, AC.ZONE Zone,AC.AISLE, AC.BAY, AC.LEVEL,

AC.Assignment_Zone 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 Size, 
AC.MAX_VOLUME MaxVolume, 
AC.Location_Barcode as CONT,
count(distinct AC.Location_Barcode) TotalLocations, 
--Sum(Case when AC.Actual_Inventory > 0 then 1 else 0 end) as LocationsWithInventory,  
Case when Sum(AC.Actual_Inventory) > 0 then 1 else 0 end 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)) 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 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

where AC.WAREHOUSE = 'CRT' 
and AC.ZONE = 'G' AND AC.AISLE = 'RD' AND AC.BAY = '47'

Group by AC.Warehouse, AC.Zone,AC.AISLE, AC.BAY, AC.LEVEL, AC.Assignment_Zone,AC.MAX_VOLUME,AC.COMPANY,AC.PICK_DETERMINATION_ZONE, AC.Location_Barcode;





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