How to find the Balances from Total Deposit with transaction wise adjustment done using Store Procedure in Sql server 2000

• Tshewang Dendup-467210

  Newbie

  Points: 4

  More actions

  March 31, 2009 at 10:15 pm

  #97439

  drop table #AdjustmentAdvance

  CREATE TABLE #AdjustmentAdvance (

  AdjustmentID varchar(20), AdvanceDepositID varchar(20), ReceiptID varchar(12),

  BookNo varchar(15), PageNo varchar(3),BSTAdvanceDeposit money, BSTAmountAdjusted money,

  RunningTotal money,AdjustmentDate smalldatetime

  )

  --[ImporterBalanceAdjustments]

  DECLARE

  @AdjustmentID varchar(20),

  @AdvanceDepositID varchar(20),

  @ReceiptID varchar(12),

  @BookNo varchar(15),

  @PageNo varchar(3),

  @BSTAdvanceDeposit money,

  @BSTAmountAdjusted money,

  @RunningTotal money,

  @AdjustmentDate smalldatetime

  SET @RunningTotal =0 --(SELECT receiptAmount FROM IMKReceipt1 where (ReceiptId = 'PG1830183'))

  DECLARE rt_cursor CURSOR

  FOR

  SELECT

  I.AdjustmentID,

  I.AdvanceDepositID,

  I.ReceiptID,

  I.BookNo,

  I.PageNo,

  A.BSTAdvanceDeposit,

  I.BSTAmountAdjusted,

  I.AdjustmentDate

  FROM ImporterBalanceAdjustments I

  INNER JOIN AdvanceDepositT A ON I.AdvanceDepositId = A.AdvanceDepositId

  where year(AdjustmentDate)=2008 and BSTAmountAdjusted is not null and Receiptid='PG1830183'

  OPEN rt_cursor

  FETCH NEXT FROM rt_cursor INTO @AdjustmentID,@AdvanceDepositID,@ReceiptID,@BookNo,@PageNo,@BSTAdvanceDeposit,@BSTAmountAdjusted,@AdjustmentDate

  WHILE @@FETCH_STATUS = 0

  BEGIN

  SET @RunningTotal = @RunningTotal - @BSTAmountAdjusted

  INSERT #AdjustmentAdvance VALUES (

  @AdjustmentID,@AdvanceDepositID,@ReceiptID,@BookNo,@PageNo,@BSTAdvanceDeposit,@BSTAmountAdjusted,@RunningTotal,@AdjustmentDate

  )

  FETCH NEXT FROM rt_cursor INTO @AdjustmentID,@AdvanceDepositID,@ReceiptID,@BookNo,@PageNo,@BSTAdvanceDeposit,@BSTAmountAdjusted,@AdjustmentDate

  END

  CLOSE rt_cursor

  DEALLOCATE rt_cursor

  SELECT * FROM #AdjustmentAdvance ORDER BY adjustmentDate

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  April 1, 2009 at 12:52 am

  #969645

  You are in the wrong forum. This is a SQL Server 2005 forum.

  [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
  Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]