ALTER PROCEDURE UpdateExpenditure 
AS 
  SET NOCOUNT  ON 
   
  DECLARE  @income_code    INT, 
           @totalcost      MONEY, 
           @eyeglass       MONEY, 
           @expenditure    MONEY, 
           @balance        MONEY, 
           @rCount         INT, 
           @rPos           INT, 
           @outsideRecords INT 
   
  DECLARE income_cursor CURSOR  FOR 
  SELECT   tabEGPurchase.Code 
  FROM     tabEGPurchase 
           INNER JOIN tabEGPayments 
             ON tabEGPurchase.Code = tabEGPayments.Code 
           INNER JOIN tabEarningRecords 
             ON tabEGPayments.IncomeCode = tabEarningRecords.SEQUENCE 
           LEFT OUTER JOIN tabFrameList 
             ON tabEGPurchase.EyeglassCode = tabFrameList.ControlCode 
  WHERE    (DATEDIFF(MONTH,tabEarningRecords.DateEarned,GETDATE()) > 0) 
  GROUP BY tabEGPurchase.Code 
   
  BEGIN TRAN --Start transaction 
   
  UPDATE tabEarningRecords 
  SET    Expenditure = 0 
  WHERE  (SEQUENCE IN (SELECT tabEarningRecords.SEQUENCE 
                       FROM   tabEGPurchase 
                              INNER JOIN tabEGPayments 
                                ON tabEGPurchase.Code = tabEGPayments.Code 
                              INNER JOIN tabEarningRecords 
                                ON tabEGPayments.IncomeCode = tabEarningRecords.SEQUENCE)) 
   
  --Check if an error occured, if yes then Rollback and Exit 
  IF (@@ERROR <> 0) 
    BEGIN 
      ROLLBACK TRAN 
       
      PRINT 'Error Encountered' 
       
      RETURN (99) 
    END 
   
  OPEN income_cursor 
   
  FETCH NEXT FROM income_cursor 
  INTO @income_code 
   
  WHILE @@FETCH_STATUS = 0 
    BEGIN 
      --Get Total Cost 
      SELECT @totalcost = ISNULL(tabFrameList.BuyingPrice,0) + ISNULL(tabEGPurchase.MTC,0) 
      FROM   tabEGPurchase 
             LEFT OUTER JOIN tabFrameList 
               ON tabEGPurchase.EyeglassCode = tabFrameList.ControlCode 
      WHERE  tabEGPurchase.Code = @income_code 
       
      --Get Balance 
      SELECT @balance = (tabEGPurchase.EyeglassPrice - tabEGPurchase.Discount - SUM(tabEGPayments.PaymentAmount)) 
      FROM     tabEGPurchase 
               INNER JOIN tabEGPayments 
                 ON tabEGPurchase.Code = tabEGPayments.Code 
      WHERE    tabEGPurchase.Code = @income_code 
      GROUP BY tabEGPurchase.EyeglassPrice, 
               tabEGPurchase.Discount 
       
      --Get Recordcount 
      SELECT @rCount = Count(tabEarningRecords.EyeGlass) 
      FROM   tabEGPurchase 
             INNER JOIN tabEGPayments 
               ON tabEGPurchase.Code = tabEGPayments.Code 
             INNER JOIN tabEarningRecords 
               ON tabEGPayments.IncomeCode = tabEarningRecords.SEQUENCE 
             LEFT OUTER JOIN tabFrameList 
               ON tabEGPurchase.EyeglassCode = tabFrameList.ControlCode 
      WHERE  (DateDiff(MONTH,tabEarningRecords.DateEarned,GETDATE()) > 0) 
             AND (tabEGPurchase.Code = @income_code) 
       
      PRINT 'Recordcount: ' + Convert(NVARCHAR,@rCount) 
       
      --Get records that do not match conditions 
      SELECT @outsideRecords = COUNT(tabEGPurchase.Code) 
      FROM   tabEarningRecords 
             INNER JOIN tabEGPayments 
               ON tabEarningRecords.SEQUENCE = tabEGPayments.IncomeCode 
             INNER JOIN tabEGPurchase 
               ON tabEGPayments.Code = tabEGPurchase.Code 
      WHERE  (DATEDIFF(MONTH,tabEarningRecords.DateEarned,GETDATE()) = 0) 
             AND (tabEGPurchase.Code = @income_code) 
       
      SET @rPos = 1 
       
      DECLARE income2_cursor CURSOR  FOR 
      SELECT tabEarningRecords.EyeGlass 
      FROM   tabEGPurchase 
             INNER JOIN tabEGPayments 
               ON tabEGPurchase.Code = tabEGPayments.Code 
             INNER JOIN tabEarningRecords 
               ON tabEGPayments.IncomeCode = tabEarningRecords.SEQUENCE 
             LEFT OUTER JOIN tabFrameList 
               ON tabEGPurchase.EyeglassCode = tabFrameList.ControlCode 
      WHERE  (DateDiff(MONTH,tabEarningRecords.DateEarned,GETDATE()) > 0) 
             AND (tabEGPurchase.Code = @income_code) 
       
      OPEN income2_cursor 
       
      FETCH NEXT FROM income2_cursor 
      INTO @eyeglass 
       
      WHILE @@FETCH_STATUS = 0 
        BEGIN 
          IF (@totalcost >= @eyeglass) 
            BEGIN 
              SET @totalcost = @totalcost - @eyeglass 
               
              IF @rPos < @rCount 
                BEGIN 
                  SET @expenditure = @eyeglass 
                END 
              ELSE 
                IF @balance = 0 
                  BEGIN 
                    IF @outsideRecords = 0 
                      SET @expenditure = @eyeglass + @totalcost 
                    ELSE 
                      SET @expenditure = @eyeglass 
                  END 
                ELSE 
                  BEGIN 
                    SET @expenditure = @eyeglass 
                  END 
            END 
          ELSE 
            BEGIN 
              SET @expenditure = @totalcost 
               
              SET @totalcost = 0 
            END 
           
          UPDATE tabEarningRecords 
          SET    Expenditure = @expenditure 
          WHERE CURRENT OF income2_cursor 
           
          --Check if an error occured, if yes then Rollback and Exit 
          IF (@@ERROR <> 0) 
            BEGIN 
              ROLLBACK TRAN 
               
              PRINT 'Error Encountered' 
               
              RETURN (99) 
            END 
           
          PRINT 'eyeglass    : ' + Convert(NVARCHAR,@eyeglass) 
           
          PRINT 'expenditure : ' + Convert(NVARCHAR,@expenditure) 
           
          PRINT 'remaining   : ' + Convert(NVARCHAR,@totalcost) 
           
          PRINT '' 
           
          IF (@totalcost = 0) 
            BREAK 
           
          FETCH NEXT FROM income2_cursor 
          INTO @eyeglass 
           
          SET @rPos = @rPos + 1 
        END 
       
      CLOSE income2_cursor 
       
      DEALLOCATE income2_cursor 
       
      -- Get the next author. 
      FETCH NEXT FROM income_cursor 
      INTO @income_code 
    END 
   
  CLOSE income_cursor 
   
  DEALLOCATE income_cursor 
   
  COMMIT TRAN --Commit transaction 

GO