I am struggling how to get the output I need to provide for a report. I am trying to show what products(LPN) I can combine, to free up space in a warehouse and how many of these opportunities there are. The below code pulls all the data I need but I can't seem to figure out how to summarize how many opportunities to consolidate.
WITH ITEM AS
(
SELECT IC.ITEM_NAME,COUNT(*)
FROM PARCEL_ID L
JOIN PARCEL_DETAIL LD ON L.LPN_ID=LD.LPN_ID
JOIN LOCN LH ON LH.LOCN_ID=L.CURR_SUB_LOCN_ID AND LH.LOCN_CLASS='R' AND
LH.PULL_ZONE='RSV'
JOIN RESV_LOCN RLH ON RLH.LOCN_ID=L.CURR_SUB_LOCN_ID
JOIN ITEM_C IC ON IC.ITEM_ID=LD.ITEM_ID
WHERE L.LPN_FACILITY_STATUS='30' AND LD.SIZE_VALUE <'30' GROUP BY
IC.ITEM_NAME HAVING COUNT(*)>'1'
)
SELECT L.TC_LPN_ID,LD.SIZE_VALUE,LH.DSP_LOCN,L.ACTUAL_VOLUME,L.WEIGHT,
IC.ITEM_NAME,RLH.MAX_VOL,RLH.MAX_WT
FROM PARCEL L
JOIN PARCEL_DETAIL LD ON L.LPN_ID=LD.LPN_ID
JOIN LOCN LH ON LH.LOCN_ID=L.CURR_SUB_LOCN_ID AND LH.LOCN_CLASS='R' AND
LH.PULL_ZONE ='RSV'
JOIN RESV_LOCN RLH ON RLH.LOCN_ID=L.CURR_SUB_LOCN_ID
JOIN ITEM_C IC ON IC.ITEM_ID=LD.ITEM_ID
WHERE L.LPN_FACILITY_STATUS='30' AND LD.SIZE_VALUE <'30' AND IC.ITEM_NAME IN
(SELECT ITEM_NAME FROM ITEM)
The data returned is a list showing any product which is stored in multiple reserve locations and need to be consolidated. Looking like the below.
LPN|Qty|Loc|Volume|Weight|Item Name| Max Vol | Max Wt
1 | 12|1 | 25 | 15 | ABC123 | 500 | 250
2 | 25|2 | 300 | 150 | ABC123 | 500 | 250
3 | 25|3 | 125 | 100 | DEF123 | 750 | 250
4 | 35|4 | 350 | 250 | DEF123 | 750 | 350
Is there a way to summarize the the number of opportunities for reserve location consolidation? In the above example there are 2 opportunities because we have 2 item names that will not exceed max volume or max weight for either location if combined. There is a second issue where there could be multiple opportunities for the same item name to be combined. How can I accomplish this, using either my current query or as a separate query referencing the result of what I currently have (I am adding this to a Cognos report so I can use a separate object).
I apologize if this is not the place for advice like this but I would appreciate any direction you might be able to offer.
Thank you in advance.
