FEE INTIMATION DUELIST - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

FEE INTIMATION DUELIST

Author

Topic

prashanth193
Starting Member

1 Post

Posted - 2012-01-02 : 00:40:13

SIR 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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-02 : 08:33:03

Without the benefit of some sample data, logical errors are very hard for someone not familiar with your code to figure out. Take a look at Brett's blog here if you need help in generating some sample data, table DDL's etc. http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

In any case, to debug this, I would first pick one student whose results seem to be incorrect and add that student id in the where clause ("and student_code = 1234"). Then, change the UNION to UNION ALL and see what you get. That might give you some clues as to why the results are not what you expect.

BTW, when you type in all capitals, it is sort of hard to read. Also, many people consider all capitals the equivalent of shouting. Would be better if you posted using the proper casing (as I am doing in this paragraph).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources