Multiple Table and SUM conundrum

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Multiple Table and SUM conundrum

Author

Topic

FunkyBunch
Starting Member

2 Posts

Posted - 2011-05-31 : 15:01:18

I'm hoping to be able to find some help with the following problem I have, this is my first time asking for help so if I need to give more information let me know.

I have two tables I need to query from

ProductDelivery and ProductStructure

ProductDelivery has 4 Columns I need to use in the Query.

ProductName, ProductQuantity, DueDate, InProgress

ProductStructure has 3 Columns I need in the Query

ProductName, SubPartName, SubPartQtyPer

As an example let's say the ProductDelivery has the following data:

ProductName ProductQuantity DueDate InProgress

Alpha_____________5____________2_________N
Beta _____________7____________3_________N
Alpha_____________5____________4_________N
Kappa_____________6____________2_________Y

And ProductStructure has the following data:

ProductName SubPartName SubPartQtyPer

Alpha_________Screw__________5
Alpha_________Nut____________6
Alpha_________Chassis________1
Beta _________Screw__________4
Beta _________Label__________2
Kappa_________Washer_________10

What I need is a query which tells me how many of each SubPart I need, based on the DueDate in the ProductDelivery Table, excluding the ones that are InProgress.

I want the following query result:

SubPartName QtyReqByDate1 QtyReqByDate2 QtyReqByDate3

Screw___________25_____________53_______________78
Nut_____________30_____________30_______________60
Chassis__________1______________1________________2
Label__________NULL(or '0')____14_______________14
Washer_________NULL__________NULL______________NULL <-The whole line can be not included.

I have tried:
SELECT
ProductStructure.SubPartName,
(CASE WHEN ProductDelivery.DueDate<=2 THEN SUM(ProductStructure.SubPartQtyPer*ProductDelivery.ProductQuantity)
END) AS QtyReqByDate1,
(CASE WHEN ProductDelivery.DueDate<=3 THEN SUM(ProductStructure.SubPartQtyPer*ProductDelivery.ProductQuantity)
END) AS QtyReqByDate2,
(CASE WHEN ProductDelivery.DueDate<=4 THEN SUM(ProductStructure.SubPartQtyPer*ProductDelivery.ProductQuantity)
END) AS QtyReqByDate3

FROM ProductDelivery, ProductStructure

WHERE ProductDelivery.Product=ProductStructure.ProductName AND ProductDelivery.InProgress='N'
GROUP BY ProductStructure.SubPartName, ProducDelivery.DueDate

But this gives the wrong values in the QtyReqByDate columns.

Any help would be appreciated.

Thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-31 : 15:30:51

I get a different answer than you do, so this may need tweaking

DECLARE @t1 TABLE (ProductName varchar(10), ProductQuantity int, DueDate int, InProgress char(1))
INSERT INTO @t1
SELECT 'Alpha',5,2,'N' UNION ALL
SELECT 'Beta',7,3,'N' UNION ALL
SELECT 'Alpha',5,4,'N' UNION ALL
SELECT 'Kappa',6,2,'Y'

DECLARE @t2 TABLE (ProductName varchar(10), SubPartName varchar(10), SubPartQtyPer int)
INSERT INTO @t2
SELECT 'Alpha','Screw',5 UNION ALL
SELECT 'Alpha','Nut',6 UNION ALL
SELECT 'Alpha','Chassis',1 UNION ALL
SELECT 'Beta','Screw',4 UNION ALL
SELECT 'Beta','Label',2 UNION ALL
SELECT 'Kappa','Washer',10

SELECT t2.SubPartName
, SUM( CASE WHEN t1.DueDate <= 2 THEN t2.SubPartQtyPer*t1.ProductQuantity ELSE 0 END) as QtyReqByDate1
, SUM( CASE WHEN t1.DueDate <= 3 THEN t2.SubPartQtyPer*t1.ProductQuantity ELSE 0 END) as QtyReqByDate2
, SUM( CASE WHEN t1.DueDate <= 4 THEN t2.SubPartQtyPer*t1.ProductQuantity ELSE 0 END) as QtyReqByDate3
FROM @t2 t2

INNER JOIN @t1 t1 ON t1.ProductName = t2.ProductName
WHERE t1.inProgress = 'N'
GROUP BY t2.SubPartName

Jim

Everyday I learn something that somebody else already knew

FunkyBunch
Starting Member

2 Posts

Posted - 2011-05-31 : 16:01:50

Thanks, you may just be a life saver:)

The difference in the results was just me making an arithmetic error.
as I computed what I wanted in my head.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources