Avatar of dexterhome
dexterhome
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Sum a order column until a value is reached

I am writing an advanced query and having some issues.
What I now need to do is sum the order quantity column until a set value is reached.


Please advise how I can do this.

This will be running inside a fetch query.

Thanks in advance

Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008

Avatar of undefined
Last Comment
dexterhome

8/22/2022 - Mon
AlokJain0412

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

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?
dexterhome

ASKER
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

Open in new window

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
deighton

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
dexterhome

ASKER
Thanks for that.

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

What would the syntax be for a running count?
dexterhome

ASKER
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