December 22, 2011 at 12:46 pm
I am developing a report using crystal reports to show "available inventory" and am doing so by trying to create a view in the database to get a single result set that I can easily use in crystal. The data is stored in multiple tables and the information needed for one product is in 2 rows in the myLots and myBins tables below. (I did not design nor can modify the table structure and anyone who has worked with the CDC Ross ERP system will feel my pain). The best way I can explain the situation is that there are 2 units of measure for each product in the myProducts table (CapacityUOM and WeightUOM), then for each unique type of Product, Lot, Status, and Warehouse there will be 2 rows in myLots (one for each UOM). If the product is stored in a "binned" warehouse, there will also be entries in the myBins table which further breaks down the Lot quantity into Bins with 2 rows for each unique Product, Lot, Bin, Status, Warehouse (1 row for each UOM). I have done some very complex joins to create this view but still not quite getting the results I want and am hoping someone may have an easier way. I am a programmer by trade so am not a SS Expert and I have tried to follow the suggestions for posting, I apologize for any confusion and appreciate any help you can provide. Based on the data below, please view the attachement to see the desired results set:
Bin Bin C Bin Bin W Lot Lot C Lot Lot W
Part Lot Numb Wh Stat Bin Cpcty UOM Wght UOM Cpcty UOM Wght UOM
------ --------- --- ---- ----- ------ ----- ----- ---- ------ ---- ----- -----
31114 40110711 09 QOH 210 50 CS 2000 LB 74 CS 2940 LB
31114 40110711 09 QOH DOCK 24 CS 940 LB 74 CS 2940 LB
31114 40110711 40 QOH NULL NULL NULL NULL NULL 10 CS 200 LB
31114 40110711 40 QC NULL NULL NULL NULL NULL 5 CS 100 LB
31114 20111214 40 QOH NULL NULL NULL NULL NULL 25 CS 125 LB
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#myProducts','U') IS NOT NULL
DROP TABLE #myProducts
IF OBJECT_ID('TempDB..#myLots','U') IS NOT NULL
DROP TABLE #myLots
IF OBJECT_ID('TempDB..#myBins','U') IS NOT NULL
DROP TABLE #myBins
--===== Create the test table with
CREATE TABLE #myProducts
(
PartCode nvarchar(10),
CapacityUOM nvarchard(10),
WeightUOM nvarchar(10)
)
CREATE TABLE #myLots
(
PartCode nvarchar(10),
Lot nvarchar(10),
Status nvarchar(10),
Warehouse nvarchar(2),
UOM nvarchar(10),
Qty numeric(24,6)
)
CREATE TABLE #myBins
(
PartCode nvarchar(10),
Lot nvarchar(10),
Status nvarchar(10),
Warehouse nvarchar(2),
Bin nvarchar(10),
UOM nvarchar(10),
Qty numeric(24,6)
)
--===== Insert the test data into the test table
INSERT INTO #myProducts
(PartCode, CapacityUOM, WeightUOM)
SELECT '31114','CS','LB'
INSERT INTO #myLots
(PartCode, Lot, Status, Warehouse, UOM, Qty)
SELECT '3114','40110711','QOH','09','CS', '74.0' UNION ALL
SELECT '3114','40110711','QOH','09','LB', '2940' UNION ALL
SELECT '3114','40110711','QOH','40','CS', '10.0' UNION ALL
SELECT '3114','40110711','QOH','40','LB', '200' UNION ALL
SELECT '3114','40110711','QC','40','CS', '5.0' UNION ALL
SELECT '3114','40110711','QC','40','LB', '100' UNION ALL
SELECT '3114','20111214','QOH','40','CS', '25.0' UNION ALL
SELECT '3114','20111214','QOH','40','LB', '125'
INSERT INTO #myBins
(PartCode, Lot, Status, Warehouse, Bin, UOM, Qty)
SELECT '3114','40110711','QOH','09','210','CS', '50.0' UNION ALL
SELECT '3114','40110711','QOH','09','210','LB', '2000' UNION ALL
SELECT '3114','40110711','QOH','09','DOCK','CS', '24.0' UNION ALL
SELECT '3114','40110711','QOH','09','DOCK','LB', '940' UNION ALL
December 22, 2011 at 1:15 pm
Does this essentially get you what you need?
;
WITH LB
AS (SELECT PartCode,
Lot,
[Status],
Warehouse,
Bin,
UOM,
Qty
FROM #myBins
WHERE UOM = 'LB'
UNION ALL
SELECT PartCode,
Lot,
[Status],
Warehouse,
NULL AS Bin,
UOM,
Qty
FROM #myLots
WHERE UOM = 'LB'),
CS
AS (SELECT PartCode,
Lot,
[Status],
Warehouse,
Bin,
UOM,
Qty
FROM #myBins
WHERE UOM = 'CS'
UNION ALL
SELECT PartCode,
Lot,
[Status],
Warehouse,
NULL AS Bin,
UOM,
Qty
FROM #myLots
WHERE UOM = 'CS')
SELECT COALESCE(LB.PartCode, CS.PartCode) AS PartCode,
COALESCE(LB.Lot, CS.Lot) AS Lot,
COALESCE(LB.[Status], CS.[Status]) AS [Status],
COALESCE(LB.Warehouse, CS.Warehouse) AS Warehouse,
COALESCE(LB.Bin, CS.Bin) AS Bin,
SUM(LB.Qty) AS LBQty,
SUM(CS.Qty) AS CSQty
FROM LB
FULL OUTER JOIN CS
ON LB.PartCode = CS.PartCode
AND LB.Lot = CS.Lot
AND LB.Status = CS.Status
AND LB.Warehouse = CS.Warehouse
AND (LB.Bin = CS.Bin
OR LB.Bin IS NULL
AND CS.Bin IS NULL)
AND LB.UOM != CS.UOM
AND LB.UOM = 'LB'
AND CS.UOM = 'CS'
GROUP BY COALESCE(LB.PartCode, CS.PartCode),
COALESCE(LB.Lot, CS.Lot),
COALESCE(LB.[Status], CS.[Status]),
COALESCE(LB.Warehouse, CS.Warehouse),
COALESCE(LB.Bin, CS.Bin);
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 22, 2011 at 1:19 pm
Here's a slightly different approach. You'll need to reduce it to the columns needed.
SELECT * FROM
(
SELECT
PartCode,
Lot,
Status,
Warehouse,
MAX(CASE WHEN UOM='CS' THEN Qty ELSE NULL END) AS CS,
MAX(CASE WHEN UOM='LB' THEN Qty ELSE NULL END) AS LB
FROM #myLots
GROUP BY PartCode, Lot, Status, Warehouse
) l
LEFT OUTER JOIN
(
SELECT
PartCode,
Lot,
Status,
Warehouse,
Bin,
MAX(CASE WHEN UOM='CS' THEN Qty ELSE NULL END) AS CS_Qty,
MAX(CASE WHEN UOM='LB' THEN Qty ELSE NULL END) AS LB_Qty
FROM #myBins
GROUP BY PartCode, Lot, Status, Warehouse,Bin
) b
ON l.PartCode=b.PartCode
AND l.Lot=b.Lot
AND l.Status=b.Status
AND l.Warehouse=b.Warehouse
December 22, 2011 at 1:22 pm
Lutz, that won't get anything that's in both places (if that's possible), nor will it get anything that's in Bins but not in Lots (if that's possible). At very least, turn it into a Full Outer Join, instead of just Left Outer.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 22, 2011 at 1:28 pm
Thank you guys for the quick response and the ideas here are similar to what I've been testing out but one of the problems I have is that the UOM's will not necessarily always be 'CS' and 'LB', they will be in the Lots and Bins tables as whatever the Capacity and Weight UOM for that product are as indicated in the Products table, it should always be 'LB' for the weight but the capacity can be several different things.
Also, I apologize, but I seem to have lost the formatting of the results set that I put in the post, I'm trying to figure out how to show that neatly.
Sorry for the dumb question but can I use the CTE Where expression in a view? Maybe I just need to join that code with the Products table and use products.capacityuom and products.weightuom instead of 'CS' and 'LB'?
Thank You
December 22, 2011 at 1:32 pm
GSquared (12/22/2011)
Lutz, that won't get anything that's in both places (if that's possible), nor will it get anything that's in Bins but not in Lots (if that's possible). At very least, turn it into a Full Outer Join, instead of just Left Outer.
But at least it'll return the requested number of rows and the correct values as provided in the expected result. 😉
It depends on the data structure if a full outer join would help or if there's another approach required. But that information is not available at the moment...
December 22, 2011 at 1:35 pm
stourault (12/22/2011)
Thank you guys for the quick response and the ideas here are similar to what I've been testing out but one of the problems I have is that the UOM's will not necessarily always be 'CS' and 'LB', they will be in the Lots and Bins tables as whatever the Capacity and Weight UOM for that product are as indicated in the Products table, it should always be 'LB' for the weight but the capacity can be several different things.Also, I apologize, but I seem to have lost the formatting of the results set that I put in the post, I'm trying to figure out how to show that neatly.
Sorry for the dumb question but can I use the CTE Where expression in a view? Maybe I just need to join that code with the Products table and use products.capacityuom and products.weightuom instead of 'CS' and 'LB'?
Thank You
You can definitely join to the Products table instead of using the hard-coded strings in the Where clause. That will work in either solution (mine or Lutz's).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 22, 2011 at 1:39 pm
stourault (12/22/2011)
Thank you guys for the quick response and the ideas here are similar to what I've been testing out but one of the problems I have is that the UOM's will not necessarily always be 'CS' and 'LB', they will be in the Lots and Bins tables as whatever the Capacity and Weight UOM for that product are as indicated in the Products table, it should always be 'LB' for the weight but the capacity can be several different things.Also, I apologize, but I seem to have lost the formatting of the results set that I put in the post, I'm trying to figure out how to show that neatly.
Would it be possible to provide a more representative example? How would the output need to look in such a scenario?
What you've provided so far leaves the impression that there are fixed values going to a specific column of your result set. If you have another UOM, will that add another set of columns (Cpcty/UOM and Wght/UOM)?
December 22, 2011 at 1:40 pm
stourault (12/22/2011)
Also, I apologize, but I seem to have lost the formatting of the results set that I put in the post, I'm trying to figure out how to show that neatly.
You need to use the [code] tags from the IFCode Shortcuts on the left-hand side of the message composer if you want to indicate that your text is pre-formatted. Otherwise it treats it as normal HTML text and collapses all whitespace into a single space.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 22, 2011 at 1:57 pm
To hopefully clarify the logic...if there products in the Bins table then they will always be in Lots (but not vice versa), all of our inventory is lot based so lots should have all the inventory in all the warehouses. Some warehouses store inventory by bins and some do not...for those that are "binned" warehouses then there will be entries in the Bins table which show what bins are part of that lot and how much is in each bin.
If you could see the result set clearly it would show a good example where Lot 40110711 is contained in 2 warehouses 09 (binned) and 40 (non-binned) so the Lots table shows that for that product and lot (only looking at status QOH for now), warehouse 40 has 200 LB (5 CS) and warehouse 09 has 2940 LB (74 CS).
Since 09 is a binned warehouse there are rows in the Bins table that show that for that same product and lot and warehouse that there are 2000 LB (50 CS) in Bin 210 and 940 LB (24 CS) in Bin "DOCK". So the amount shown in Lots for this product, lot, warehouse is a total of the Bins.
I uploaded an attachment which hopefully shows the desired result set better.
Thank you for your help
December 22, 2011 at 2:09 pm
Did you have a look at my solution?
It shows the result you're looking for (at least after eliminating the undesired columns).
But that still doesn't answer the questions I asked earlier.
December 22, 2011 at 2:30 pm
I have not had a chance to try the suggested code yet, I have been also participating in a webinar at the same time, however I am eager to try and greatly appreciate the help. To try to answer your questions, there will always only be 2 units of measure, one for capacity and one for weight, these values for a given product will be stored in products.capacityUOM and products.weightUOM, which is the only reason I need to access that table. The weight UOM should always be 'LB' (I think) but the capacity UOM can be 'CS', 'HD', 'COMBO', 'BOX', 'PC' or a few others.
December 22, 2011 at 2:48 pm
Will something like this bring you closer to the result you're looking for?
The information that there will never be more than two UOMs allows to use the ROW_NUMBER approach to no longer need to know the two values.
;
WITH cte_lots AS
(
SELECT
PartCode,
Lot,
Status,
Warehouse,
UOM,
Qty,
ROW_NUMBER() OVER(PARTITION BY PartCode, Lot, Status, Warehouse ORDER BY UOM ) AS rwn
FROM #myLots
),
cte_lots_pvt AS
(
SELECT
PartCode,
Lot,
Status,
Warehouse,
MAX(CASE WHEN rwn=1 THEN UOM ELSE NULL END) AS UOM1,
MAX(CASE WHEN rwn=1 THEN Qty ELSE NULL END) AS QTY1,
MAX(CASE WHEN rwn=2 THEN UOM ELSE NULL END) AS UOM2,
MAX(CASE WHEN rwn=2 THEN Qty ELSE NULL END) AS QTY2
FROM cte_lots
GROUP BY PartCode, Lot, Status, Warehouse
),
cte_bins AS
(
SELECT
PartCode,
Lot,
Status,
Warehouse,
Bin,
UOM,
Qty,
ROW_NUMBER() OVER(PARTITION BY PartCode, Lot, Status, Warehouse,Bin ORDER BY UOM ) AS rwn
FROM #myBins
),
cte_bin_pvt AS
(
SELECT
PartCode,
Lot,
Status,
Warehouse,
Bin,
MAX(CASE WHEN rwn=1 THEN UOM ELSE NULL END) AS BIN_UOM1,
MAX(CASE WHEN rwn=1 THEN Qty ELSE NULL END) AS BIN_QTY1,
MAX(CASE WHEN rwn=2 THEN UOM ELSE NULL END) AS BIN_UOM2,
MAX(CASE WHEN rwn=2 THEN Qty ELSE NULL END) AS BIN_QTY2
FROM cte_bins
GROUP BY PartCode, Lot, Status, Warehouse,Bin
)
SELECT * FROM
cte_lots_pvt l
LEFT OUTER JOIN
cte_bin_pvt b
ON l.PartCode=b.PartCode
AND l.Lot=b.Lot
AND l.Status=b.Status
AND l.Warehouse=b.Warehouse
December 22, 2011 at 8:46 pm
Thanks to both of you for replying to my posts, I was able to get the exact results I wanted as a view in a much more readable and manageable way than the insane joins I was using. I learned a lot from all the examples and appreciate the help. I've included the actual code I ended up with in case you are interested.
Thank You
;
WITH cteLots AS
(
SELECT
ls.COMPANY_CODE,
ls.PART_CODE,
ls.IC_LOT_NUMBER,
ls.IC_STATUS_CODE,
ls.WAREHOUSE,
ls.UNIT_OF_MEASURE,
ls.IC_QTY_AVAILABLE,
ROW_NUMBER() OVER(PARTITION BY COMPANY_CODE,PART_CODE, IC_LOT_NUMBER, IC_STATUS_CODE, WAREHOUSE ORDER BY UNIT_OF_MEASURE ) AS rwn
FROM IC_LOT_STATUS ls
),
cteLotsComb AS
(
SELECT
COMPANY_CODE,
PART_CODE,
IC_LOT_NUMBER,
IC_STATUS_CODE,
WAREHOUSE,
MAX(CASE WHEN rwn=1 THEN UNIT_OF_MEASURE ELSE NULL END) AS UOM1,
MAX(CASE WHEN rwn=1 THEN IC_QTY_AVAILABLE ELSE NULL END) AS QTY1,
MAX(CASE WHEN rwn=2 THEN UNIT_OF_MEASURE ELSE NULL END) AS UOM2,
MAX(CASE WHEN rwn=2 THEN IC_QTY_AVAILABLE ELSE NULL END) AS QTY2
FROM cteLots
GROUP BY COMPANY_CODE ,PART_CODE, IC_LOT_NUMBER, IC_STATUS_CODE, WAREHOUSE
),
cteBins as
(
SELECT
COMPANY_CODE,
PART_CODE,
IC_LOT_NUMBER,
IC_STATUS_CODE,
WAREHOUSE,
BIN_LOCATION,
UNIT_OF_MEASURE,
IC_QTY_AVAILABLE,
ROW_NUMBER() OVER(PARTITION BY COMPANY_CODE,PART_CODE, IC_LOT_NUMBER, IC_STATUS_CODE, WAREHOUSE, BIN_LOCATION ORDER BY UNIT_OF_MEASURE ) AS rwn
FROM IC_BIN_DETAIL_STATUS
),
cteBinsComb AS
(
SELECT
COMPANY_CODE,
PART_CODE,
IC_LOT_NUMBER,
IC_STATUS_CODE,
WAREHOUSE,
BIN_LOCATION,
MAX(CASE WHEN rwn=1 THEN UNIT_OF_MEASURE ELSE NULL END) AS binUOM1,
MAX(CASE WHEN rwn=1 THEN IC_QTY_AVAILABLE ELSE NULL END) AS binQTY1,
MAX(CASE WHEN rwn=2 THEN UNIT_OF_MEASURE ELSE NULL END) AS binUOM2,
MAX(CASE WHEN rwn=2 THEN IC_QTY_AVAILABLE ELSE NULL END) AS binQTY2
FROM cteBins
GROUP BY COMPANY_CODE, PART_CODE, IC_LOT_NUMBER, IC_STATUS_CODE, WAREHOUSE, BIN_LOCATION
),
cteInv AS
(
SELECT
l.COMPANY_CODE,
l.PART_CODE,
l.IC_LOT_NUMBER,
l.WAREHOUSE,
l.IC_STATUS_CODE,
l.QTY1,
l.UOM1,
l.QTY2,
l.UOM2,
b.BIN_LOCATION,
b.binQTY1,
b.binUOM1,
b.binQTY2,
b.binUOM2
FROM cteLotsComb l
LEFT OUTER JOIN cteBinsComb b ON
l.COMPANY_CODE = b.COMPANY_CODE
AND l.PART_CODE=b.PART_CODE
AND l.IC_LOT_NUMBER=b.IC_LOT_NUMBER
AND l.IC_STATUS_CODE=b.IC_STATUS_CODE
AND l.WAREHOUSE=b.WAREHOUSE
WHERE l.QTY1 > 0 or l.QTY2 > 0
)
SELECT
cteInv.*,
pm.PART_DESC_1,
pm.IC_UNIT_CAPACITY,
pm.WEIGHT_UOM,
pb.MANUFACTURING_DATE,
pb.CREATION_DATE,
pb.RECEIVED_DATE
FROM cteInv
join PRODUCT_MASTER pm on
pm.COMPANY_CODE = cteInv.COMPANY_CODE
and pm.PART_CODE = cteInv.PART_CODE
left join PART_BATCHES pb on
pb.COMPANY_CODE = cteInv.COMPANY_CODE
and pb.WAREHOUSE = cteInv.WAREHOUSE
and pb.IC_LOT_NUMBER = cteInv.IC_LOT_NUMBER
and pb.PART_CODE = cteInv.PART_CODE
ORDER BY cteInv.PART_CODE, cteInv.IC_LOT_NUMBER
December 23, 2011 at 1:59 am
Glad I could help 😀
This example once again demonstrate how a "semi-optimal" database design usually increase query complexity...
The sad part is, there are companies out there making money by selling such db structures.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply