Stock to follow query - 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 2005 Forums

Transact-SQL (2005)

Stock to follow query

Author

Topic

steve_c
Yak Posting Veteran

54 Posts

Posted - 2007-10-23 : 18:59:24

Hi all - I am having a nightmare with this query...

I have a stock despatch program. A person may place an order for 100 televisions. I may despatch these on 3 seperate despatch notes. On each, I need the amount of stock to follow.

If I despatched 10 televisions on the first despatch note, tofollow would be 90.

My tables are as follows:

Orders
orderid

Orderlines
orderlineid
productid
qty

Despatch
despatchid

DespatchLines
despatchlineid
productid
qty
amount

Products
productid

So far I have the following:

SELECT     despatch.despatchid, despatch.orderid, 
                      despatchline.qty, despatchline.description, products.productcode,
[ToFollow] = despatchline.qty - 
       (
           SELECT SUM(qty) 
           FROM despatch AS D2
                JOIN despatchline AS DL2
                    ON D2.despatchid = DL2.despatchid
                   AND DL2.productid = products.productid
           WHERE D2.orderid = orders.orderid
       )

FROM         despatch INNER JOIN
                      orders ON despatch.orderid = orders.orderid INNER JOIN
                      clients ON orders.clientid = clients.clientid INNER JOIN
                      despatchline ON despatch.despatchid = despatchline.despatchid INNER JOIN
                      orderline ON orders.orderid = orderline.orderid LEFT OUTER JOIN
                      products ON despatchline.productid = products.productid
WHERE     (despatch.despatchid = @despatchid)

This is not working - bringing up too many rows and I can't pin down the error... :(

Thanks,

Steve.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-23 : 21:30:21

can you post some sample data and the expected result ?

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources