Untitled

SQL query for inventory.


veerendra kumar meka
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;

This is a SQL query that selects various fields from the "ACTIVE_LOCATION" table in the "BI_DATA" schema, and joins it with the "ITEM_MASTER_DETAIL" table in the same schema.

The query filters the results to only include rows where the warehouse is 'CRT' and the zone is 'G', the aisle is 'RD', and the bay is '47'.

The query also includes some calculated fields, such as the total number of locations, the number of empty locations, and the percentage of locations occupied by inventory.

The results are grouped by warehouse, zone, aisle, bay, level, assignment zone, maximum volume, company, pick determination zone, and location barcode.

The query also includes a CASE statement to assign a size category based on the value of the "Assignment_Zone" field.




veerendra kumar meka
For column LocationWithInventory we need to use below subquery with can be joined
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

Yes, you are correct. We can use the subquery you provided to get the count of distinct location barcodes where actual inventory is greater than 0, and then join it with the main query to get the value of the "LocationsWithInventory" column.

The modified query would look like this:

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 '