Sum a order column until a value is reached

Hi
can You  Pls  send the sample  table structure and result set u require

Do you mean you need to return only the earliest records, such that the sum of the orders first exceeds some limit?

Could you show us the relevant table details and give us a bit of an example to go on?

Thanks for replying.

What I actually need to do is to get the value of the outstanding orders based on the stock arriving on the container.

For this I need to reference the PO and the PO order amount and then reference the existing orders and work out the value, but we have more orders than stock arriving and each order will have different sales values.

This made me go down the cursor route fetching each bit of data as required until now I need the orders fetch to stop once the running sum of the orders qty value matches the PO order amount.

Makes my head spin just thinking about it.

This may not have been the correct way to do this and if you know a clear better option please advise that.

For now here is my code.

 

-- THIS NEEDS A COUNT FUNCTION ADDING SOMEWHERE

DECLARE @ponumber varchar(5)
DECLARE @pop_stock_code varchar(7)
DECLARE @sum_pod_qtyord int
DECLARE @po_message varchar(30)
DECLARE @podata_message nvarchar(30)

SET @ponumber = '455'

PRINT ''
	SELECT @po_message = 'VALUE OF CONTAINER ' + @ponumber
		PRINT @po_message

DECLARE po_cursor CURSOR FOR
	SELECT
		pod_stock_code,
		sum(pod_qtyord)
	FROM pop_detail
	WHERE pod_order_no = @ponumber
	GROUP BY pod_stock_code
	ORDER BY pod_stock_code

	OPEN po_cursor

FETCH NEXT
FROM po_cursor INTO @pop_stock_code, @sum_pod_qtyord

/*
=== SECTION TO TEST PRINT COMMAND AT FECTH STATUS ===

		WHILE @@FETCH_STATUS = 0
		BEGIN
				PRINT	@pop_stock_code + ' ' + convert(varchar(10),@sum_pod_qtyord) --''
			
FETCH NEXT
FROM po_cursor INTO @pop_stock_code, @sum_pod_qtyord

END
CLOSE po_cursor
DEALLOCATE po_cursor
*/
	
	
WHILE @@FETCH_STATUS = 0
BEGIN
												
							DECLARE @od_stock_code varchar(7)
							DECLARE @sum_od_unitcst float
							DECLARE @sum_od_qtyord float
							--DECLARE @od_order_number varchar(10)

							DECLARE od_cursor CURSOR FOR
							SELECT
								--TOP(@sum_pod_qtyord)
								od_stock_code,
								sum(od_unitcst*od_qtyord)
							FROM ord_detail
							WHERE od_stock_code = @pop_stock_code and	
									od_status <> 2 and
									(od_qtyord - od_qtydelvd - od_qtyreserved) > 0
									
							GROUP BY od_stock_code
							ORDER BY od_stock_code
							
							OPEN od_cursor

							FETCH NEXT
							FROM od_cursor INTO @od_stock_code, @sum_od_unitcst
							
								WHILE @@FETCH_STATUS = 0
									BEGIN	
											PRINT @od_stock_code + ' ' + convert(varchar(15),@sum_od_unitcst)

							FETCH NEXT
							FROM od_cursor INTO @od_stock_code, @sum_od_unitcst
							
							END
							CLOSE od_cursor
							DEALLOCATE od_cursor
							
FETCH NEXT
FROM po_cursor INTO @pop_stock_code, @sum_pod_qtyord

END
CLOSE po_cursor
DEALLOCATE po_cursor

Select allOpen in new window

Thanks for that.

I can use my PO order qty for your @total.

What would the syntax be for a running count?

Thanks for the response.
I have now gone back to a crystal report and got that working as required.
I only got my around that after your help here.

Thanks