tricky query, help needed. - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

tricky query, help needed.

Author

Topic

brettgab
Starting Member

4 Posts

Posted - 2004-05-24 : 21:52:37

Hi all,

Frustrated at my lack of Transact-SQL knowledge, wish my employer would send me on a course!!! For now I will beg someone who is cluey to help me...

I also have a table called PRODUCTS, key field PRODUCTID

I have a table called STOCK. The important fields here are the ID and the quantity of that item in the warehouse.
STOCKID QUANTITYONHAND
1 10
40 3
100 5

Each Product is built using a quantity of 1 or more Stock Items. The definitions for a product's stock items are found in the PRODUCTPARENTCHILDREN table which has the structure

PRODUCTID STOCKID QUANTITYTOBUILD
10 1 5
10 40 1
10 100 10
11 40 1
12 1 1
12 40 1
12 100 5
13 1 15
14 40 2

What I need is a VIEW which shows the QUANTITY available for each PRODUCT based on the quantity available of the STOCK ITEMS that make it up.

Based on the above values, the VIEW would have results like so...
PRODUCTID QUANTITYAVAILABLE
10 2
11 3
12 1
13 0

What I effectively need to achieve is to do DIV the QUANTITYONHAND by the QUANTITYTOBUILD (ie get the whole number only) for each stock item of the Product. The QUANTITYAVAILABLE is then the highest value of each of the product's
(QUANTITYONHAND DIV QUANTITYTOBUILD) results.

Would be very appreciative, because if I have to write a function to calculate this, it's going to slow everything down.

Regards,

Brett

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-05-24 : 22:19:51

Brett,

This is how it could be done in one statement:

SELECT T.ProductID, Min(T.Units) FROM
(SELECT PPC.ProductID, PPC.StockID, PPC.QuantityToBuild,
S.QuantityOnHand, S.QuantityOnHand / PPC.QuantityToBuild as Units
FROM dbo.PRODUCTPARENTCHILDREN PPC INNER JOIN
dbo.STOCK S ON PPC.StockID = S.StockID
) T
GROUP BY ProductID

Although I'd prefer to create a view of the bracketed subquery as it looks a bit neater.

BTW - You can't build any of product 10 as you have only 5 of item 100, when you need 10 per unit.

Tim

btw - Are you in Oz?

brettgab
Starting Member

4 Posts

Posted - 2004-05-25 : 01:06:21

It worked, mate you are a legend. Great knowledge of SQL.

Yes I am in Australia, Melbourne. If you are as well, we will have to meet up and I'll buy you a beer or few.

Cheers,
Brett.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-05-25 : 01:39:16

You're not the Brett, once of Crazy John's are you???

brettgab
Starting Member

4 Posts

Posted - 2004-05-25 : 19:00:56

OMG. Yep that would be me. How did you guess that ?????

Are you the Tim the Pronto expert who was put on just after I was made redundant ? If so, are you still at Crazy Fuckheads ?

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-05-25 : 19:07:08

Nope - I was the one you replaced.

brettgab
Starting Member

4 Posts

Posted - 2004-05-25 : 19:12:44

Freaky Tim, freaky. There was a Tim who also replaced me, hence the confusion.

So what's happening? Last time I saw you at South Melbourne. Are you working in the city ? If so I will buy you a beer like I promised.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-05-25 : 19:16:15

I'm working at the ASX building. Send me a mail - check my profile for the address.

Tim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources