SUM rows where value = DT 1

[craigward]

wHi There,

I hit an issue today that I am scratching my head with, any guidance would be much appreciated.

I am trying to come up with a query that SUMS all the rows 'Total_After_Discount' field values above and including when the row where the value in the quote_print = 'DT is found and for the sub_total field on the 'DT' row to be populated with the value of the lines above including the DT line. So in the example, row_no 1,2,3,4 add the Total_After_Discount values for these rows together and set line 4's subtotal with the value, in this example 563.

Then from row_no 5 to the next rows where the quote_print value = 'DT' row_no 9 in the example do the same. So the sub_total field for row_no 9 would be set to 480.

So every time an instance of quote_print = 'DT' stop add this line and all lines above's Total_After_Discount values and set the DT row sub_total with the calculation.

I hope that makes sense!

I have this example query that returns the following (see screenshot). Thanks for looking and any help you provide.

SELECT rank() OVER (ORDER BY Sub_Quote_Code, Sort_Order) as row_no , quote_print, component, Total_After_Discount, Sub_Total, Sub_Quote_Code, Sort_Order
  FROM [Almanzora_Site].[dbo].[wce_quote_items] where Quote_No = '2959' order by Sub_Quote_Code, Sort_Order

 

[DaveInIowa]

Here's an attempt without using a cursor...

IF (OBJECT_ID('tempdb..#TableWithRowAndGroup', 'U') IS NOT NULL) DROP TABLE #TableWithRowAndGroup

SELECT ROW_NUMBER() OVER (ORDER BY Sub_Quote_Code, Sort_Order) RowNo, CAST(NULL AS INT) GroupNo, *
  INTO #TableWithRowAndGroup
  FROM @Table

UPDATE twrg
   SET GroupNo = g.GroupNo
  FROM #TableWithRowAndGroup twrg
 CROSS
 APPLY (SELECT MIN(RowNo) GroupNo
		  FROM #TableWithRowAndGroup
		 WHERE quote_print = 'DT'
		   AND Sub_Quote_Code >= twrg.Sub_Quote_Code
		   AND Sort_Order >= twrg.Sort_Order) g

;WITH summary AS (
SELECT GroupNo, SUM(Total_After_Discount) SumTotal_After_Discount
  FROM #TableWithRowAndGroup
 GROUP BY GroupNo
)

UPDATE t
   SET Sub_Total = s.SumTotal_After_Discount
  FROM @Table t
  JOIN #TableWithRowAndGroup twrg
    ON t.quote_print = twrg.quote_print
   AND t.Sub_Quote_Code = twrg.Sub_Quote_Code
   AND t.Sort_Order = twrg.Sort_Order
  JOIN summary s
    ON twrg.GroupNo = s.GroupNo
 WHERE t.quote_print = 'DT'

SELECT *
  FROM @Table
 ORDER BY Sub_Quote_Code, Sort_Order

But for readability, it's probably better to process using a cursor...

DECLARE
	@quote_print			CHAR(2),
	@Total_After_Discount	DEC(10,2),
	@Sub_Total				DEC(10,2),
	@Sub_Quote_Code			CHAR(2),
	@Sort_Order				INT

DECLARE csr CURSOR FOR
SELECT quote_print, Total_After_Discount, Sub_Quote_Code, Sort_Order
  FROM @Table
 ORDER BY Sub_Quote_Code, Sort_Order
	  
OPEN csr
FETCH NEXT FROM csr INTO @quote_print, @Total_After_Discount, @Sub_Quote_Code, @Sort_Order
	
SET @Sub_Total = 0.00 
WHILE (@@FETCH_STATUS = 0) BEGIN

	SET @Sub_Total += COALESCE(@Total_After_Discount, 0.00)
	
	IF (@quote_print = 'DT') BEGIN	
		UPDATE @Table
		   SET Sub_Total = @Sub_Total
		 WHERE Sub_Quote_Code = @Sub_Quote_Code
		   AND Sort_Order = @Sort_Order

		SET @Sub_Total = 0.00
	END
	
	FETCH NEXT FROM csr INTO @quote_print, @Total_After_Discount, @Sub_Quote_Code, @Sort_Order
END
CLOSE csr
DEALLOCATE csr

SELECT *
  FROM @Table
 ORDER BY Sub_Quote_Code, Sort_Order

 

[craigward]

Thanks Dave, that is great, thank you for your help!

Can I trouble you with another question. I have applied your script to a particular quote number '2959' so it does the math for that quotes line items. The table has many quotes in it and new quotes (with unique quote numbers) added daily. Is there a way to add something so that id does the logic you have designed quote number specific. So each quote number (many line items per unique quote number) has the calculation done based on each quote number? It works when I specify the quote number but I can't manually do this for each quote.

Thank you again for your input, I will try and figure this out but if you have any ideas that would be really helpful, thanks again.

SELECT quote_print, Total_After_Discount, Sub_Quote_Code, Sort_Order
  FROM wce_quote_items where quote_no = '2959'
 ORDER BY Sub_Quote_Code, Sort_Order

DECLARE
	@quote_print			CHAR(2),
	@Total_After_Discount	DEC(10,2),
	@Sub_Total				DEC(10,2),
	@Sub_Quote_Code			CHAR(2),
	@Sort_Order				INT


DECLARE csr CURSOR FOR
SELECT quote_print, Total_After_Discount, Sub_Quote_Code, Sort_Order
  FROM wce_quote_items where quote_no = '2959'
 ORDER BY Sub_Quote_Code, Sort_Order
	  
OPEN csr
FETCH NEXT FROM csr INTO @quote_print, @Total_After_Discount, @Sub_Quote_Code, @Sort_Order
	
SET @Sub_Total = 0.00 
WHILE (@@FETCH_STATUS = 0) BEGIN

	SET @Sub_Total += COALESCE(@Total_After_Discount, 0.00)
	
	IF (@quote_print = 'DT') BEGIN	
		UPDATE wce_quote_items
		   SET Sub_Total = @Sub_Total
		 WHERE Sub_Quote_Code = @Sub_Quote_Code
		   AND Sort_Order = @Sort_Order

		SET @Sub_Total = 0.00
	END
	
	FETCH NEXT FROM csr INTO @quote_print, @Total_After_Discount, @Sub_Quote_Code, @Sort_Order
END
CLOSE csr
DEALLOCATE csr

SELECT quote_print, component, Total_After_Discount,Sub_Total, Sub_Quote_Code
  FROM wce_quote_items where Quote_No = '2959'
 ORDER BY Sub_Quote_Code, Sort_Order

 

[DaveInIowa]

Could you process all unprocessed quotes like this?

DECLARE @UnprocessedQuotes TABLE (quote_no VARCHAR(16))
INSERT INTO @UnprocessedQuotes
SELECT DISTINCT quote_no
  FROM @Table
 WHERE quote_print = 'DT'
   AND Sub_Total IS NULL

DECLARE csr CURSOR FOR
SELECT quote_print, Total_After_Discount, Sub_Quote_Code, Sort_Order
  FROM wce_quote_items
 WHERE quote_no IN (SELECT quote_no FROM @UnprocessedQuotes)
 ORDER BY quote_no, Sub_Quote_Code, Sort_Order

 

[craigward]

Thanks for your reply.

So when I run this I see in the @UnprocessedQuotes table the quote number 2959. Are you saying integrate this with the script you created that sets the values as I need them in the sub_total fields?