title: SQL Server Stored Procedure Update with SUM on INNER JOIN
body: <p>I am working in SQL Server 2005, creating a stored procedure to pull payor/billing data from my database.</p>
  
  <p>The stored procedure creates a temp table to pass back to the application (VB.NET).  The first part pulls every record from the period balance table into the temp table and then determines the "age" of the balance for that period (current, 30 days past due, 60 days past due, etc.).  This works fine.</p>
  
  <p>What I now need to do is determine the number of days charged for the current period.  So I pull all records from my temp table where the age is 'CUR'.  The number of days is stored in a separate table in my database called charge.  There may be multiple charge records for one period.  In those cases I want the sum of all of the days for that period.</p>
  
  <p>For example, one person for the period of March 1 - March 31 may have 4 separate charge records: first records has 3 days, second record has 6 days, third record has 1 day, fourth record has 5 days.  What I want in my temp table is just ONE record for this person for this period, with the days value at 15.</p>
  
  <p>Currently the procedure is producing the correct number of days for 3 people (out of 102 people).  82 people have NULL for days.  There are a few people where that would be correct, but definitely not 82 of them.  The remaining people seem to have days that increase randomly.  The number of days cannot be more than the number of days in the month but there are people with the days column set to a number in the hundreds and even in the thousands.</p>
  
  <p>This is related to <a href="https://stackoverflow.com/questions/15929560/sql-server-group-by-sum-by-company-name-balance">the question I posted yesterday</a> but is a different part of the report.</p>
  
  <p>I would appreciate any help you can give.</p>
  
  <pre><code>CREATE PROCEDURE [AgedPayorReport]    
      @VCurrStart     DATETIME
  AS
  BEGIN
      SET NOCOUNT ON;
  
      BEGIN TRY
  
          CREATE TABLE #t_temp
              (                    
                  bal                     DECIMAL(11, 2), 
                  period                  DATETIME,
                  account_code            INT,
                  payor_code              INT,
                  plan_code               INT,        
                  age                     VARCHAR(10),
                  census_days             INT
              )          
  
          DECLARE @VCurrEnd           DATETIME
          DECLARE @V30Start           DATETIME
          DECLARE @V60Start           DATETIME
          DECLARE @V90Start           DATETIME
          DECLARE @V120Start          DATETIME
          DECLARE @V150Start          DATETIME
          DECLARE @V180Start          DATETIME
          DECLARE @V210Start          DATETIME
          DECLARE @V240Start          DATETIME
  
  SET @VCurrEnd = DATEADD(DAY,-1,(DATEADD(MONTH,1,@VCurrStart)))
  SET @V30Start = DATEADD(month,-1,@VCurrStart)
  SET @V60Start = DATEADD(month,-2,@VCurrStart)
  SET @V90Start = DATEADD(month,-3,@VCurrStart)
  SET @V120Start = DATEADD(month,-4,@VCurrStart)
  SET @V150Start = DATEADD(month,-5,@VCurrStart)
  SET @V180Start = DATEADD(month,-6,@VCurrStart)
  SET @V210Start = DATEADD(month,-7,@VCurrStart)
  SET @V240Start = DATEADD(month,-8,@VCurrStart)
  
  INSERT INTO #t_temp
              (                    
                  bal,
                  period,             
                  age
              )            
  SELECT    
                  b.ledger_bal,
                  b.period,               
                  (SELECT CASE 
                  WHEN period &gt; @VCurrStart THEN 'ADV'
                  WHEN period = @VCurrStart THEN 'CUR'
                  WHEN period = @V30Start THEN '30'
                  WHEN period = @V60Start THEN '60'
                  WHEN period = @V90Start THEN '90'
                  WHEN period = @V120Start THEN '120'
                  WHEN period = @V150Start THEN '150'
                  WHEN period = @V180Start THEN '180'
                  WHEN period = @V210Start THEN '210'
                  WHEN period = @V240Start THEN '240'
                  WHEN period &lt; @V240Start THEN '270'
                  END)
              FROM balance b WITH(NOLOCK)         
  
  
  SELECT *
  INTO #t_temp_CUR
  FROM #t_temp
  WHERE age = 'CUR'
  
  
  UPDATE t
  SET t.census_days = (charge.sum_days)
  FROM #t_temp_CUR t
  INNER JOIN 
      (
          SELECT account_code, SUM(days) AS sum_days
          FROM charge WITH(NOLOCK)
          GROUP BY account_code
      ) as charge
      ON charge.account_code = t.account_code
  INNER JOIN charge c WITH(NOLOCK)
  ON c.account_code = t.account_code
  AND c.payor_code = t.payor_code
  AND c.plan_code = t.plan_code
  WHERE c.start_date &gt;= @VCurrStart
  AND c.end_date &lt;= @VCurrEnd
  AND c.type = 'C'
  AND c.void &lt;&gt; 1
  AND c.days &lt;&gt; 0
  AND c.days IS NOT NULL
  
  
  END TRY
      BEGIN CATCH
  
      END CATCH
  END
  </code></pre>
  
  <p>Edited to add sample data:</p>
  
  <p>Existing period balance table:</p>
  
  <pre><code>account_code    payor_code      plan_code       period      ledger_bal  period_bal_code
  ---------------------------------------------------------------------------------------------------------------
  9421            493             224             2012-07-01  191.82      30813
  9420            6833            527             2012-07-01  767.28      30810
  9419            6533            510             2012-07-01  1400.00     30806
  9418            6533            510             2012-07-01  1400.00     30803
  9417            493             224             2012-07-01  959.10      30800
  9416            2193            340             2012-07-01  244.17      30794
  9416            8468            584             2012-07-01  1370.00     30795
  9416            8935            433             2012-07-01  250.00      30798
  9415            493             224             2012-07-01  1150.92     30791
  9414            6158            495             2012-07-01  2916.00     30788
  9413            6833            527             2012-07-01  6385.61     30785
  9412            6034            474             2012-07-01  8488.16     30781
  9412            8930            433             2012-07-01  800.00      30783
  9411            493             224             2012-07-01  7840.18     30777
  9410            8468            584             2012-07-01  6156.00     30776
  </code></pre>
  
  <p>Some corresponding records in the charge table:</p>
  
  <pre><code>account_code    payor_code      plan_code   charge_code start_date  end_date    quantity    gross_amount    net_amount      days    void
  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  9413            6833            527         5070631     2012-07-21  2012-07-31  1           2508.00         6385.61         11      0
  9411            493             224         5070755     2012-07-16  2012-07-29  1           3192.00         6972.28         14      0
  9411            493             224         5070757     2012-07-30  2012-07-31  1           456.00          867.90          2       0
  9416            8468            584         5071869     2012-07-27  2012-07-31  1           1140.00         1620.00         5       0
  9418            6533            510         5072255     2012-07-28  2012-07-31  1           912.00          1400.00         4       0
  9417            493             224         5073818     2012-07-27  2012-07-31  1           1140.00         959.10          5       0
  9410            8468            584         5075878     2012-07-12  2012-07-30  1           4332.00         6156.00         19      0
  9420            6833            527         5076857     2012-07-28  2012-07-31  1           912.00          767.28          4       0
  9412            6034            474         5076991     2012-07-16  2012-07-16  1           228.00          580.51          1       0
  9412            6034            474         5076994     2012-07-17  2012-07-29  1           2964.00         7546.63         13      0
  9412            6034            474         5076997     2012-07-30  2012-07-31  1           456.00          1161.02         2       0
  9421            493             224         5077918     2012-07-31  2012-07-31  1           228.00          191.82          1       0
  9414            6158            495         5079045     2012-07-23  2012-07-31  1           2052.00         2916.00         9       0
  9419            6533            510         5079366     2012-07-28  2012-07-31  1           912.00          1400.00         4       0
  9415            493             224         5079418     2012-07-26  2012-07-31  1           1368.00         1150.92         6       0
  9410            8468            584         5084838     2012-07-12  2012-07-30  1           -4332.00        -6156.00        19      0
  9410            8468            584         5085112     2012-07-12  2012-07-30  1           4332.00         6156.00         19      0
  9413            6833            527         5085371     2012-08-01  2012-08-03  1           684.00          1741.53         3       0
  9413            6833            527         5085373     2012-08-04  2012-08-09  1           1368.00         1150.92         6       0
  9413            6833            527         5085375     2012-08-10  2012-08-31  1           5016.00         4220.04         22      0
  </code></pre>
  
  <p>So take account # 9411.  Current stored procedure produces:</p>
  
  <pre><code>ledger_bal  period      account_code    payor_code      plan_code       age     census_days
  ------------------------------------------------------------------------------------------------
  7840.18     2012-07-01  9411            493             224             CUR         70
  </code></pre>
  
  <p>But I want it to produce:</p>
  
  <pre><code>ledger_bal  period      account_code    payor_code      plan_code       age     census_days
      ------------------------------------------------------------------------------------------------
  7840.18     2012-07-01  9411            493             224             CUR         16
  </code></pre>