March 17, 2009 at 4:49 pm
Today I have a similar problem like some days ago, but a little different ant that makes me crazy. Hopefully somebody can help me.
Situation is that all has to be calculated on the fly, no writing to the database!
---===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#myparts','U') IS NOT NULL
DROP TABLE #myparts
IF OBJECT_ID('TempDB..#mycombinations','U') IS NOT NULL
DROP TABLE #mycombinations
---===== Create the test table with
CREATE TABLE #myparts
(
ProductID INT,
ProductType INT,
ProductWeight money
)
---===== Create the test table with
CREATE TABLE #mycombinations
(
MainProductID INT,
SubProductID INT,
Quantity money
)
---===== Insert the test data into the test table
INSERT INTO #mycombinations (MainProductID, SubProductID, Quantity)
SELECT '2789','2843',1.00 UNION ALL
SELECT '2789','2898',1.00 UNION ALL
SELECT '2789','2959',2.00 UNION ALL
SELECT '2789','3436',2.00 UNION ALL
SELECT '2843','2960',1.00 UNION ALL
SELECT '2843','2961',2.00
INSERT INTO #myparts (ProductID, ProductType, ProductWeight)
SELECT '2789','7',460 UNION ALL
SELECT '2843','7',320 UNION ALL
SELECT '2898','1',50 UNION ALL
SELECT '2959','1',25 UNION ALL
SELECT '2960','1',30 UNION ALL
SELECT '2961','1',28 UNION ALL
SELECT '3436','1',69;
WITH cteSammel AS (
SELECT
1 AS [Level],
P.ProductID AS pid,
P.ProductWeight AS pw,
P.ProductType AS pt
FROM #myparts P INNER JOIN #mycombinations PC ON (P.ProductID=PC.SubProductID)
UNION ALL
SELECT
X.[Level] + 1,
C.SubProductID,
X.pw *C.Quantity,
P.ProductType
FROM cteSammel X
INNER JOIN #mycombinations C ON (X.pid = C.MainProductID)
INNER JOIN #myparts P ON (C.SubProductID = P.ProductID)
)
SELECT
y.ProductID as partnumber,
SUM(X.pw) AS weight
FROM cteSammel X INNER JOIN #myparts y ON pid = ProductID
GROUP BY y.ProductID
ORDER BY y.ProductID
Result with current query:
partnumber weight
2843320,00
289850,00
295925,00
2960350,00
2961668,00
343669,00
Result has to be:
partnumber weight
2789324,00
284386,00
289850,00
295925,00
296030,00
343669,00
Note that if parttype is 7 there has to be
a combination and therefore the weight of the subparts
has to be taken and not the weight store in that part.
So in general we have to assume that all weight
that is store at a parttype 7 is wrong.
I hope somebody can help me. These recursive queries drives me crazy:crazy:
March 17, 2009 at 7:49 pm
Does this union query get you the right results?
SELECT PC.MainProductID AS partnumber, sum(P.ProductWeight * PC.quantity) AS weight
FROM #myparts P INNER JOIN #mycombinations PC ON (P.ProductID=PC.SubProductID)
WHERE ProductType <>7
GROUP BY MainProductID
UNION ALL
SELECT ProductID as partnumber, productweight as weight
FROM #myparts
WHERE ProductType <>7
ORDER BY partnumber
One of my numbers is different to your expected results so I may have the logic slightly wrong...?
Bevan
March 18, 2009 at 6:25 am
I have just tried your script and you are right. The first item will give a wrong result (I have again checked what I have expected). The first part has two level and the second level is not taken into the weight. Only the first level is counted.
I am sorry, but this does not give results that I am looking for, but thanks so far for your suggestion. Any new ideas?
March 18, 2009 at 11:14 am
I think the trick here is to start at the bottom (parts that don't have subcomponents) and work up.
WARNING: I haven't got access to SS2K5 right now so code below is untested
;WITH cteSammel AS (
SELECT P.ProductID AS pid, P.ProductWeight AS pw
FROM #myparts P
LEFT JOIN #mycombinations C
ON (P.ProductID = C.MainProductID)
WHERE (C.MainProductID IS NULL)
UNION ALL
SELECT P.ProductID, X.pw * C.Quantity
FROM cteSammel X
INNER JOIN #mycombinations C
ON (X.pid = C.SubProductID)
INNER JOIN #myparts P
ON (C.MainProductID = P.ProductID)
)
SELECT
P.ProductID AS partnumber,
P.ProductType AS parttype,
SUM(X.pw) AS weight
FROM #myparts P
INNER JOIN cteSammel X ON (P.ProductID = X.pid)
GROUP BY P.ProductID, P.ProductType
ORDER BY P.ProductID
March 18, 2009 at 12:08 pm
Great! This works on the test data, now I go for testing in real!
Thanks so far.
IT WORKS. GREAT! How do you do that without a testbench in from of you. I am impressed!
THANKS AGAIN.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply