Comments by Shivam Chinna 2022 (Top 7 by date)

Hi CHILL60 I appreciate your time and effort. thank you. Your solution works fine for my desired output. But when I try the same logic in my stored proc I'm not getting the same output. I'll post you my proc can you troubleshoot my mistakes.

alter proc usp_PreviousMonthBankStmt
(
@acid int = 1
)
as
begin
-- set nocount on will remove the sql messages
set nocount on
declare @CustomerName varchar(40)
declare @pid char(2)
declare @brid char(3)
declare @cbalance money

declare @RNO int
declare @DOT datetime
declare @TXN_TYPE char(3)
declare @CHQ_NO int
declare @TXN_AMOUNT money
declare @Running_TXN_AMOUNT money

declare @lastmonth varchar(20)
declare @lastmonthshort varchar(20)
declare @TodaysDate datetime
declare @LastMonthEndDate datetime
set @TodaysDate = GETDATE()
-- get the last month name
select @lastmonth = DATENAME(mm,dateadd(mm,-1,@TodaysDate))
-- get short form of month name
select @lastmonthshort = SUBSTRING(@lastmonth,1,3)
-- gets me last date of previous month
select @LastMonthEndDate = EOMONTH(dateadd(mm,-1,@TodaysDate))

print '----------------------------------------------------------------------------------'
print ' Indian Bank '
print ' List of Transactions from '+@lastmonthshort + ' 1 to '+ convert(varchar,@LastMonthEndDate,107)+ ' Report'
print '----------------------------------------------------------------------------------'

-- 1: Get the customer info

select @CustomerName = name,
@brid = brid,
@pid = pid,
@cbalance = clearbalance
from AccountMaster where acid = @acid

-- 2: Print the variables
Print 'Product Name :'+@pid
Print 'Account No :'+cast(@acid as varchar) +space(10)+' Branch :'+@brid
print 'Customer Name :'+@CustomerName +space(10)+'Clear Balance :'+cast(@cbalance as varchar)+' INR'
print'___________________________________________________________________________________'
print'SL.No DOT TXN_TYPE Cheque_No Amount RunningBalance'
print'___________________________________________________________________________________'

-- 3: Get previous year txns for the given customer
select ROW_NUMBER() over (order by DOT asc) as RNO, DOT, TXN_TYPE, CHQ_NO,
case WHEN TXN_TYPE = 'CW' THEN (-1) * TXN_AMOUNT ELSE TXN_AMOUNT END AS TXN_AMOUNT
into #TxnData
from TransactionMaster
where datediff(YY,dot,getdate()) <= 2 and acid = @acid

-- 4: Print the data from temp table

--select * from #TxnData

-- 5: Loop
declare @x int
set @x = 1
declare @count int
-- Condition
select @count = count(*) from #TxnData

-- loop syntax
while( @x <= @count )
begin

select @RNO = RNO,
@DOT = DOT,
@TXN_TYPE = TXN_TYPE,
@CHQ_NO = CHQ_NO,
@TXN_AMOUNT = TXN_AMOUNT,
--@Running_TXN_AMOUNT = sum(TXN_AMOUNT) over (order by RNO)
from #TxnData where RNO = @x



-- print the data
print cast(@RNO as varchar) + space(8) + convert(varchar, @DOT, 107) + space(7) +
@TXN_TYPE + space(8) + cast(isnull(@CHQ_NO,0) as varchar) + space(8) + cast(@TXN_AMOUNT as varchar)+ space(8)+cast(sum(TXN_AMOUNT) over (order by RNO) as varchar)

-- incr
set @x = @x + 1
end --- loop end
print '____________________________________________________________________________________'
print 'Total no of transactions :' +cast(@count as varchar)
declare @CDs int
select @CDs = count(*) from #TxnData where TXN_TYPE = 'CD'
print ' Cash Deposits :'+ cast(@CDs as varchar)
declare @CWs int
select @CWs = count(*) from #TxnData where TXN_TYPE = 'CW'
print ' Cash Withdrawals :'+ cast(@CWs as varchar)
declare @CQDs int
select @CQDs = count(*) from #TxnData where TXN_TYPE = 'CQD'
print ' Cheque Deposits :'+ cast(@CQDs as varchar)
print '____________________________________________________________________________________'
print '____________________________________________________________________________________'
end
go

e