T-SQL Populate Column Using While Loop

Question

I have the following problem, and SQL isn't my strongest skill yet. I have the following procedure in SQL Server 2014 that works for returning the amount of money in each individual Quote for a Quoting System. Each Quote has Groups, which in turn have Parts in them.:

USE [My_DB_Name]
GO

DECLARE @QuoteNumberID int;  

SELECT @QuoteNumberID = QuoteNumberId FROM [Quote].[Quote]

    WHILE  @QuoteNumberID IS NOT NULL
    BEGIN

                INSERT INTO [Quote].[ZQuoteBackupGL117] (QuoteAmount)(    
                SELECT ISNULL(SUM(ExtendedPrice) ,0) AS QuoteTotal
                FROM(
                        SELECT (Quantity * ((UnitPrice - ISNULL(DollarDiscount, 0)) - 
                        ROUND((((ISNULL(PercentDiscount,0)/100 + ISNULL(CustomerPercentDiscount,0)/100))) * UnitPrice, 2))) AS ExtendedPrice
                        FROM [Quote].[PartGroupPart] p
                          INNER JOIN [Quote].[QuotePartGroup] g ON p.PartGroupID = g.PartGroupID
                          INNER JOIN [Quote].[ZQuoteBackupGL117] q ON g.QuoteID = q.QuoteID 
                        WHERE QuoteNumberId = @QuoteNumberID AND g.IsRecommended = 0 AND g.ExcludeFromTotal = 0 AND (q.GrandTotalValue IS NULL OR q.GrandTotalValue = 0)
                    )tmp)

                    SELECT @QuoteNumberID = MAX(QuoteNumberId) FROM [Quote].[Quote] WHERE @QuoteNumberId > QuoteNumberID
    END

What I'm trying to do is get the value of QuoteTotal and put it in a new field in the [Quote].[ZQuoteBackupGL117] backup table named QuoteAmount(money, allow nulls) in each entry in the table. I will also run it on my production table later. Normally, I would avoid using a loop for this, but this query is only meant to run once to populate QuoteAmount for retroactive Quotes, of which there are thousands. I have looked into using INSERT INTO, but I'm not sure how I am supposed to structure it using this loop. Any help you guys can give me would be appreciated.

clearly you should do this with an insert or an update statement and not a loop. you don't show any place an update or insert statement is being used in your example so I've no idea what you are actually trying to do. — Hogan, Aug 05 '16 at 20:06
@Hogan updated to try to better illustrate what I'm trying to do. — DarthVis17, Aug 05 '16 at 20:46
Does the inner query return multiple rows for a single QuoteNumberID? I.e., are you trying to generate a Quote total across several Parts? — AaronLS, Aug 05 '16 at 20:52
It looks like you are inserting into the same table you are selecting from: `INNER JOIN [Quote].[ZQuoteBackupGL117] q ON g.QuoteID = q.QuoteID` Are you trying to insert a new record seperate from the existing one with same QuoteId, or do you simply want to update the existing records QuoteAmount? — AaronLS, Aug 05 '16 at 20:54
@AaronLS Trying to update existing entries. Yes, the inner query is supposed to generate the total from multiple Parts per one QuoteNumberID, with groups as an intermediary since Parts are associated with Groups and Groups associated with Quotes, but Parts are not directly associated to Quotes. — DarthVis17, Aug 05 '16 at 21:16
insert means insert rows -- if you want to change or add a field that would be an update. — Hogan, Aug 05 '16 at 21:24
@DarthVis17 An update with a `Select .. From ZQuoteBackupGL117 Join ...` It sounds like you need a `group by` as well to sum up a total Quote from multiple parts if those joins are one to many relationships. See this for `Update From...Join` examples: http://stackoverflow.com/questions/1604091/update-a-table-using-join-in-sql-server — AaronLS, Aug 05 '16 at 21:28

score 0 · Accepted Answer · answered Aug 05 '16 at 21:28

You probably want something like this.

UPDATE [Quote].[ZQuoteBackupGL117] 
  SET QuoteAmount = (
    SELECT (Quantity * ((UnitPrice - ISNULL(DollarDiscount, 0)) - ROUND((((ISNULL(PercentDiscount,0)/100 + ISNULL(CustomerPercentDiscount,0)/100))) * UnitPrice, 2))) AS ExtendedPrice
    FROM [Quote].[PartGroupPart] p
    JOIN [Quote].[QuotePartGroup] g ON p.PartGroupID = g.PartGroupID
    JOIN [Quote].[ZQuoteBackupGL117] q ON g.QuoteID = q.QuoteID 
    WHERE QuoteNumberId = @QuoteNumberID AND g.IsRecommended = 0 AND g.ExcludeFromTotal = 0 AND COALESCE(q.GrandTotalValue,0) = 0
)    
WHERE QuoteNumberId = @QuoteNumberID

but I wouldn't bet any money on it.

This only works if there is one part on a Quote, but it is a good start, thank you. — DarthVis17, Aug 16 '16 at 15:11
@DarthVis17 - What do you mean.. what parts is it missing? Do you want a group by on QuoteID in the innner query? — Hogan, Aug 16 '16 at 15:14

T-SQL Populate Column Using While Loop

1 Answers1