October 23, 2007 at 4:52 am
hi all,
Im making one reporting system using sql server2000 and c# asp.net.I have to make some calculation inside the stored procedur.But it take long time to generate my report. Inside my stored procedur i used cursors and some t-sql functions.
so i want to know are there any method to do fast all calculations inside sp's .
My problem is normally my PC take 40 seconds to generate my report.
If i host this system i think it will take more than 1 mints for generate same report.
Now i indexces all my data filtering tables . But my problem is still same. Can anybody help me to do fast this in sqlserver 2000.:D
thankx
October 23, 2007 at 5:53 am
You mentioned, you have used cursors in your procedure which i guess might be taking too much of the process time [and most of the time it will]...
If you can post your code, we can make convert your RBAR [in Jeff Modenology] logic into set based logic...
--Ramesh
October 23, 2007 at 6:19 am
CREATE PROCEDURE [dbo].[VIEW_REPORTS]
@BRC_START_DATE datetime,
@BRC_END_DATE datetime,
@Hild_Branch varchar(50),
@REPORTSIZE int,
@RPT_REPORT_NAME varchar(10),
@USER_ID INT,
@ReportType_Code nvarchar(50)
AS
declare @MonthFirstdate as varchar(50)
declare @Month as varchar(50)
Declare @Year as varchar(50)
set @year =year(@BRC_END_DATE);
set @Month='';
if MONTH(@BRC_END_DATE)<10
begin
set @Month= '0' + month(@BRC_END_DATE);
set @MonthFirstdate= @year + '-0' + @Month + '-01';
end
if MONTH(@BRC_END_DATE)>=10
begin
set @Month=month(@BRC_END_DATE);
set @MonthFirstdate= @year + '-' + @Month + '-01'
end
DECLARE @PID_BRANCH as varchar(100);
IF @Hild_Branch='NAT'
BEGIN
DECLARE BRANCH_CHILD CURSOR
FOR
SELECT e1.BRC_BRANCH_CODE
FROM BRANCH_CODE e1 LEFT JOIN BRANCH_CODE e2 ON e1.BRC_SUPPER_CODE =e2.BRC_BRANCH_CODE
--WHERE e2.BRC_SUPPER_CODE = @Hild_Branch or e1.BRC_SUPPER_CODE = @Hild_Branch
END
IF @Hild_Branch<>'NAT'
BEGIN
DECLARE BRANCH_CHILD CURSOR
FOR
SELECT e1.BRC_BRANCH_CODE
FROM BRANCH_CODE e1 LEFT JOIN BRANCH_CODE e2 ON e1.BRC_SUPPER_CODE =e2.BRC_BRANCH_CODE
WHERE e2.BRC_SUPPER_CODE = @Hild_Branch or e1.BRC_SUPPER_CODE = @Hild_Branch
END
OPEN BRANCH_CHILD
FETCH NEXT FROM BRANCH_CHILD INTO @PID_BRANCH;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
Declare @AgentCode as varchar(100);
IF @RPT_REPORT_NAME='GL/TL/BPO'
BEGIN
DECLARE Branch_Agents CURSOR
FOR
SELECT AGT_CODE FROM AGENT where AGT_BRANCH_CODE =@PID_BRANCH AND (AGT_SUB_CODE LIKE '%GL%' OR AGT_SUB_CODE LIKE '%TL%' OR AGT_SUB_CODE LIKE '%BPO%') ;
END
IF @RPT_REPORT_NAME='FM/AFM'
BEGIN
DECLARE Branch_Agents CURSOR
FOR
SELECT AGT_CODE FROM AGENT where AGT_BRANCH_CODE =@PID_BRANCH AND (AGT_SUB_CODE LIKE '%FM%' OR AGT_SUB_CODE LIKE '%AFM%') ;
END
IF @RPT_REPORT_NAME='ADV'
BEGIN
DECLARE Branch_Agents CURSOR
FOR
SELECT AGT_CODE FROM AGENT where AGT_BRANCH_CODE =@PID_BRANCH AND (AGT_SUB_CODE) is null;
END
OPEN Branch_Agents
Declare @Agent_Code as varchar(100);
Declare @Agent_Name as varchar(100);
Declare @Leader_Code as varchar(100);
Declare @Agent_FirstName as varchar(100);
Declare @Agent_LastName as varchar(100);
Declare @AGT_TITLE as varchar(100);
Declare @Fyp as numeric;
Declare @SumFyp as numeric;
Declare @Branch_Name as varchar(100);
Declare @PID_TIME_SLAB as varchar(10);
Declare @Gwp as numeric;
Declare @SumGwp as numeric;
Declare @ModeMonth as numeric;
Declare @SumModeMonth as numeric;
Declare @Quarter as numeric;
Declare @SumQuarter as numeric;
Declare @HalfYear as numeric;
Declare @SumHalfYear as numeric;
Declare @ModeYear as numeric;
Declare @SumModeYear as numeric;
Declare @ModeSp as numeric;
Declare @SumModeSp as numeric;
Declare @LeaderCode as varchar(100);
Declare @Code as varchar(100);
Declare @Name as varchar(100);
Declare @Branch as varchar(100);
Declare @Rank as int;
Declare @AGT_Sub_Code as varchar(10);
Declare @ModeMonth_Adv as numeric;
Declare @SumModeMonth_Adv as numeric;
Declare @Quarter_Adv as numeric;
Declare @SumQuarter_Adv as numeric;
Declare @HalfYear_Adv as numeric;
Declare @SumHalfYear_Adv as numeric;
Declare @ModeYear_Adv as numeric;
Declare @SumModeYear_Adv as numeric;
Declare @ModeSp_Adv as numeric;
Declare @SumModeSp_Adv as numeric;
Declare @LeaderID as varchar(100);
Declare @AFMID as varchar(100);
Declare @AFMSUPER_AGENTID as varchar(100);
Declare @AFMSUPER_AGENTAMOUNT as numeric;
Declare @GLTLBPO_CODE as varchar(50);
Declare @LeaderGl_ID as varchar(50);
Declare @FypGl as numeric;
Declare @SumFypGl as numeric;
FETCH NEXT FROM Branch_Agents INTO @AgentCode;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
set @Fyp=0
set @SumFyp=0
set @Gwp=0
set @SumGwp=0
set @ModeMonth=0
Select @Branch_Name=BRC_BRANCH_NAME FROM BRANCH_CODE Where BRC_BRANCH_CODE=@PID_BRANCH
Select @Agent_FirstName=AGT_FIRST_NAME, @Agent_LastName=AGT_LAST_NAME ,@AGT_TITLE=AGT_TITLE From AGENT Where AGT_CODE=@AgentCode
Select @Leader_Code=AGT_SUB_CODE From AGENT Where AGT_CODE=@AgentCode
set @Agent_Name=@AGT_TITLE+' '+ + @Agent_FirstName+' '+ + @Agent_LastName
SELECT @LeaderID= AGT_ID FROM AGENT WHERE AGT_CODE =@AgentCode
IF @ReportType_Code='FST'
BEGIN
IF @RPT_REPORT_NAME='FM/AFM'
BEGIN
SELECT @Fyp=SUM(PID_ACC.PID_RECEIPT_AMT) FROM PID_ACC INNER JOIN AGENT ON PID_ACC.PID_AGT_CODE = AGENT.AGT_CODE
WHERE ((PID_ACC.PID_AGT_CODE = @AgentCode) OR (AGENT.AGT_SUPER_CODE = @LeaderID)) AND (PID_ACC.PID_TIME_SLAB = 'FST') AND (PID_ACC.PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
IF (ISNULL(@Fyp,1)=1)
BEGIN
SET @Fyp=0;
END
SELECT @SumFyp= SUM(PID_ACC.PID_RECEIPT_AMT) FROM PID_ACC INNER JOIN AGENT ON PID_ACC.PID_AGT_CODE = AGENT.AGT_CODE
WHERE ((PID_ACC.PID_AGT_CODE = @AgentCode) OR (AGENT.AGT_SUPER_CODE = @LeaderID)) AND (PID_ACC.PID_TIME_SLAB = 'FST') AND (PID_ACC.PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
IF (ISNULL(@SumFyp,1)=1)
BEGIN
SET @SumFyp=0;
END
DECLARE FINDLEADERTOTAL CURSOR
FOR
SELECT AGT_CODE FROM AGENT WHERE AGT_SUPER_CODE=@LeaderID
OPEN FINDLEADERTOTAL
FETCH NEXT FROM FINDLEADERTOTAL INTO @GLTLBPO_CODE;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SELECT @LeaderGl_ID= AGT_ID FROM AGENT WHERE AGT_CODE =@GLTLBPO_CODE
SELECT @FypGl=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE (AGENT.AGT_SUPER_CODE =@LeaderGl_ID) AND (PID_TIME_SLAB='FST' ) AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
IF (ISNULL(@FypGl,1)=1)
BEGIN
SET @FypGl=0;
END
SELECT @SumFypGl=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE (AGENT.AGT_SUPER_CODE =@LeaderGl_ID) AND (PID_TIME_SLAB='FST') AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
IF (ISNULL(@SumFypGl,1)=1)
BEGIN
SET @SumFypGl=0;
END
SET @Fyp= @Fyp + @FypGl
SET @SumFyp= @SumFyp + @SumFypGl
END;
FETCH NEXT FROM FINDLEADERTOTAL INTO @GLTLBPO_CODE;
END;
CLOSE FINDLEADERTOTAL;
DEALLOCATE FINDLEADERTOTAL;
END
IF @RPT_REPORT_NAME='GL/TL/BPO'
BEGIN
SELECT @Fyp=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE ((PID_AGT_CODE=@AgentCode) OR (AGENT.AGT_SUPER_CODE=@LeaderId)) AND (PID_TIME_SLAB='FST' ) AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
IF (ISNULL(@Fyp,1)=1)
BEGIN
SET @Fyp=0;
END
SELECT @SumFyp=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE ((PID_AGT_CODE=@AgentCode) OR (AGENT.AGT_SUPER_CODE=@LeaderId)) AND (PID_TIME_SLAB='FST') AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
IF (ISNULL(@SumFyp,1)=1)
BEGIN
SET @SumFyp=0;
END
END
IF @RPT_REPORT_NAME='ADV'
BEGIN
SELECT @Fyp=sum(PID_RECEIPT_AMT) FROM PId_ACC WHERE PID_AGT_CODE=@AgentCode AND (PID_TIME_SLAB='FST' ) AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
IF (ISNULL(@Fyp,1)=1)
BEGIN
SET @Fyp=0;
END
SELECT @SumFyp=sum(PID_RECEIPT_AMT) FROM PId_ACC WHERE PID_AGT_CODE=@AgentCode AND (PID_TIME_SLAB='FST') AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
IF (ISNULL(@SumFyp,1)=1)
BEGIN
SET @SumFyp=0;
END
END
END
IF @ReportType_Code='MCFP'
BEGIN
IF @RPT_REPORT_NAME='FM/AFM'
BEGIN
SELECT @ModeMonth=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE ((PID_AGT_CODE=@AgentCode) OR (AGENT.AGT_SUPER_CODE=@LeaderId)) AND (PID_PAY_FREQ='M' ) AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
SELECT @Quarter=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE ((PID_AGT_CODE=@AgentCode) OR (AGENT.AGT_SUPER_CODE=@LeaderId)) AND (PID_PAY_FREQ='Q' ) AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
SELECT @HalfYear=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE ((PID_AGT_CODE=@AgentCode) OR (AGENT.AGT_SUPER_CODE=@LeaderId)) AND (PID_PAY_FREQ='HY' ) AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
SELECT @ModeYear=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE ((PID_AGT_CODE=@AgentCode) OR (AGENT.AGT_SUPER_CODE=@LeaderId)) AND (PID_PAY_FREQ='A' ) AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
SELECT @ModeSp=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE ((PID_AGT_CODE=@AgentCode) OR (AGENT.AGT_SUPER_CODE=@LeaderId)) AND (PID_PAY_FREQ='S' ) AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
IF (ISNULL(@ModeMonth,1)=1)
BEGIN
SET @ModeMonth=0;
END
IF (ISNULL(@Quarter,1)=1)
BEGIN
SET @Quarter=0;
END
IF (ISNULL(@HalfYear,1)=1)
BEGIN
SET @HalfYear=0;
END
IF (ISNULL(@ModeYear,1)=1)
BEGIN
SET @ModeYear=0;
END
IF (ISNULL(@ModeSp,1)=1)
BEGIN
SET @ModeSp=0;
END
SET @Fyp=@ModeMonth + @Quarter/3 + @HalfYear/6 + @ModeYear/12 + @ModeSp/60
SELECT @SumModeMonth=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE ((PID_AGT_CODE=@AgentCode) OR (AGENT.AGT_SUPER_CODE=@LeaderId)) AND (PID_PAY_FREQ='M' ) AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
SELECT @SumQuarter=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE ((PID_AGT_CODE=@AgentCode) OR (AGENT.AGT_SUPER_CODE=@LeaderId)) AND (PID_PAY_FREQ='Q' ) AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
SELECT @SumHalfYear=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE ((PID_AGT_CODE=@AgentCode) OR (AGENT.AGT_SUPER_CODE=@LeaderId)) AND (PID_PAY_FREQ='HY' ) AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
SELECT @SumModeYear=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE ((PID_AGT_CODE=@AgentCode) OR (AGENT.AGT_SUPER_CODE=@LeaderId)) AND (PID_PAY_FREQ='A' ) AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
SELECT @SumModeSp=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE ((PID_AGT_CODE=@AgentCode) OR (AGENT.AGT_SUPER_CODE=@LeaderId)) AND (PID_PAY_FREQ='S' ) AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
IF (ISNULL(@SumModeMonth,1)=1)
BEGIN
SET @SumModeMonth=0;
END
IF (ISNULL(@SumQuarter,1)=1)
BEGIN
SET @SumQuarter=0;
END
IF (ISNULL(@SumHalfYear,1)=1)
BEGIN
SET @SumHalfYear=0;
END
IF (ISNULL(@SumModeYear,1)=1)
BEGIN
SET @SumModeYear=0;
END
IF (ISNULL(@SumModeSp,1)=1)
BEGIN
SET @SumModeSp=0;
END
SET @SumFyp=@SumModeMonth + @SumQuarter/3 + @SumHalfYear/6 + @SumModeYear/12 + @SumModeSp/60
DECLARE FINDLEADERTOTAL CURSOR
FOR
SELECT AGT_CODE FROM AGENT WHERE AGT_SUPER_CODE=@LeaderID
OPEN FINDLEADERTOTAL
FETCH NEXT FROM FINDLEADERTOTAL INTO @GLTLBPO_CODE;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SELECT @LeaderGl_ID= AGT_ID FROM AGENT WHERE AGT_CODE =@GLTLBPO_CODE
SELECT @ModeMonth_Adv=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE (AGENT.AGT_SUPER_CODE=@LeaderId) AND (PID_PAY_FREQ='M' ) AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
SELECT @Quarter_Adv=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE (AGENT.AGT_SUPER_CODE=@LeaderId) AND (PID_PAY_FREQ='Q' ) AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
SELECT @HalfYear_Adv=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE (AGENT.AGT_SUPER_CODE=@LeaderId) AND (PID_PAY_FREQ='HY' ) AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
SELECT @ModeYear_Adv=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE (AGENT.AGT_SUPER_CODE=@LeaderId) AND (PID_PAY_FREQ='A' ) AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
SELECT @ModeSp_Adv=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE (AGENT.AGT_SUPER_CODE=@LeaderId) AND (PID_PAY_FREQ='S' ) AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
IF (ISNULL(@ModeMonth_Adv,1)=1)
BEGIN
SET @ModeMonth_Adv=0;
END
IF (ISNULL(@Quarter_Adv,1)=1)
BEGIN
SET @Quarter_Adv=0;
END
IF (ISNULL(@HalfYear_Adv,1)=1)
BEGIN
SET @HalfYear_Adv=0;
END
IF (ISNULL(@ModeYear_Adv,1)=1)
BEGIN
SET @ModeYear_Adv=0;
END
IF (ISNULL(@ModeSp_Adv,1)=1)
BEGIN
SET @ModeSp_Adv=0;
END
SET @Fyp=@Fyp+ @ModeMonth_Adv + @Quarter_Adv/3 + @HalfYear_Adv/6 + @ModeYear_Adv/12 + @ModeSp_Adv/60
SELECT @SumModeMonth_Adv=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE (AGENT.AGT_SUPER_CODE=@LeaderId) AND (PID_PAY_FREQ='M' ) AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
SELECT @SumQuarter_Adv=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE (AGENT.AGT_SUPER_CODE=@LeaderId) AND (PID_PAY_FREQ='Q' ) AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
SELECT @SumHalfYear_Adv=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE (AGENT.AGT_SUPER_CODE=@LeaderId) AND (PID_PAY_FREQ='HY' ) AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
SELECT @SumModeYear_Adv=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE (AGENT.AGT_SUPER_CODE=@LeaderId) AND (PID_PAY_FREQ='A' ) AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
SELECT @SumModeSp_Adv=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE (AGENT.AGT_SUPER_CODE=@LeaderId) AND (PID_PAY_FREQ='S' ) AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
IF (ISNULL(@SumModeMonth_Adv,1)=1)
BEGIN
SET @SumModeMonth_Adv=0;
END
IF (ISNULL(@SumQuarter_Adv,1)=1)
BEGIN
SET @SumQuarter_Adv=0;
END
IF (ISNULL(@SumHalfYear_Adv,1)=1)
BEGIN
SET @SumHalfYear_Adv=0;
END
IF (ISNULL(@SumModeYear_Adv,1)=1)
BEGIN
SET @SumModeYear_Adv=0;
END
IF (ISNULL(@SumModeSp_Adv,1)=1)
BEGIN
SET @SumModeSp_Adv=0;
END
SET @SumFyp=@SumFyp+ @SumModeMonth_Adv + @SumQuarter_Adv/3 + @SumHalfYear_Adv/6 + @SumModeYear_Adv/12 + @SumModeSp_Adv/60
END;
FETCH NEXT FROM FINDLEADERTOTAL INTO @GLTLBPO_CODE;
END;
CLOSE FINDLEADERTOTAL;
DEALLOCATE FINDLEADERTOTAL;
END
IF @RPT_REPORT_NAME='GL/TL/BPO'
BEGIN
SELECT @ModeMonth=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE ((PID_AGT_CODE=@AgentCode) OR (AGENT.AGT_SUPER_CODE=@LeaderId)) AND (PID_PAY_FREQ='M' ) AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
SELECT @Quarter=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE ((PID_AGT_CODE=@AgentCode) OR (AGENT.AGT_SUPER_CODE=@LeaderId)) AND (PID_PAY_FREQ='Q' ) AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
SELECT @HalfYear=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE ((PID_AGT_CODE=@AgentCode) OR (AGENT.AGT_SUPER_CODE=@LeaderId)) AND (PID_PAY_FREQ='HY' ) AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
SELECT @ModeYear=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE ((PID_AGT_CODE=@AgentCode) OR (AGENT.AGT_SUPER_CODE=@LeaderId)) AND (PID_PAY_FREQ='A' ) AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
SELECT @ModeSp=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE ((PID_AGT_CODE=@AgentCode) OR (AGENT.AGT_SUPER_CODE=@LeaderId)) AND (PID_PAY_FREQ='S' ) AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
IF (ISNULL(@ModeMonth,1)=1)
BEGIN
SET @ModeMonth=0;
END
IF (ISNULL(@Quarter,1)=1)
BEGIN
SET @Quarter=0;
END
IF (ISNULL(@HalfYear,1)=1)
BEGIN
SET @HalfYear=0;
END
IF (ISNULL(@ModeYear,1)=1)
BEGIN
SET @ModeYear=0;
END
IF (ISNULL(@ModeSp,1)=1)
BEGIN
SET @ModeSp=0;
END
SET @Fyp=@ModeMonth + @Quarter/3 + @HalfYear/6 + @ModeYear/12 + @ModeSp/60
SELECT @SumModeMonth=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE ((PID_AGT_CODE=@AgentCode) OR (AGENT.AGT_SUPER_CODE=@LeaderId)) AND (PID_PAY_FREQ='M' ) AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
SELECT @SumQuarter=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE ((PID_AGT_CODE=@AgentCode) OR (AGENT.AGT_SUPER_CODE=@LeaderId)) AND (PID_PAY_FREQ='Q' ) AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
SELECT @SumHalfYear=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE ((PID_AGT_CODE=@AgentCode) OR (AGENT.AGT_SUPER_CODE=@LeaderId)) AND (PID_PAY_FREQ='HY' ) AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
SELECT @SumModeYear=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE ((PID_AGT_CODE=@AgentCode) OR (AGENT.AGT_SUPER_CODE=@LeaderId)) AND (PID_PAY_FREQ='A' ) AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
SELECT @SumModeSp=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE ((PID_AGT_CODE=@AgentCode) OR (AGENT.AGT_SUPER_CODE=@LeaderId)) AND (PID_PAY_FREQ='S' ) AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
IF (ISNULL(@SumModeMonth,1)=1)
BEGIN
SET @SumModeMonth=0;
END
IF (ISNULL(@SumQuarter,1)=1)
BEGIN
SET @SumQuarter=0;
END
IF (ISNULL(@SumHalfYear,1)=1)
BEGIN
SET @SumHalfYear=0;
END
IF (ISNULL(@SumModeYear,1)=1)
BEGIN
SET @SumModeYear=0;
END
IF (ISNULL(@SumModeSp,1)=1)
BEGIN
SET @SumModeSp=0;
END
SET @SumFyp=@SumModeMonth + @SumQuarter/3 + @SumHalfYear/6 + @SumModeYear/12 + @SumModeSp/60
END
IF @RPT_REPORT_NAME='ADV'
BEGIN
SELECT @ModeMonth=sum(PID_RECEIPT_AMT) FROM PId_ACC WHERE PID_AGT_CODE=@AgentCode AND (PID_PAY_FREQ='M' ) AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
SELECT @Quarter=sum(PID_RECEIPT_AMT) FROM PId_ACC WHERE PID_AGT_CODE=@AgentCode AND (PID_PAY_FREQ='Q' ) AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
SELECT @HalfYear=sum(PID_RECEIPT_AMT) FROM PId_ACC WHERE PID_AGT_CODE=@AgentCode AND (PID_PAY_FREQ='HY' ) AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
SELECT @ModeYear=sum(PID_RECEIPT_AMT) FROM PId_ACC WHERE PID_AGT_CODE=@AgentCode AND (PID_PAY_FREQ='A' ) AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
SELECT @ModeSp=sum(PID_RECEIPT_AMT) FROM PId_ACC WHERE PID_AGT_CODE=@AgentCode AND (PID_PAY_FREQ='S' ) AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
IF (ISNULL(@ModeMonth,1)=1)
BEGIN
SET @ModeMonth=0;
END
IF (ISNULL(@Quarter,1)=1)
BEGIN
SET @Quarter=0;
END
IF (ISNULL(@HalfYear,1)=1)
BEGIN
SET @HalfYear=0;
END
IF (ISNULL(@ModeYear,1)=1)
BEGIN
SET @ModeYear=0;
END
IF (ISNULL(@ModeSp,1)=1)
BEGIN
SET @ModeSp=0;
END
SET @Fyp=@ModeMonth + @Quarter/3 + @HalfYear/6 + @ModeYear/12 + @ModeSp/60
SELECT @SumModeMonth=sum(PID_RECEIPT_AMT) FROM PId_ACC WHERE PID_AGT_CODE=@AgentCode AND (PID_PAY_FREQ='M' ) AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
SELECT @SumQuarter=sum(PID_RECEIPT_AMT) FROM PId_ACC WHERE PID_AGT_CODE=@AgentCode AND (PID_PAY_FREQ='Q' ) AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
SELECT @SumHalfYear=sum(PID_RECEIPT_AMT) FROM PId_ACC WHERE PID_AGT_CODE=@AgentCode AND (PID_PAY_FREQ='HY' ) AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
SELECT @SumModeYear=sum(PID_RECEIPT_AMT) FROM PId_ACC WHERE PID_AGT_CODE=@AgentCode AND (PID_PAY_FREQ='A' ) AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
SELECT @SumModeSp=sum(PID_RECEIPT_AMT) FROM PId_ACC WHERE PID_AGT_CODE=@AgentCode AND (PID_PAY_FREQ='S' ) AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
IF (ISNULL(@SumModeMonth,1)=1)
BEGIN
SET @SumModeMonth=0;
END
IF (ISNULL(@SumQuarter,1)=1)
BEGIN
SET @SumQuarter=0;
END
IF (ISNULL(@SumHalfYear,1)=1)
BEGIN
SET @SumHalfYear=0;
END
IF (ISNULL(@SumModeYear,1)=1)
BEGIN
SET @SumModeYear=0;
END
IF (ISNULL(@SumModeSp,1)=1)
BEGIN
SET @SumModeSp=0;
END
SET @SumFyp=@SumModeMonth + @SumQuarter/3 + @SumHalfYear/6 + @SumModeYear/12 + @SumModeSp/60
END
END
IF @ReportType_Code='FYP'
BEGIN
IF @RPT_REPORT_NAME='FM/AFM'
BEGIN
SELECT @Fyp= SUM(PID_ACC.PID_RECEIPT_AMT) FROM PID_ACC INNER JOIN AGENT ON PID_ACC.PID_AGT_CODE = AGENT.AGT_CODE
WHERE ((PID_ACC.PID_AGT_CODE = @AgentCode) OR (AGENT.AGT_SUPER_CODE = @LeaderID)) AND (PID_ACC.PID_TIME_SLAB = 'FYP') AND (PID_ACC.PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
IF (ISNULL(@Fyp,1)=1)
BEGIN
SET @Fyp=0;
END
SELECT @SumFyp= SUM(PID_ACC.PID_RECEIPT_AMT) FROM PID_ACC INNER JOIN AGENT ON PID_ACC.PID_AGT_CODE = AGENT.AGT_CODE
WHERE ((PID_ACC.PID_AGT_CODE = @AgentCode) OR (AGENT.AGT_SUPER_CODE = @LeaderID)) AND (PID_ACC.PID_TIME_SLAB = 'FYP') AND (PID_ACC.PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
IF (ISNULL(@SumFyp,1)=1)
BEGIN
SET @SumFyp=0;
END
DECLARE FINDLEADERTOTAL CURSOR
FOR
SELECT AGT_CODE FROM AGENT WHERE AGT_SUPER_CODE=@LeaderID
OPEN FINDLEADERTOTAL
FETCH NEXT FROM FINDLEADERTOTAL INTO @GLTLBPO_CODE;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SELECT @LeaderGl_ID= AGT_ID FROM AGENT WHERE AGT_CODE =@GLTLBPO_CODE
SELECT @FypGl=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE (AGENT.AGT_SUPER_CODE =@LeaderGl_ID) AND (PID_TIME_SLAB='FYP' ) AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
IF (ISNULL(@FypGl,1)=1)
BEGIN
SET @FypGl=0;
END
SELECT @SumFypGl=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE (AGENT.AGT_SUPER_CODE =@LeaderGl_ID) AND (PID_TIME_SLAB='FYP') AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
IF (ISNULL(@SumFypGl,1)=1)
BEGIN
SET @SumFypGl=0;
END
SET @Fyp= @Fyp + @FypGl
SET @SumFyp= @SumFyp + @SumFypGl
END;
FETCH NEXT FROM FINDLEADERTOTAL INTO @GLTLBPO_CODE;
END;
CLOSE FINDLEADERTOTAL;
DEALLOCATE FINDLEADERTOTAL;
END
IF @RPT_REPORT_NAME='GL/TL/BPO'
BEGIN
SELECT @Fyp=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE (PID_AGT_CODE=@AgentCode) AND (PID_TIME_SLAB='FYP' ) AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
IF (ISNULL(@Fyp,1)=1)
BEGIN
SET @Fyp=0;
END
SELECT @SumFyp=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE ((PID_AGT_CODE=@AgentCode) OR AGENT.AGT_SUPER_CODE=@LeaderId) AND (PID_TIME_SLAB='FYP') AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
IF (ISNULL(@SumFyp,1)=1)
BEGIN
SET @SumFyp=0;
END
END
IF @RPT_REPORT_NAME='ADV'
BEGIN
SELECT @Fyp=sum(PID_RECEIPT_AMT) FROM PId_ACC WHERE PID_AGT_CODE=@AgentCode AND (PID_TIME_SLAB='FYP' ) AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
IF (ISNULL(@Fyp,1)=1)
BEGIN
SET @Fyp=0;
END
SELECT @SumFyp=sum(PID_RECEIPT_AMT) FROM PId_ACC WHERE PID_AGT_CODE=@AgentCode AND (PID_TIME_SLAB='FYP') AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
IF (ISNULL(@SumFyp,1)=1)
BEGIN
SET @SumFyp=0;
END
END
END
IF @ReportType_Code='TOTAL FYP'
BEGIN
IF @RPT_REPORT_NAME='FM/AFM'
BEGIN
SELECT @Fyp= SUM(PID_ACC.PID_RECEIPT_AMT) FROM PID_ACC INNER JOIN AGENT ON PID_ACC.PID_AGT_CODE = AGENT.AGT_CODE
WHERE ((PID_ACC.PID_AGT_CODE = @AgentCode) OR (AGENT.AGT_SUPER_CODE = @LeaderID)) AND (PID_TIME_SLAB='FST' OR PID_TIME_SLAB='FYP') AND (PID_ACC.PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
IF (ISNULL(@Fyp,1)=1)
BEGIN
SET @Fyp=0;
END
SELECT @SumFyp= SUM(PID_ACC.PID_RECEIPT_AMT) FROM PID_ACC INNER JOIN AGENT ON PID_ACC.PID_AGT_CODE = AGENT.AGT_CODE
WHERE ((PID_ACC.PID_AGT_CODE = @AgentCode) OR (AGENT.AGT_SUPER_CODE = @LeaderID)) AND (PID_TIME_SLAB='FST' OR PID_TIME_SLAB='FYP') AND (PID_ACC.PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
IF (ISNULL(@SumFyp,1)=1)
BEGIN
SET @SumFyp=0;
END
DECLARE FINDLEADERTOTAL CURSOR
FOR
SELECT AGT_CODE FROM AGENT WHERE AGT_SUPER_CODE=@LeaderID
OPEN FINDLEADERTOTAL
FETCH NEXT FROM FINDLEADERTOTAL INTO @GLTLBPO_CODE;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SELECT @LeaderGl_ID= AGT_ID FROM AGENT WHERE AGT_CODE =@GLTLBPO_CODE
SELECT @FypGl=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE (AGENT.AGT_SUPER_CODE =@LeaderGl_ID) AND (PID_TIME_SLAB='FST' OR PID_TIME_SLAB='FYP') AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
IF (ISNULL(@FypGl,1)=1)
BEGIN
SET @FypGl=0;
END
SELECT @SumFypGl=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE (AGENT.AGT_SUPER_CODE =@LeaderGl_ID) AND (PID_TIME_SLAB='FST' OR PID_TIME_SLAB='FYP') AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
IF (ISNULL(@SumFypGl,1)=1)
BEGIN
SET @SumFypGl=0;
END
SET @Fyp= @Fyp + @FypGl
SET @SumFyp= @SumFyp + @SumFypGl
END;
FETCH NEXT FROM FINDLEADERTOTAL INTO @GLTLBPO_CODE;
END;
CLOSE FINDLEADERTOTAL;
DEALLOCATE FINDLEADERTOTAL;
END
IF @RPT_REPORT_NAME='GL/TL/BPO'
BEGIN
SELECT @Fyp=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE (PID_AGT_CODE=@AgentCode) AND (PID_TIME_SLAB='FST' OR PID_TIME_SLAB='FYP') AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
IF (ISNULL(@Fyp,1)=1)
BEGIN
SET @Fyp=0;
END
SELECT @SumFyp=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE ((PID_AGT_CODE=@AgentCode) OR AGENT.AGT_SUPER_CODE=@LeaderId) AND (PID_TIME_SLAB='FST' OR PID_TIME_SLAB='FYP') AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
IF (ISNULL(@SumFyp,1)=1)
BEGIN
SET @SumFyp=0;
END
END
IF @RPT_REPORT_NAME='ADV'
BEGIN
SELECT @Fyp=sum(PID_RECEIPT_AMT) FROM PId_ACC WHERE PID_AGT_CODE=@AgentCode AND (PID_TIME_SLAB='FST' OR PID_TIME_SLAB='FYP') AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
IF (ISNULL(@Fyp,1)=1)
BEGIN
SET @Fyp=0;
END
SELECT @SumFyp=sum(PID_RECEIPT_AMT) FROM PId_ACC WHERE PID_AGT_CODE=@AgentCode AND (PID_TIME_SLAB='FST' OR PID_TIME_SLAB='FYP') AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
IF (ISNULL(@SumFyp,1)=1)
BEGIN
SET @SumFyp=0;
END
END
END
IF @ReportType_Code='RNW'
BEGIN
IF @RPT_REPORT_NAME='FM/AFM'
BEGIN
SELECT @Fyp= SUM(PID_ACC.PID_RECEIPT_AMT) FROM PID_ACC INNER JOIN AGENT ON PID_ACC.PID_AGT_CODE = AGENT.AGT_CODE
WHERE ((PID_ACC.PID_AGT_CODE = @AgentCode) OR (AGENT.AGT_SUPER_CODE = @LeaderID)) AND (PID_TIME_SLAB like'RNW') AND (PID_ACC.PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
IF (ISNULL(@Fyp,1)=1)
BEGIN
SET @Fyp=0;
END
SELECT @SumFyp= SUM(PID_ACC.PID_RECEIPT_AMT) FROM PID_ACC INNER JOIN AGENT ON PID_ACC.PID_AGT_CODE = AGENT.AGT_CODE
WHERE ((PID_ACC.PID_AGT_CODE = @AgentCode) OR (AGENT.AGT_SUPER_CODE = @LeaderID)) AND (PID_TIME_SLAB LIKE'RNW') AND (PID_ACC.PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
IF (ISNULL(@SumFyp,1)=1)
BEGIN
SET @SumFyp=0;
END
DECLARE FINDLEADERTOTAL CURSOR
FOR
SELECT AGT_CODE FROM AGENT WHERE AGT_SUPER_CODE=@LeaderID
OPEN FINDLEADERTOTAL
FETCH NEXT FROM FINDLEADERTOTAL INTO @GLTLBPO_CODE;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SELECT @LeaderGl_ID= AGT_ID FROM AGENT WHERE AGT_CODE =@GLTLBPO_CODE
SELECT @FypGl=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE (AGENT.AGT_SUPER_CODE =@LeaderGl_ID) AND (PID_TIME_SLAB LIKE 'RNW') AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
IF (ISNULL(@FypGl,1)=1)
BEGIN
SET @FypGl=0;
END
SELECT @SumFypGl=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE (AGENT.AGT_SUPER_CODE =@LeaderGl_ID) AND (PID_TIME_SLAB LIKE 'RNW') AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
IF (ISNULL(@SumFypGl,1)=1)
BEGIN
SET @SumFypGl=0;
END
SET @Fyp= @Fyp + @FypGl
SET @SumFyp= @SumFyp + @SumFypGl
END;
FETCH NEXT FROM FINDLEADERTOTAL INTO @GLTLBPO_CODE;
END;
CLOSE FINDLEADERTOTAL;
DEALLOCATE FINDLEADERTOTAL;
END
IF @RPT_REPORT_NAME='GL/TL/BPO'
BEGIN
SELECT @Fyp=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE (PID_AGT_CODE=@AgentCode) AND (PID_TIME_SLAB LIKE 'RNW') AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
IF (ISNULL(@Fyp,1)=1)
BEGIN
SET @Fyp=0;
END
SELECT @SumFyp=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE ((PID_AGT_CODE=@AgentCode) OR AGENT.AGT_SUPER_CODE=@LeaderId) AND (PID_TIME_SLAB LIKE 'RNW') AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
IF (ISNULL(@SumFyp,1)=1)
BEGIN
SET @SumFyp=0;
END
END
IF @RPT_REPORT_NAME='ADV'
BEGIN
SELECT @Fyp=sum(PID_RECEIPT_AMT) FROM PId_ACC WHERE PID_AGT_CODE=@AgentCode AND (PID_TIME_SLAB LIKE 'RNW') AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
IF (ISNULL(@Fyp,1)=1)
BEGIN
SET @Fyp=0;
END
SELECT @SumFyp=sum(PID_RECEIPT_AMT) FROM PId_ACC WHERE PID_AGT_CODE=@AgentCode AND (PID_TIME_SLAB LIKE 'RNW') AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
IF (ISNULL(@SumFyp,1)=1)
BEGIN
SET @SumFyp=0;
END
END
END
IF @ReportType_Code='SP'
BEGIN
IF @RPT_REPORT_NAME='FM/AFM'
BEGIN
SELECT @Fyp= SUM(PID_ACC.PID_RECEIPT_AMT) FROM PID_ACC INNER JOIN AGENT ON PID_ACC.PID_AGT_CODE = AGENT.AGT_CODE
WHERE ((PID_ACC.PID_AGT_CODE = @AgentCode) OR (AGENT.AGT_SUPER_CODE = @LeaderID)) AND (PID_TIME_SLAB='SP') AND (PID_ACC.PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE) AND(PID_TABLE='6')
IF (ISNULL(@Fyp,1)=1)
BEGIN
SET @Fyp=0;
END
SELECT @SumFyp= SUM(PID_ACC.PID_RECEIPT_AMT) FROM PID_ACC INNER JOIN AGENT ON PID_ACC.PID_AGT_CODE = AGENT.AGT_CODE
WHERE ((PID_ACC.PID_AGT_CODE = @AgentCode) OR (AGENT.AGT_SUPER_CODE = @LeaderID)) AND (PID_TIME_SLAB='SP') AND (PID_ACC.PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE) AND(PID_TABLE='6')
IF (ISNULL(@SumFyp,1)=1)
BEGIN
SET @SumFyp=0;
END
DECLARE FINDLEADERTOTAL CURSOR
FOR
SELECT AGT_CODE FROM AGENT WHERE AGT_SUPER_CODE=@LeaderID
OPEN FINDLEADERTOTAL
FETCH NEXT FROM FINDLEADERTOTAL INTO @GLTLBPO_CODE;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SELECT @LeaderGl_ID= AGT_ID FROM AGENT WHERE AGT_CODE =@GLTLBPO_CODE
SELECT @FypGl=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE (AGENT.AGT_SUPER_CODE =@LeaderGl_ID) AND (PID_TIME_SLAB='SP') AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE) AND (PID_TABLE='6')
IF (ISNULL(@FypGl,1)=1)
BEGIN
SET @FypGl=0;
END
SELECT @SumFypGl=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE (AGENT.AGT_SUPER_CODE =@LeaderGl_ID) AND (PID_TIME_SLAB='SP') AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE) AND (PID_TABLE='6')
IF (ISNULL(@SumFypGl,1)=1)
BEGIN
SET @SumFypGl=0;
END
SET @Fyp= @Fyp + @FypGl
SET @SumFyp= @SumFyp + @SumFypGl
END;
FETCH NEXT FROM FINDLEADERTOTAL INTO @GLTLBPO_CODE;
END;
CLOSE FINDLEADERTOTAL;
DEALLOCATE FINDLEADERTOTAL;
END
IF @RPT_REPORT_NAME='GL/TL/BPO'
BEGIN
SELECT @Fyp=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE (PID_AGT_CODE=@AgentCode) AND (PID_TIME_SLAB='SP') AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)AND(PID_TABLE='6')
IF (ISNULL(@Fyp,1)=1)
BEGIN
SET @Fyp=0;
END
SELECT @SumFyp=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE ((PID_AGT_CODE=@AgentCode) OR AGENT.AGT_SUPER_CODE=@LeaderId) AND (PID_TIME_SLAB='SP') AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)AND(PID_TABLE='6')
IF (ISNULL(@SumFyp,1)=1)
BEGIN
SET @SumFyp=0;
END
END
IF @RPT_REPORT_NAME='ADV'
BEGIN
SELECT @Fyp=sum(PID_RECEIPT_AMT) FROM PId_ACC WHERE PID_AGT_CODE=@AgentCode AND (PID_TIME_SLAB='SP') AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)AND(PID_TABLE='6')
IF (ISNULL(@Fyp,1)=1)
BEGIN
SET @Fyp=0;
END
SELECT @SumFyp=sum(PID_RECEIPT_AMT) FROM PId_ACC WHERE PID_AGT_CODE=@AgentCode AND (PID_TIME_SLAB='SP') AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)AND(PID_TABLE='6')
IF (ISNULL(@SumFyp,1)=1)
BEGIN
SET @SumFyp=0;
END
END
END
IF @ReportType_Code='MRP'
BEGIN
IF @RPT_REPORT_NAME='FM/AFM'
BEGIN
SELECT @Fyp= SUM(PID_ACC.PID_RECEIPT_AMT) FROM PID_ACC INNER JOIN AGENT ON PID_ACC.PID_AGT_CODE = AGENT.AGT_CODE
WHERE ((PID_ACC.PID_AGT_CODE = @AgentCode) OR (AGENT.AGT_SUPER_CODE = @LeaderID)) AND (PID_TIME_SLAB='MRP') AND (PID_ACC.PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
IF (ISNULL(@Fyp,1)=1)
BEGIN
SET @Fyp=0;
END
SELECT @SumFyp= SUM(PID_ACC.PID_RECEIPT_AMT) FROM PID_ACC INNER JOIN AGENT ON PID_ACC.PID_AGT_CODE = AGENT.AGT_CODE
WHERE ((PID_ACC.PID_AGT_CODE = @AgentCode) OR (AGENT.AGT_SUPER_CODE = @LeaderID)) AND (PID_TIME_SLAB='MRP') AND (PID_ACC.PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
IF (ISNULL(@SumFyp,1)=1)
BEGIN
SET @SumFyp=0;
END
DECLARE FINDLEADERTOTAL CURSOR
FOR
SELECT AGT_CODE FROM AGENT WHERE AGT_SUPER_CODE=@LeaderID
OPEN FINDLEADERTOTAL
FETCH NEXT FROM FINDLEADERTOTAL INTO @GLTLBPO_CODE;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SELECT @LeaderGl_ID= AGT_ID FROM AGENT WHERE AGT_CODE =@GLTLBPO_CODE
SELECT @FypGl=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE (AGENT.AGT_SUPER_CODE =@LeaderGl_ID) AND (PID_TIME_SLAB='MRP') AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
IF (ISNULL(@FypGl,1)=1)
BEGIN
SET @FypGl=0;
END
SELECT @SumFypGl=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE (AGENT.AGT_SUPER_CODE =@LeaderGl_ID) AND (PID_TIME_SLAB='MRP') AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
IF (ISNULL(@SumFypGl,1)=1)
BEGIN
SET @SumFypGl=0;
END
SET @Fyp= @Fyp + @FypGl
SET @SumFyp= @SumFyp + @SumFypGl
END;
FETCH NEXT FROM FINDLEADERTOTAL INTO @GLTLBPO_CODE;
END;
CLOSE FINDLEADERTOTAL;
DEALLOCATE FINDLEADERTOTAL;
END
IF @RPT_REPORT_NAME='GL/TL/BPO'
BEGIN
SELECT @Fyp=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE (PID_AGT_CODE=@AgentCode) AND (PID_TIME_SLAB='MRP') AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
IF (ISNULL(@Fyp,1)=1)
BEGIN
SET @Fyp=0;
END
SELECT @SumFyp=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE ((PID_AGT_CODE=@AgentCode) OR AGENT.AGT_SUPER_CODE=@LeaderId) AND (PID_TIME_SLAB='MRP') AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
IF (ISNULL(@SumFyp,1)=1)
BEGIN
SET @SumFyp=0;
END
END
IF @RPT_REPORT_NAME='ADV'
BEGIN
SELECT @Fyp=sum(PID_RECEIPT_AMT) FROM PId_ACC WHERE PID_AGT_CODE=@AgentCode AND (PID_TIME_SLAB='MRP') AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
IF (ISNULL(@Fyp,1)=1)
BEGIN
SET @Fyp=0;
END
SELECT @SumFyp=sum(PID_RECEIPT_AMT) FROM PId_ACC WHERE PID_AGT_CODE=@AgentCode AND (PID_TIME_SLAB='MRP') AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
IF (ISNULL(@SumFyp,1)=1)
BEGIN
SET @SumFyp=0;
END
END
END
IF @ReportType_Code='GLP'
BEGIN
IF @RPT_REPORT_NAME='FM/AFM'
BEGIN
SELECT @Fyp= SUM(PID_ACC.PID_RECEIPT_AMT) FROM PID_ACC INNER JOIN AGENT ON PID_ACC.PID_AGT_CODE = AGENT.AGT_CODE
WHERE ((PID_ACC.PID_AGT_CODE = @AgentCode) OR (AGENT.AGT_SUPER_CODE = @LeaderID)) AND (PID_TIME_SLAB='GLP') AND (PID_ACC.PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
IF (ISNULL(@Fyp,1)=1)
BEGIN
SET @Fyp=0;
END
SELECT @SumFyp= SUM(PID_ACC.PID_RECEIPT_AMT) FROM PID_ACC INNER JOIN AGENT ON PID_ACC.PID_AGT_CODE = AGENT.AGT_CODE
WHERE ((PID_ACC.PID_AGT_CODE = @AgentCode) OR (AGENT.AGT_SUPER_CODE = @LeaderID)) AND (PID_TIME_SLAB='GLP') AND (PID_ACC.PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
IF (ISNULL(@SumFyp,1)=1)
BEGIN
SET @SumFyp=0;
END
DECLARE FINDLEADERTOTAL CURSOR
FOR
SELECT AGT_CODE FROM AGENT WHERE AGT_SUPER_CODE=@LeaderID
OPEN FINDLEADERTOTAL
FETCH NEXT FROM FINDLEADERTOTAL INTO @GLTLBPO_CODE;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SELECT @LeaderGl_ID= AGT_ID FROM AGENT WHERE AGT_CODE =@GLTLBPO_CODE
SELECT @FypGl=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE (AGENT.AGT_SUPER_CODE =@LeaderGl_ID) AND (PID_TIME_SLAB='GLP') AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
IF (ISNULL(@FypGl,1)=1)
BEGIN
SET @FypGl=0;
END
SELECT @SumFypGl=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE (AGENT.AGT_SUPER_CODE =@LeaderGl_ID) AND (PID_TIME_SLAB='GLP') AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
IF (ISNULL(@SumFypGl,1)=1)
BEGIN
SET @SumFypGl=0;
END
SET @Fyp= @Fyp + @FypGl
SET @SumFyp= @SumFyp + @SumFypGl
END;
FETCH NEXT FROM FINDLEADERTOTAL INTO @GLTLBPO_CODE;
END;
CLOSE FINDLEADERTOTAL;
DEALLOCATE FINDLEADERTOTAL;
END
IF @RPT_REPORT_NAME='GL/TL/BPO'
BEGIN
SELECT @Fyp=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE (PID_AGT_CODE=@AgentCode) AND (PID_TIME_SLAB='GLP') AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
IF (ISNULL(@Fyp,1)=1)
BEGIN
SET @Fyp=0;
END
SELECT @SumFyp=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE ((PID_AGT_CODE=@AgentCode) OR AGENT.AGT_SUPER_CODE=@LeaderId) AND (PID_TIME_SLAB='GLP') AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
IF (ISNULL(@SumFyp,1)=1)
BEGIN
SET @SumFyp=0;
END
END
IF @RPT_REPORT_NAME='ADV'
BEGIN
SELECT @Fyp=sum(PID_RECEIPT_AMT) FROM PId_ACC WHERE PID_AGT_CODE=@AgentCode AND (PID_TIME_SLAB='GLP') AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
IF (ISNULL(@Fyp,1)=1)
BEGIN
SET @Fyp=0;
END
SELECT @SumFyp=sum(PID_RECEIPT_AMT) FROM PId_ACC WHERE PID_AGT_CODE=@AgentCode AND (PID_TIME_SLAB='GLP') AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
IF (ISNULL(@SumFyp,1)=1)
BEGIN
SET @SumFyp=0;
END
END
END
IF @ReportType_Code='MCR'
BEGIN
IF @RPT_REPORT_NAME='FM/AFM'
BEGIN
SELECT @Fyp= SUM(PID_ACC.PID_RECEIPT_AMT) FROM PID_ACC INNER JOIN AGENT ON PID_ACC.PID_AGT_CODE = AGENT.AGT_CODE
WHERE ((PID_ACC.PID_AGT_CODE = @AgentCode) OR (AGENT.AGT_SUPER_CODE = @LeaderID)) AND (PID_TIME_SLAB='MCR') AND (PID_ACC.PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
IF (ISNULL(@Fyp,1)=1)
BEGIN
SET @Fyp=0;
END
SELECT @SumFyp= SUM(PID_ACC.PID_RECEIPT_AMT) FROM PID_ACC INNER JOIN AGENT ON PID_ACC.PID_AGT_CODE = AGENT.AGT_CODE
WHERE ((PID_ACC.PID_AGT_CODE = @AgentCode) OR (AGENT.AGT_SUPER_CODE = @LeaderID)) AND (PID_TIME_SLAB='MCR') AND (PID_ACC.PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
IF (ISNULL(@SumFyp,1)=1)
BEGIN
SET @SumFyp=0;
END
DECLARE FINDLEADERTOTAL CURSOR
FOR
SELECT AGT_CODE FROM AGENT WHERE AGT_SUPER_CODE=@LeaderID
OPEN FINDLEADERTOTAL
FETCH NEXT FROM FINDLEADERTOTAL INTO @GLTLBPO_CODE;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SELECT @LeaderGl_ID= AGT_ID FROM AGENT WHERE AGT_CODE =@GLTLBPO_CODE
SELECT @FypGl=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE (AGENT.AGT_SUPER_CODE =@LeaderGl_ID) AND (PID_TIME_SLAB='MCR') AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
IF (ISNULL(@FypGl,1)=1)
BEGIN
SET @FypGl=0;
END
SELECT @SumFypGl=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE (AGENT.AGT_SUPER_CODE =@LeaderGl_ID) AND (PID_TIME_SLAB='MCR') AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
IF (ISNULL(@SumFypGl,1)=1)
BEGIN
SET @SumFypGl=0;
END
SET @Fyp= @Fyp + @FypGl
SET @SumFyp= @SumFyp + @SumFypGl
END;
FETCH NEXT FROM FINDLEADERTOTAL INTO @GLTLBPO_CODE;
END;
CLOSE FINDLEADERTOTAL;
DEALLOCATE FINDLEADERTOTAL;
END
IF @RPT_REPORT_NAME='GL/TL/BPO'
BEGIN
SELECT @Fyp=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE (PID_AGT_CODE=@AgentCode) AND (PID_TIME_SLAB='MCR') AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
IF (ISNULL(@Fyp,1)=1)
BEGIN
SET @Fyp=0;
END
SELECT @SumFyp=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE ((PID_AGT_CODE=@AgentCode) OR (AGENT.AGT_SUPER_CODE=@LeaderId)) AND (PID_TIME_SLAB='MCR') AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
IF (ISNULL(@SumFyp,1)=1)
BEGIN
SET @SumFyp=0;
END
END
IF @RPT_REPORT_NAME='ADV'
BEGIN
SELECT @Fyp=sum(PID_RECEIPT_AMT) FROM PId_ACC WHERE PID_AGT_CODE=@AgentCode AND (PID_TIME_SLAB='MCR') AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
IF (ISNULL(@Fyp,1)=1)
BEGIN
SET @Fyp=0;
END
SELECT @SumFyp=sum(PID_RECEIPT_AMT) FROM PId_ACC WHERE PID_AGT_CODE=@AgentCode AND (PID_TIME_SLAB='MCR') AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
IF (ISNULL(@SumFyp,1)=1)
BEGIN
SET @SumFyp=0;
END
END
END
IF @ReportType_Code='GWP'
BEGIN
IF @RPT_REPORT_NAME='FM/AFM'
BEGIN
SELECT @Fyp= SUM(PID_ACC.PID_RECEIPT_AMT) FROM PID_ACC INNER JOIN AGENT ON PID_ACC.PID_AGT_CODE = AGENT.AGT_CODE
WHERE ((PID_ACC.PID_AGT_CODE = @AgentCode) OR (AGENT.AGT_SUPER_CODE = @LeaderID)) AND (PID_TIME_SLAB='FST' OR PID_TIME_SLAB='FYP' OR PID_TIME_SLAB='RNW'OR PID_TIME_SLAB='MRP' OR PID_TIME_SLAB='GLP' OR PID_TIME_SLAB='MCR') AND (PID_ACC.PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
IF (ISNULL(@Fyp,1)=1)
BEGIN
SET @Fyp=0;
END
SELECT @SumFyp= SUM(PID_ACC.PID_RECEIPT_AMT) FROM PID_ACC INNER JOIN AGENT ON PID_ACC.PID_AGT_CODE = AGENT.AGT_CODE
WHERE ((PID_ACC.PID_AGT_CODE = @AgentCode) OR (AGENT.AGT_SUPER_CODE = @LeaderID)) AND (PID_TIME_SLAB='FST' OR PID_TIME_SLAB='FYP' OR PID_TIME_SLAB='RNW'OR PID_TIME_SLAB='MRP' OR PID_TIME_SLAB='GLP' OR PID_TIME_SLAB='MCR') AND (PID_ACC.PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
IF (ISNULL(@SumFyp,1)=1)
BEGIN
SET @SumFyp=0;
END
DECLARE FINDLEADERTOTAL CURSOR
FOR
SELECT AGT_CODE FROM AGENT WHERE AGT_SUPER_CODE=@LeaderID
OPEN FINDLEADERTOTAL
FETCH NEXT FROM FINDLEADERTOTAL INTO @GLTLBPO_CODE;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SELECT @LeaderGl_ID= AGT_ID FROM AGENT WHERE AGT_CODE =@GLTLBPO_CODE
SELECT @FypGl=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE (AGENT.AGT_SUPER_CODE =@LeaderGl_ID) AND (PID_TIME_SLAB='FST' OR PID_TIME_SLAB='FYP' OR PID_TIME_SLAB='RNW'OR PID_TIME_SLAB='MRP' OR PID_TIME_SLAB='GLP' OR PID_TIME_SLAB='MCR') AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
IF (ISNULL(@FypGl,1)=1)
BEGIN
SET @FypGl=0;
END
SELECT @SumFypGl=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE (AGENT.AGT_SUPER_CODE =@LeaderGl_ID) AND(PID_TIME_SLAB='FST' OR PID_TIME_SLAB='FYP' OR PID_TIME_SLAB='RNW'OR PID_TIME_SLAB='MRP' OR PID_TIME_SLAB='GLP' OR PID_TIME_SLAB='MCR') AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
IF (ISNULL(@SumFypGl,1)=1)
BEGIN
SET @SumFypGl=0;
END
SET @Fyp= @Fyp + @FypGl
SET @SumFyp= @SumFyp + @SumFypGl
END;
FETCH NEXT FROM FINDLEADERTOTAL INTO @GLTLBPO_CODE;
END;
CLOSE FINDLEADERTOTAL;
DEALLOCATE FINDLEADERTOTAL;
END
IF @RPT_REPORT_NAME='GL/TL/BPO'
BEGIN
SELECT @Fyp=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE (PID_AGT_CODE=@AgentCode) AND (PID_TIME_SLAB='FST' OR PID_TIME_SLAB='FYP' OR PID_TIME_SLAB='RNW'OR PID_TIME_SLAB='MRP' OR PID_TIME_SLAB='GLP' OR PID_TIME_SLAB='MCR') AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
IF (ISNULL(@Fyp,1)=1)
BEGIN
SET @Fyp=0;
END
SELECT @SumFyp=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE ((PID_AGT_CODE=@AgentCode) OR AGENT.AGT_SUPER_CODE=@LeaderId) AND (PID_TIME_SLAB='FST' OR PID_TIME_SLAB='FYP' OR PID_TIME_SLAB='RNW'OR PID_TIME_SLAB='MRP' OR PID_TIME_SLAB='GLP' OR PID_TIME_SLAB='MCR') AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
IF (ISNULL(@SumFyp,1)=1)
BEGIN
SET @SumFyp=0;
END
END
IF @RPT_REPORT_NAME='ADV'
BEGIN
SELECT @Fyp=sum(PID_RECEIPT_AMT) FROM PId_ACC WHERE PID_AGT_CODE=@AgentCode AND (PID_TIME_SLAB='FST' OR PID_TIME_SLAB='FYP' OR PID_TIME_SLAB='RNW'OR PID_TIME_SLAB='MRP' OR PID_TIME_SLAB='GLP' OR PID_TIME_SLAB='MCR') AND (PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE)
IF (ISNULL(@Fyp,1)=1)
BEGIN
SET @Fyp=0;
END
SELECT @SumFyp=sum(PID_RECEIPT_AMT) FROM PId_ACC WHERE PID_AGT_CODE=@AgentCode AND (PID_TIME_SLAB='FST' OR PID_TIME_SLAB='FYP' OR PID_TIME_SLAB='RNW' OR PID_TIME_SLAB='MRP' OR PID_TIME_SLAB='GLP' OR PID_TIME_SLAB='MCR') AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
IF (ISNULL(@SumFyp,1)=1)
BEGIN
SET @SumFyp=0;
END
END
END
--Insert into TEMP_REPORTS_DATA_TABLE (BRANCH_CODE,AGENT_CODE,LEADER_CODE,AGENT_NAME,FYP,SUMFYP,BRANCH,USER_ID,Month) values (@Branch,@Agent_Code,@Leader_Code,@Agent_Name,@FYP,@SumFyp,@Branch_Name,@USER_ID,@RPT_REPORT_NAME)
Insert into TEMP_REPORTS_DATA_TABLE (BRANCH_CODE,AGENT_CODE,LEADER_CODE,AGENT_NAME,FYP,SUMFYP,BRANCH,USER_ID,Month) values (@Branch,@AgentCode,@Leader_Code,@Agent_Name,@FYP,@SumFyp,@Branch_Name,@USER_ID,@RPT_REPORT_NAME)
set @LeaderId=0;
END;
FETCH NEXT FROM Branch_Agents INTO @AgentCode;
END;
CLOSE Branch_Agents;
DEALLOCATE Branch_Agents;
END;
FETCH NEXT FROM BRANCH_CHILD INTO @PID_BRANCH;
END;
CLOSE BRANCH_CHILD;
DEALLOCATE BRANCH_CHILD;
RETURN 1;
GO
October 23, 2007 at 7:05 am
Yikes! :blink:
A few things noticed before my eyes went blank...
Cursor - get rid of it. Now, I didn't read close enough what it actually did, but I'd be surprised if it can't be removed.
Datatypes - be consistent with your typing.. There seems to be a lot of implicit conversions everywhere... (eg @year varchar(50) ???)
IF's - seems to be a 'do it all' proc... By having all those different IF's and different statements sprinkled around, it's not likely you'll ever see an optimal plan from this proc.
(not to mention it'll be easier to read) 😉
Have you considered breaking out the different reports into separate procedures instead of all those IF branchings?
/Kenneth
October 23, 2007 at 7:38 am
What you seem to need is a CROSSTAB/PIVOT like thisCREATE PROCEDURE dbo.VIEW_REPORTS
(
@BRC_START_DATE DATETIME,
@BRC_END_DATE DATETIME,
@Hild_Branch VARCHAR(50),
@REPORTSIZE INT,
@RPT_REPORT_NAME VARCHAR(10),
@USER_ID INT,
@ReportType_Code NVARCHAR(50)
)
AS
SET NOCOUNT ON
DECLARE @MonthFirstDate DATETIME
SET @MonthFirstDate = DATEADD(MONTH, DATEDIFF(MONTH, @BRC_END_DATE, '19000101'), '19000101')
SELECT b.BRC_BRANCH_CODE,
sum(case when c.AGT_CODE = acc.PID_AGT_CODE AND acc.PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE then acc.PID_RECEIPT_AMT else 0 end) AS ModeMonth
sum(case when c.AGT_CODE = acc.PID_AGT_CODE AND acc.PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE then acc.PID_RECEIPT_AMT else 0 end) AS ModeQuarter
sum(case when c.AGT_CODE = acc.PID_AGT_CODE AND acc.PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE then acc.PID_RECEIPT_AMT else 0 end) AS ModeHalfYear
sum(case when c.AGT_CODE = acc.PID_AGT_CODE AND acc.PID_RECEIPT_DATE BETWEEN @MonthFirstdate AND @BRC_END_DATE then acc.PID_RECEIPT_AMT else 0 end) AS ModeYear
sum(case when c.AGT_CODE = acc.PID_AGT_CODE AND (acc.PID_AGT_CODE = @AgentCode OR c.AGT_SUPER_CODE = @LeaderId) AND acc.PID_PAY_FREQ = 'M' AND acc.PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE then acc.PID_RECEIPT_AMT else 0 end) AS SumModeMonth
sum(case when c.AGT_CODE = acc.PID_AGT_CODE AND (acc.PID_AGT_CODE = @AgentCode OR c.AGT_SUPER_CODE = @LeaderId) AND acc.PID_PAY_FREQ = 'Q' AND acc.PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE then acc.PID_RECEIPT_AMT else 0 end) AS SumModeQuarter
sum(case when c.AGT_CODE = acc.PID_AGT_CODE AND (acc.PID_AGT_CODE = @AgentCode OR c.AGT_SUPER_CODE = @LeaderId) AND acc.PID_PAY_FREQ = 'HY' AND acc.PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE then acc.PID_RECEIPT_AMT else 0 end) AS SumModeHalfYear
sum(case when c.AGT_CODE = acc.PID_AGT_CODE AND (acc.PID_AGT_CODE = @AgentCode OR c.AGT_SUPER_CODE = @LeaderId) AND acc.PID_PAY_FREQ = 'A' AND acc.PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE then acc.PID_RECEIPT_AMT else 0 end) AS SumModeYear
FROM (
SELECT e1.BRC_BRANCH_CODE,
e1.BRC_BRANCH_NAME
FROM BRANCH_CODE AS e1
LEFT JOIN BRANCH_CODE AS e2 ON e2.BRC_BRANCH_CODE = e1.BRC_SUPPER_CODE
WHERE @Hild_Branch = 'NAT'
UNION
SELECT e1.BRC_BRANCH_CODE,
e1.BRC_BRANCH_NAME
FROM BRANCH_CODE AS e1
LEFT JOIN BRANCH_CODE AS e2 ON e2.BRC_BRANCH_CODE = e1.BRC_SUPPER_CODE
WHERE @Hild_Branch IN (e1.BRC_SUPPER_CODE, e2.BRC_SUPPER_CODE)
AND @Hild_Branch <> 'NAT'
) AS b
LEFT JOIN (
SELECT AGT_CODE,
AGT_FIRST_NAME,
AGT_LAST_NAME,
AGT_TITLE,
AGT_SUB_CODE AS LeaderCode,
AGT_TITLE + ' ' + AGT_FIRST_NAME + ' ' + AGT_LAST_NAME AS AgentName,
AGT_ID AS LeaderID
FROM AGENT
WHERE (AGT_SUB_CODE LIKE '%GL%' OR AGT_SUB_CODE LIKE '%TL%' OR AGT_SUB_CODE LIKE '%BPO%')
AND @RPT_REPORT_NAME = 'GL/TL/BPO'
UNION
SELECT AGT_CODE,
AGT_FIRST_NAME,
AGT_LAST_NAME,
AGT_TITLE,
AGT_SUB_CODE AS LeaderCode,
AGT_TITLE + ' ' + AGT_FIRST_NAME + ' ' + AGT_LAST_NAME AS AgentName,
AGT_ID AS LeaderID
FROM AGENT
WHERE (AGT_SUB_CODE LIKE '%FM%' OR AGT_SUB_CODE LIKE '%AFM%')
AND @RPT_REPORT_NAME = 'FM/AFM'
UNION
SELECT AGT_CODE,
AGT_FIRST_NAME,
AGT_LAST_NAME,
AGT_TITLE,
AGT_SUB_CODE AS LeaderCode,
AGT_TITLE + ' ' + AGT_FIRST_NAME + ' ' + AGT_LAST_NAME AS AgentName,
AGT_ID AS LeaderID
FROM AGENT
WHERE AGT_SUB_CODE IS NULL
AND @RPT_REPORT_NAME = 'ADV'
) AS c ON c.AGT_BRANCH_CODE = b.BRC_BRANCH_CODE
LEFT JOIN PID_ACC AS acc ON acc.PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE
GROUP BY b.BRC_BRANCH_CODE
N 56°04'39.16"
E 12°55'05.25"
October 23, 2007 at 7:39 am
Thanks god, I've managed to read the entire code....:hehe:
1. 10+ cursors, get rid of it
2. Repeative code such as
Select @Agent_FirstName=AGT_FIRST_NAME, @Agent_LastName=AGT_LAST_NAME ,@AGT_TITLE=AGT_TITLE From AGENT Where AGT_CODE=@AgentCode
Select @Leader_Code=AGT_SUB_CODE From AGENT Where AGT_CODE=@AgentCode
set @Agent_Name=@AGT_TITLE+' '+ + @Agent_FirstName+' '+ + @Agent_LastName
SELECT @LeaderID= AGT_ID FROM AGENT WHERE AGT_CODE =@AgentCode
can be written into a single statement like
Select @Agent_FirstName=AGT_FIRST_NAME, @Agent_LastName=AGT_LAST_NAME ,@AGT_TITLE=AGT_TITLE, @Leader_Code=AGT_SUB_CODE, @LeaderID= AGT_ID
From AGENT Where AGT_CODE=@AgentCode
set @Agent_Name=@AGT_TITLE+' '+ + @Agent_FirstName+' '+ + @Agent_LastName
3. Too many reports in a single procedure...Create a separate procedure for each report
4. Multiple aggregate statements can be combined into a single statement like
SELECT @SumModeMonth_Adv=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE (AGENT.AGT_SUPER_CODE=@LeaderId) AND (PID_PAY_FREQ='M' ) AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
SELECT @SumQuarter_Adv=sum(PID_RECEIPT_AMT) FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE (AGENT.AGT_SUPER_CODE=@LeaderId) AND (PID_PAY_FREQ='Q' ) AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
SELECT @SumModeMonth_Adv=sum( CASE WHEN PID_PAY_FREQ ='M' THEN PID_RECEIPT_AMT ELSE 0 END ) ,@SumQuarter_Adv=sum( CASE WHEN PID_PAY_FREQ ='Q' THEN PID_RECEIPT_AMT ELSE 0 END )
FROM AGENT INNER JOIN PID_ACC ON AGENT.AGT_CODE = PID_ACC.PID_AGT_CODE WHERE (AGENT.AGT_SUPER_CODE=@LeaderId) AND (PID_RECEIPT_DATE BETWEEN @BRC_START_DATE AND @BRC_END_DATE)
5. Remove the code that doesn't make sense....
IF (ISNULL(@SumModeMonth,1)=1)
BEGIN
SET @SumModeMonth=0;
END
6. You need to spent some more time learning T-SQL...
--Ramesh
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply