Fee intimation due list

[prashanth193]

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






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