title: Trouble creating SQL Query to update line item amounts
body: <p>I'm going to preface this question with the disclaimer that creating what I call "complex" queries isn't in my forte. Most of the time, there is a much simpler way to accomplish what I'm trying to accomplish so if the below query isn't up to par, I apologize.</p>
  
  <p>With that said, I have a table that keeps track of Vendor Invoices and Vendor Invoice Items (along with a Vendor Invoice Type and Vendor Invoice Item Type). Our bookkeeper wants a report that simply shows: Vendor | Location | Inv Number | Inv Type | Item Type | Inv Date | Rental Fee | Restock Fee | Shipping Fee | Line Item Cost | Total (Line Item + Fees)</p>
  
  <p>Most of the time, one vendor invoice is one line. However, there are exceptions where a vendor invoice can have many item types, thus creating two rows. Not a big deal EXCEPT the fees (Rental, Restock, Shipping) are attached to the Vendor Invoice table. So, I first created a query that checks the temp table for Invoices that have multiple rows, takes the last row, and zero's out the fees. So that only one line item would have the fee. However, our bookkeeper doesn't like that. Instead, she'd like the fees to be "distributed" among the line items. </p>
  
  <p>So, if a vendor invoice has a $25 shipping charge, has two line items, then each line item would be $12.50.</p>
  
  <p>After working with the query, I got it to update the Last Row to be the adjusted amount but row 1+ would have the original amount.</p>
  
  <p>I'm going to post my entire query here (again - I'm sorry that this may not be the best looking query; however, suggestions are always welcome)</p>
  
  <pre><code>DROP TABLE #tVendorInvoiceReport
  DROP TABLE #tSummary
  
  SELECT  v.Name AS Vendor ,
      vii.Location ,
      vi.VendorInvNumber ,
      vit.Descr AS InvoiceType ,
      vii.VendorInvoiceItemType ,
      CONVERT(VARCHAR(10), vi.VendorInvDate, 120) VendorInvDate ,
      vi.RentalFee ,
      vi.RestockFee ,
      vi.ShippingFee ,
      SUM(vii.TotalUnitCost) TotalItemCost ,
      CONVERT(MONEY, 0) TotalInvoice  ,
      RowID = IDENTITY( INT,1,1)
  INTO    #tVendorInvoiceReport
  FROM    dbo.vVendorInvoiceItems AS vii
      JOIN dbo.VendorInvoices AS vi ON vii.VendorInvID = vi.VendorInvID
      JOIN dbo.Vendors AS v ON vi.VendorID = v.VendorID
      JOIN dbo.VendorInvoiceTypes AS vit ON vi.VendorInvTypeID = vit.VendorInvTypeID
  WHERE   vi.VendorInvDate &gt;= '2012-01-01'
      AND vi.VendorInvDate &lt;= '2012-01-31'
  GROUP BY v.Name ,
      vii.Location ,
      vi.VendorInvNumber ,
      vit.Descr ,
      vii.VendorInvoiceItemType ,
      CONVERT(VARCHAR(10), vi.VendorInvDate, 120) ,
      vi.RentalFee ,
      vi.RestockFee ,
      vi.ShippingFee
  ORDER BY v.Name ,
      vii.Location ,
      vi.VendorInvNumber ,
      vit.Descr ,
      vii.VendorInvoiceItemType ,
      CONVERT(VARCHAR(10), vi.VendorInvDate, 120)
  
  
  SELECT  VendorInvNumber ,
      COUNT(RowID) TotalLines ,
      MAX(RowID) LastLine
  INTO    #tSummary
  FROM    #tVendorInvoiceReport
  GROUP BY VendorInvNumber
  
  WHILE ( SELECT  COUNT(LastLine)
      FROM    #tSummary AS ts
      WHERE   TotalLines &gt; 1
    ) &gt; 0 
  BEGIN
      DECLARE @LastLine INT
      DECLARE @NumItems INT
  
      SET @LastLine = ( SELECT    MAX(LastLine)
                        FROM      #tSummary AS ts
                        WHERE     TotalLines &gt; 1
                      )
  
      SET @NumItems = ( SELECT    COUNT(VendorInvNumber)
                        FROM      #tVendorInvoiceReport
                        WHERE     VendorInvNumber IN (
                                  SELECT  VendorInvNumber
                                  FROM    #tSummary
                                  WHERE   LastLine = @LastLine )
                      ) 
  
      UPDATE  #tVendorInvoiceReport
      SET     RentalFee = ( RentalFee / @NumItems ) ,
              RestockFee = ( RestockFee / @NumItems ) ,
              ShippingFee = ( ShippingFee / @NumItems )
      WHERE   RowID = @LastLine
  
      DELETE  FROM #tSummary
      WHERE   LastLine = @LastLine
  
      --PRINT @NumItems
  END
  
  UPDATE  #tVendorInvoiceReport
  SET     TotalInvoice = ( TotalItemCost + RentalFee + RestockFee + ShippingFee )
  
  SELECT  Vendor ,
      Location ,
      VendorInvNumber ,
      InvoiceType ,
      VendorInvoiceItemType ,
      VendorInvDate ,
      RentalFee ,
      RestockFee ,
      ShippingFee ,
      TotalItemCost ,
      TotalInvoice
  FROM    #tVendorInvoiceReport AS tvir
  </code></pre>
  
  <p>I sincerely appreciate anyone who took the time to read this and attempt to point me in the right direction.</p>
  
  <p>Thank you,
  Andrew</p>
  
  <p>PS - I did try and remove "WHERE RowID = @LastLine" from the first Update, but that changed the Shipping Fees for the first line with two items to "0.0868" instead of 12.50 ($25/2)</p>