Despatch Note (Items To Follow)

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)

Despatch Note (Items To Follow)

Author

Topic

steve_c
Yak Posting Veteran

54 Posts

Posted - 2007-10-16 : 12:25:36

Hi all - am struggling a bit here... Am using reporting services and have created a despatch note which uses a stored proc as the datasource. All working fine :) However, now the client wants an extra field called "to follow" for any items on the current despatch note which has items outstanding (not on any despatch notes).

To try and explain further, a customer orders 100 televisions. I have despatched 10 and 20 on seperate notes. I am now despatching 50. The "to follow" amount should be "20". This is the proc I am currently using:

SELECT     despatch.despatchid, despatch.orderid, despatch.address1, despatch.address2, despatch.address3, despatch.address4, despatch.address5, 
                      despatch.postcode, orders.clientorderno, despatch.datedespatched, clients.accountno, despatch.deliverycontact, despatch.specialinstructions, 
                      despatchline.qty, despatchline.description, products.productcode
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 LEFT OUTER JOIN
                      products ON despatchline.productid = products.productid
WHERE     (despatch.despatchid = @despatchid)

Any help greatly appreciated.

Steve.

Kristen
Test

22859 Posts

Posted - 2007-10-16 : 12:42:18

I would define ToFollow in the SELECT list as something like:


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

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources