from my store procedure i want the pending amt should be come to last rows for each student
Store Procedure code as follows;
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[OH_BatchWise_Collection_Report](@BatchId varchar(10))as
begin
declare @SNo int,
@stud_name varchar(100),
@stud_id varchar(100),
@CrBillNo varchar(20),
@BillNo varchar(20),
@Rcptno varchar(20),
@Rcptdt varchar(20),
@RcptAmt varchar(20),
@Chqtype varchar(20),
@chqnum varchar(20),
@pendamt varchar(20)
create table #TempTable(SNo int, Stud_ID varchar(10), Stud_Name varchar(100),
Rcptno varchar(20),Rcptdt varchar(20), RcptAmt varchar(20),Chqtype varchar(20),chqnum varchar(20),pendamt varchar(20))
declare Batchwise cursor for
select s.stud_id, s.stud_name, cr.cr_bill_no from course_registration cr, batch_course_registration bcr, student s
where cr.stud_id = s.stud_id and bcr.cr_bill_no = cr.cr_bill_no and cr.cr_active = 'A'
and bcr.bcr_batch_id = @BatchId
SET @SNo = 0
open Batchwise
fetch next from Batchwise into @stud_id, @stud_name, @CrBillNo
While @@Fetch_status = 0
begin
select @BillNo = bill_no,@pendamt = bill_pend_amt from bill_file2 where cr_bill_no = @CrBillNo and bill_active = 'A'
SET @SNo = @SNo + 1
declare Batchwise_cur cursor for
select r.rcpt_no, convert(char(12),r.rcpt_dt,106) as Rcptdt, r.rcpt_amt from receipt_file2 r where r.bill_no = @BillNo
open Batchwise_cur
fetch next from Batchwise_cur into @Rcptno, @Rcptdt, @RcptAmt
while @@Fetch_status = 0
begin
set @Chqtype = ''
select @Chqtype = chq_type,@chqnum = chq_num from cheque_file2 where rcpt_no= @Rcptno
insert into #TempTable values(@SNo, @stud_id, @stud_name, @Rcptno,@Rcptdt,@RcptAmt,@Chqtype,@chqnum,@pendamt)
fetch next from Batchwise_cur into @Rcptno, @Rcptdt, @RcptAmt
end
close Batchwise_cur
deallocate Batchwise_cur
fetch next from Batchwise into @stud_id, @stud_name, @CrBillNo
end
close Batchwise
deallocate Batchwise
SELECT CASE WHEN RowNo =1 THEN CONVERT(VARCHAR(10), sno) ELSE '' END AS sno, CASE WHEN RowNo =1 THEN CONVERT(VARCHAR(10), stud_id) ELSE '' END AS stu_id,
CASE WHEN RowNo =1 THEN [stud_name] ELSE '' END AS [stud_name],[Rcptno], [Rcptdt], [RcptAmt], [Chqtype], [chqnum],CASE WHEN RowNo =2 THEN CONVERT(VARCHAR(10), pendamt) ELSE '' END AS pendamt
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY sno ORDER BY sno) AS RowNo
FROM #TempTable
) AS T
end
exec [OH_BatchWise_Collection_Report] 'B10720'
i written the above store procedure after executing the store procedure output as follows;
158172 RALPH BENNET .R 1572 21 Apr 2012 100000.00DD 264287
1573 21 Apr 2012 59000.00DD 875452 0.00
258173 RAM 1564 21 Apr 2012 100000.00DD 256107
1553 21 Apr 2012 59000.00DD 895150 0.00
358166 BHARATH .B 1565 21 Apr 2012 10000.00DD 067935
1566 21 Apr 2012 49000.00DD 067936 0.00
197026 Jun 2012 9000.00DD 900671
197126 Jun 2012 21000.00DD 900672
208823 Jul 2012 30000.00DD 902109
458282 UPPADA 1643 30 Apr 2012 19000.00DD 102280
2037 13 Jul 2012 40000.00DD 937468 0.00
1901 24 Jun 2012 100000.00DD 699377
from the above output i want the output as follows;
158172 RALPH BENNET .R 1572 21 Apr 2012 100000.00DD 264287
1573 21 Apr 2012 59000.00DD 875452 0.00
258173 RAM 1564 21 Apr 2012 100000.00DD 256107
1553 21 Apr 2012 59000.00DD 895150 0.00
358166 BHARATH .B 1565 21 Apr 2012 10000.00DD 067935
1566 21 Apr 2012 49000.00DD 067936
197026 Jun 2012 9000.00DD 900671
197126 Jun 2012 21000.00DD 900672
208823 Jul 2012 30000.00DD 902109 0.00
458282 UPPADA 1643 30 Apr 2012 19000.00DD 102280
2037 13 Jul 2012 40000.00DD 937468
1901 24 Jun 2012 100000.00DD 699377 0.00
for getting the above output in the store procedure code what code i have to written to get the above output.
how can i do? please help me.
Regards,
Rao.