query help 1

[jcisco2]

I have a view that i'm setting up. the query works but i would like to add a qty that is rolled and subtracted out per line. An example is: say i have item 11081 with a qty of 100, and i have two orders that ask for that item. one asking for a qty of 70, and the other asking for a qty of 40. I would be short -10 total for the order. I would like that -10 to show up in the on hand col. right now what it will show is 100 - 70 resulting 30 next line 100-40 resulting 60.. the result set looks like this thus far:

select A.OrderNumber, A.PONumber, A.OrderStatus, REPLACE(A.CustomerNumber, 'WALBEN0', 'WAL*MART') AS CustomerNumber, A.ShipToName, 
                      REPLACE(A.WarehouseID, 'B03', 'BEL') AS WarehouseID, 
B.ItemNumber, B.QtyOrdered, B.LineType, A.PromiseDate, sum(C.ItemQuantity) [On-Hand], (sum(C.ItemQuantity) - QtyOrdered) QtyResult
FROM dbo.tblOrderMasterfile A INNER JOIN
                      TranMaster.dbo.tblSalesOrderDetail B ON A.OrderNumber = B.OrderNumber
Inner Join PWMS..tblPalletInventory C
ON
B.ItemNumber = C.ItemNumber
Where (A.WarehouseID = 'U105') AND (A.OrderStatus = 'IN PROCESS' OR
                      A.OrderStatus = 'NEW') AND C.WhseCode = '002'
group by A.PromiseDate, A.OrderNumber, A.PONumber, A.OrderStatus, A.CustomerNumber, A.ShipToName, A.WarehouseID,
B.ItemNumber, B.QtyOrdered, B.LineType

dataset:
SO18134 	6047168781            	IN PROCESS	00010150	WAL-MART 6047                 	U105    	14500          	10	1	2006-08-08 00:00:00.000	132	122
SO18134 	6047168781            	IN PROCESS	00010150	WAL-MART 6047                 	U105    	90341          	50	1	2006-08-08 00:00:00.000	1890	1840

any ideas?

cheers.

 

[SQLSister]

Handling it in this query is not the best way to go. Waht you want will be extremely processor intensive and will make your system run slowly and may or may not be accurate depending on if your query contains all the possible order records.

What i would do is add a column for available items to the item table. You will have to intialize the data by subtracting unfilled orders from existing warehouse count of the item. Have the system automatically decrement this count whenever an order for that item is placed. If an order is cancelled before shipping, have a trigger put the amount back into the record.

Then your query simply needs to access the data inthis field which is alawys up-to-date and doesn;t require lengthy calculations through individual records to get the answer each time you run the query.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[jcisco2]

thanks!