if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Statement]') 
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Statement]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


Create Procedure Statement 
  As
Begin

declare @Startdate 	    As datetime
declare @Enddate 	    As datetime

declare @Customer_No        As char(15)
declare @loanno             As char(15)

declare @transaction_date   As datetime
declare @transaction_type   As char(3)
declare @reference       As varchar(20)
declare @notes   	    As varchar(255)
declare @transaction_amount As decimal (9,2)
declare @transaction_description As varchar(50)

declare @debit_amount       As decimal (9,2)
declare @credit_amount      As decimal (9,2)
declare @counter            As int
declare @balance            As decimal (9,2)
declare @user_changed As char(8)


  declare c2 CURSOR FOR
    SELECT loan_no
    FROM   loan
    where  customer_no = @Customer_No
    ORDER BY loan_no

  OPEN c2
  FETCH NEXT FROM c2 INTO @loanno

  -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
  WHILE @@FETCH_STATUS = 0
  BEGIN

    declare c1 CURSOR FOR
      SELECT transaction_record.loan_no,
	     transaction_record.transaction_date,
	     transaction_record.transaction_type,
	     transaction_record.reference_no,
	     transaction_record.notes,
	     transaction_record.transaction_amount,
	     transaction_type.[description]
      FROM   transaction_record
      inner  join transaction_type 
                                 on transaction_type.transaction_type = transaction_record.transaction_type
      where  loan_no = @loanno and transaction_Date between @startdate and @enddate and transaction_amount <> 0
      ORDER BY transaction_date

    OPEN c1
    FETCH NEXT FROM c1 
 	INTO @transaction_date,
	     @transaction_type,
             @reference,
             @notes,
             @transaction_amount,
             @transaction_description

    -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
    WHILE @@FETCH_STATUS = 0
    BEGIN
      If (@transaction_amount < 0)
      Begin
        set @credit_amount = @transaction_amount
        set @debit_amount = 0
      End
      Else
      Begin
        set @debit_amount = @transaction_amount
        set @credit_amount = 0
      End

      If (@counter = 0)
      Begin
        set @balance = @transaction_amount
      End
      Else
      Begin
        set @balance = @balance + @transaction_amount
      End

      insert into Statement (customer_no,loan_no,
                  transaction_date,transaction_type,transaction_description,
                  reference, notes, debit_amount, credit_amount, balance,
                  user_changed, transaction_no)
      
     Values (@Customer_No,
             @loanno,
             @transaction_date,
             @transaction_type,
             @transaction_description,
             @reference,
             @notes,
             @debit_amount,
             @credit_amount,
             @balance,
             @user_changed,
             @counter)

      set @counter = @counter+1

      FETCH NEXT FROM c1 
                      INTO @transaction_date,