从我的商店程序输出学生姓名不应重复 [英] From my store procedure output student name should not repeat

问题描述

存储程序代码如下;

Store procedure code as follows;

 set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[OH_BatchWise_Collection_Report_Presea](@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

set @BillNo = 0
set @pendamt = 0

select @BillNo = bill_no,@pendamt = bill_pend_amt from bill_file2 where cr_bill_no = @CrBillNo and bill_active = 'A'

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 = ''
		set @chqnum= ''

		select @Chqtype = chq_type, @chqnum = chq_num from cheque_file2 where rcpt_no= @Rcptno

     if @Chqtype = 'DEMAND DRAFT'
		set @Chqtype = 'DD'
	 else
		set @Chqtype = @Chqtype
		
		SET @SNo = @SNo + 1

		insert into #TempTable values(@SNo, @stud_id, @stud_name, @Rcptno,@Rcptdt,@RcptAmt,@Chqtype,@chqnum,@pendamt)  --added
    
    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 Student_Id,
       CASE WHEN RowNo =1 THEN [stud_name] ELSE '' END AS [Student_Name],[Rcptno] as Receipt_No, [Rcptdt] as Receipt_Date, [RcptAmt] as Receipt_Amt, [Chqtype] as Payment_Type, [chqnum] as Number,CASE WHEN RowNo =1 THEN CONVERT(VARCHAR(10), pendamt) ELSE '' END AS Pending_Amt
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY sno ORDER BY sno) AS RowNo
    FROM #TempTable   
) AS T
    end

来自我上面的存储过程当我执行输出为如下;



OH_BatchWise_Collection_Report_Presea''B8614

From my above Store Procedure when i execute output as follows;

OH_BatchWise_Collection_Report_Presea''B8614

1 53321     RAYEES. P	  472	30 Apr 2011 	59000.00   100000.00
2 52162     PATIL AVINASH 187	19 Feb 2011 	59000.00   100000.00
3 52169     BENIN JACOB J 189	19 Feb 2011 	59000.00   100000.00
4 51693     VARUN KUMAR   40	25 Jan 2011 	59000.00			
5 51693     VARUN KUMAR  384	16 Apr 2011 	100000.00			

从上面的输出中，我想要输出如下;

From the above output, i want the output as follows;

1 53321     RAYEES. P	  472	30 Apr 2011 	59000.00   100000.00
2 52162     PATIL AVINASH 187	19 Feb 2011 	59000.00   100000.00
3 52169     BENIN JACOB J 189	19 Feb 2011 	59000.00   100000.00
4 51693     VARUN KUMAR   40	25 Jan 2011 	59000.00			
                          384	16 Apr 2011 	100000.00	

为此，每个学生都有两个以上的记录，学生ID和学生姓名不应该重复。



为此我写了以下代码如下;

For that for each and every student have two more records, student id and student name should not repeat.

for that i written a above code as follows;

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 Student_Id,
       CASE WHEN RowNo =1 THEN [stud_name] ELSE '' END AS [Student_Name],[Rcptno] as Receipt_No, [Rcptdt] as Receipt_Date, [RcptAmt] as Receipt_Amt, [Chqtype] as Payment_Type, [chqnum] as Number,CASE WHEN RowNo =1 THEN CONVERT(VARCHAR(10), pendamt) ELSE '' END AS Pending_Amt
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY sno ORDER BY sno) AS RowNo
    FROM #TempTable   
) AS T
    end

上面的代码是正确的吗？请帮助我。



问候，

Narasiman P.

The above code is Correct? please help me.

Regards,
Narasiman P.

推荐答案

是的，代码是对的。你有没有错误。

yes the code is right. do u go any error.

这篇关于从我的商店程序输出学生姓名不应重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！