narasiman rao

narasiman rao

  • NA
  • 519
  • 768.7k

from my SP i want pending amt should be come tolast rows

Apr 18 2013 8:36 AM

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.