Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 1 of 1
  1. #1
    New Coder
    Join Date
    Dec 2011
    Posts
    29
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Fee intimation due list

    SIR I AM USING SQL SERVER 2005 IN THAT I GOT 3 LOGICAL ERRORS WHILE I AM USING FIVE TABLES 1.STUDENTINFO,2.TBL TERM FEE,3.TBL FEE,4.TBL BUS FEE, 5.TBL TREA FEE FROM THE ABOVE FIVE TABLE I RETRIVED THE STUDEN_ID,STUDENT_CODE,STUDENT NAME,FATHER NAME,TERM FEE TYPE ID,TERM FEE AMOUNT,TERM FEE PAID AMOUNT,DUE AMOUNT,BUS FEE ID,ESCRIPTION,BUS FEE AMOUNMT,PAID AMOUNT AND DUE AMOUNT.BY USING THE FOLLOWING CODE I GOT THE DATA PROPERLY EXCEPT TERM FEE PAID AMOUNT AND BUS FEE PAID AMOUNT AND THIRD ONE IS WHENEVER I PAID TERM FEE THAT PERSON BUJS FEE DUE AMOUNT NOT DISPLAYED



    THE CODE IS SHOWN BELOW






    Code:
    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    go
    
    
    
    
    
    
    
    
    ALTER procedure [dbo].[sp_prashanth]
    	@intClassId int,    
    	@intSectionId int,
    	@strFeeTypeIds varchar(1000),
    	@strMonthIds varchar(500)
    	      
    AS    
    	BEGIN    
    		DECLARE @intFYID int
    --		DECLARE @fAmount FLOAT
    		   
    		SELECT @intFYID = FY_ID FROM TBLFISCALYEARS WHERE ACTIVE = 1    
    		SELECT     
    			Student_Id, SRC.Student_Code, SRC.FULLNAME, SRC.FATHERNAME, 
    			SRC.FEETYPE_ID TERMFEETYPE_ID,
    --			(SELECT Description from tblTermFeeTypes where FEETYPE_ID=SRC.FEETYPE_ID)DESCRIPTION,(SELECT Amount from tblTermFeeTypes  where FEETYPE_ID=SRC.FEETYPE_ID)AMOUNT,
    			SRC.DESCRIPTION,SRC.TERMFEE_AMOUNT,
    			SRC.TERMFEE_PAID_AMOUNT PAID_AMOUNT,ISNULL(SRC.TERMFEE_AMOUNT,0) -ISNULL(SRC.TERMFEE_PAID_AMOUNT,0) DUE_AMOUNT,SRC.MONTH_ID FEETYPE_ID,
    			(SELECT MONTH_NAME FROM TBLMONTHS WHERE MONTH_ID = SRC.MONTH_ID AND FY_ID = @intFYID) + 'Bus Fee' DESCRIPTION ,
    			TAF.BusFee AMOUNT, SRC.PAID_AMOUNT, TAF.BusFee - SRC.PAID_AMOUNT DUE_AMOUNT
    		FROM     
    		(    
    			SELECT    
    				TSI.Student_Code, TSI.FIRSTNAME + ' ' + TSI.LASTNAME FULLNAME, TSI.FATHERNAME,TFT.FEETYPE_ID,TFT.DESCRIPTION ,TFT.AMOUNT TERMFEE_AMOUNT,SUM(TF.AMOUNT) TERMFEE_PAID_AMOUNT,TBF.Student_Id,TBF.MONTH_ID,
    				TBF.AREA_ID, SUM(TBF.AMOUNT) PAID_AMOUNT    
    			FROM    
    				TBLBUSFEE TBF,
    				TBLSTUDENTINFO TSI,
    				tblFee TF,
    				tblTermFeeTypes TFT
    				
    			WHERE     
    --				(ISNULL (TFT.AMOUNT,0) -ISNULL (TF.AMOUNT,0)) <> 0
    			     
    				TSI.CLASS_ID = @intClassId  AND TSI.SECTION_ID = @intSectionId
    				AND TSI.DELETE_FLAG = 0 
    				AND TSI.FY_ID = @intFYID  AND TFT.FY_ID = @intFYID
    				AND TFT.CLASS_ID = @intClassId AND TFT.FY_ID = @intFYID
    				AND TFT.FEETYPE_ID IN (SELECT VALUE FROM dbo.fn_split(@strFeeTypeIds,',')) 
    				AND TF.CLASS_ID = @intClassId AND TF.SECTION_ID = @intSectionId  AND TF.FY_ID = @intFYID
    				AND TF.FEETYPE_ID IN (SELECT VALUE FROM dbo.fn_split(@strFeeTypeIds,',')) 
    				AND TBF.CLASS_ID =@intClassId  AND TBF.SECTION_ID = @intSectionId
    				AND TBF.FY_ID =@intFYID
    				AND TBF.MONTH_ID IN (SELECT VALUE FROM DBO.FN_SPLIT(@strMonthIds,','))    
    				AND TSI.STUDENT_ID = TBF.STUDENT_ID
    				AND TSI.DELETE_FLAG = 0
    				
    
    
    
    --AND TF.STUDENT_ID=TSI.STUDENT_ID
    --				AND TF.CLASS_ID=TBF.CLASS_ID
    --				AND TF.SECTION_ID=TBF.SECTION_ID
    --				AND TF.FEETYPE_ID IN (SELECT VALUE FROM dbo.fn_split(@strFeeTypeIds,','))
    --				AND TFT.FEETYPE_ID IN (SELECT VALUE FROM dbo.fn_split(@strFeeTypeIds,','))
    
    -- 	ORDER BY  TStudent_Id, FEETYPE_ID
    				GROUP BY
    					TFT.FEETYPE_ID,TF.FEETYPE_ID, TF.Student_Id,TFT.DESCRIPTION,TFT.AMOUNT, TBF.Student_Id, TBF.MONTH_ID,TBF.AREA_ID,TSI.FIRSTNAME, TSI.LASTNAME , TSI.FATHERNAME ,TSI.Student_Code
    				
    
    		)    
    		SRC   
    	LEFT OUTER JOIN     
    		tblAreaBusFee TAF
    			ON TAF.FY_ID = @intFYID     
    			AND TAF.AREA_ID = SRC.AREA_ID    
    			AND TAF.MONTH_ID = SRC.MONTH_ID     
    			AND TAF.MONTH_ID IN (SELECT VALUE FROM DBO.FN_SPLIT(@strMonthIds,','))  
       
    			--AND (TAF.BusFee - SRC.PAID_AMOUNT) <> 0
    		UNION    
    		SELECT     
    			DISTINCT TSI.Student_Id, TSI.Student_Code, FIRSTNAME + ' ' + LASTNAME FULLNAME, FATHERNAME,
    			TFT.FEETYPE_ID TERMFEETYPE_ID,
    --			(SELECT Description from tblTermFeeTypes where FEETYPE_ID=TFT.FEETYPE_ID),(SELECT SUM(Amount) from tblTermFeeTypes where FEETYPE_ID=TFT.FEETYPE_ID), 
    			TFT.DESCRIPTION,TFT.AMOUNT,0 TERMFEE_PAID_AMOUNT,TFT.AMOUNT DUE_AMOUNT, TAF.MONTH_ID FEETYPE_ID,
    --			TF.AMOUNT DUE_AMOUNT,ISNULL(TFT.AMOUNT,0) -ISNULL(TF.AMOUNT,0) DUE_AMOUNT,
    			(SELECT MONTH_NAME FROM TBLMONTHS WHERE MONTH_ID = TAF.MONTH_ID AND FY_ID = @intFYID) + ' Bus Fee'
    			DESCRIPTION, TAF.BUSFEE AMOUNT , 0 PAID_AMOUNT, TAF.BUSFEE DUE_AMOUNT     
    		FROM    
    			TBLSTUDENTINFO TSI,    
    			tblAreaBusFee TAF,
    			tblFEE TF,
    			tblTermFeeTypes TFT
    		WHERE     
    			TSI.CLASS_ID = @intClassId  AND TSI.SECTION_ID = @intSectionId 
    			AND TSI.STUDENT_ID NOT IN (SELECT DISTINCT STUDENT_ID FROM TBLBUSFEE WHERE CLASS_ID = @intClassId and section_id = @intSectionId )
    			AND TSI.FY_ID = @intFYID AND TAF.FY_ID = @intFYID    
    			AND TSI.AREA_ID = TAF.AREA_ID  
    			AND TSI.DELETE_FLAG = 0  
    			AND TAF.MONTH_ID IN (SELECT VALUE FROM DBO.FN_SPLIT(@strMonthIds,',')) 
    --			AND TF.STUDENT_ID=TSI.STUDENT_ID
    --			AND TF.CLASS_ID=@intClassId
    --			AND TF.SECTION_ID=@intSectionId
    			AND TF.FEETYPE_ID IN (SELECT VALUE FROM dbo.fn_split(@strFeeTypeIds,',')) 
    			AND TFT.FEETYPE_ID IN (SELECT VALUE FROM dbo.fn_split(@strFeeTypeIds,','))  
    			
    			
    
    
    
    
    
    
    
    		 UNION    
    			SELECT     
    				DISTINCT TSI.Student_Id,TSI.Student_code, FIRSTNAME + ' ' + LASTNAME FULLNAME, FATHERNAME, 
    				TFT.FEETYPE_ID TERMFEETYPE_ID,
    --				(SELECT Description from tblTermFeeTypes where FEETYPE_ID=TFT.FEETYPE_ID),(SELECT SUM(Amount) from tblTermFeeTypes where FEETYPE_ID=TFT.FEETYPE_ID), 
    					TFT.DESCRIPTION,TFT.AMOUNT,0 TERMFEE_PAID_AMOUNT,TFT.AMOUNT DUE_AMOUNT,TAF.MONTH_ID FEETYPE_ID, 
    --				TF.AMOUNT DUE_AMOUNT,ISNULL(TFT.AMOUNT,0) -ISNULL(TF.AMOUNT,0) DUE_AMOUNT,
    --				TF.AMOUNT DUE_AMOUNT,(SELECT SUM(Amount) from tblTermFeeTypes  where FEETYPE_ID=TFT.FEETYPE_ID) -TF.AMOUNT DUE_AMOUNT,
    				(SELECT MONTH_NAME FROM TBLMONTHS WHERE MONTH_ID = TAF.MONTH_ID AND FY_ID = @intFYID) + ' Bus Fee' DESCRIPTION, 
    				TAF.BUSFEE AMOUNT , 0 PAID_AMOUNT, TAF.BUSFEE DUE_AMOUNT     
    			FROM    
    				TBLSTUDENTINFO TSI,    
    				tblAreaBusFee TAF,
    				tblFEE TF,
    				tblTermFeeTypes TFT
    			WHERE 
    
    
    				TSI.CLASS_ID = @intClassId  AND TSI.SECTION_ID = @intSectionId 
    				AND TSI.STUDENT_ID IN (SELECT DISTINCT STUDENT_ID FROM TBLBUSFEE WHERE CLASS_ID = @intClassId and section_id = @intSectionId )
    				AND TAF.MONTH_ID NOT IN (SELECT DISTINCT MONTH_ID FROM TBLBUSFEE WHERE CLASS_ID = @intClassId and section_id = @intSectionId AND STUDENT_ID = TSI.STUDENT_ID)
    				AND TF.FEETYPE_ID NOT  IN (SELECT DISTINCT FEETYPE_ID FROM tblFEE WHERE CLASS_ID = @intClassId and section_id = @intSectionId AND STUDENT_ID = TSI.STUDENT_ID) 
    --				AND TFT.FEETYPE_ID NOT  IN (SELECT DISTINCT FEETYPE_ID FROM tblTermFeeTypes WHERE CLASS_ID = @intClassId and section_id = @intSectionId AND STUDENT_ID = TSI.STUDENT_ID) 
    				AND TSI.FY_ID = @intFYID AND TAF.FY_ID = @intFYID    
    				AND TSI.AREA_ID = TAF.AREA_ID    
    				AND TSI.DELETE_FLAG = 0
    				AND TAF.MONTH_ID IN (SELECT VALUE FROM DBO.FN_SPLIT(@strMonthIds,','))  
        --				AND TF.STUDENT_ID=TSI.STUDENT_ID
    --				AND TF.CLASS_ID=@intClassId
    --				AND TF.SECTION_ID=@intSectionId
    				AND TF.FEETYPE_ID IN (SELECT VALUE FROM dbo.fn_split(@strFeeTypeIds,','))
    --				AND TFT.FEETYPE_ID IN (SELECT VALUE FROM dbo.fn_split(@strFeeTypeIds,','))
    				 
    		 ORDER BY Student_Id, FEETYPE_ID
    end
    Last edited by WA; 01-02-2012 at 03:15 PM.


 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •