Running remainder 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)

Running remainder query

Author

Topic

notmyrealname

98 Posts

Posted - 2011-11-28 : 14:19:27

Hi,

Say i have two tables:

PURCHASE_ORDERS:
Qty Type
-------------
500 HS1X3

WORK_ORDERS:
Qty Type ShipDate
---------------------------------------
250 HS1X3 10/17/2011 12:00:00 AM
100 HS1X3 10/31/2011 12:00:00 AM
100 HS1X3 11/2/2011 12:00:00 AM
100 HS1X3 11/2/2011 12:00:00 AM
100 HS1X3 11/2/2011 12:00:00 AM
100 HS1X3 11/2/2011 12:00:00 AM

I need to display an ORDERED status to the WORK_ORDERS results. I need to assign the PURCHASE_ORDER Qty 500 to the WORK_ORDER results until i run out.

Qty OrderQty Type ShipDate
-----------------------------------------------
250 250 HS1X3 10/17/2011 12:00:00 AM
100 100 HS1X3 10/31/2011 12:00:00 AM
100 100 HS1X3 11/2/2011 12:00:00 AM
100 50 HS1X3 11/2/2011 12:00:00 AM
100 0 HS1X3 11/2/2011 12:00:00 AM
100 0 HS1X3 11/2/2011 12:00:00 AM

I am wondering if there is any way to join PURCCHASE_ORDERS to WORK_ORDERS and return an OrderQty that "pulls from" the PURCHASE_ORDERS Qty field. I want the Order Qty to simply display the Qty if there are enough left, otherwise display whatever is left.

Any help would be greatly appreciated.

Thanks.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-11-28 : 19:43:08

Wouldn't you also need to keep track of how the WorkOrder quantities are allocated? Otherwise, the original 750 will be continually allocated as new PurchaseOrders are received.

=======================================
Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith

notmyrealname

98 Posts

Posted - 2011-11-29 : 08:52:13

Hi,

The WORK_ORDERS rows are sorted by the ShipDate field. The goal is to use the Qty available in the PURCHASE_ORDERS table to supply the WORK_ORDERS Qty until we run out. It is quite possible that the WORK_ORDERS ShipDate values will change but this is ok. We just want to use the PURCHASE_ORDERS Qty as needed.

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources