create Procedure spAgencyPerformance (  
@AgencyInternalNo int = null,  
@FinancialYearWise bit = 1  
)  
as  
set nocount on  
begin  
  Declare @iMonth int  
     
  if (@FinancialYearWise = 0) and (@AgencyInternalNo > 0) begin  
    Select @iMonth = Month(agency.agentsince) from Agency where Agency.AgencyInternalNo = @AgencyInternalNo  
    if (@iMonth is null) or (@iMonth = 0) begin  
      select @iMonth = 4  
    end  
  end else begin  
    Select @iMonth = 4  
  end  
    
  if (@AgencyInternalNo is null) or (@AgencyInternalno = 0) begin  
    select TempAgencyPerformance.YearNo, TempAgencyPerformance.PolicyStatus, TempAgencyPerformance.MonthYr,   
           TempAgencyPerformance.YearYr, PolicySummary.TotalSumAssured, PolicySummary.NoOfPolicy, LiveSummary.NoOfLives,  
           Agency.AgencyName, CommissionFirstYear.SumComm1, CommissionSubsequentYear.SumComm2, tmpPremiumDeposit.TotalPremium,  
           PolicySummary.FPI  
      From (select distinct  
              case  
                when month(Date) >= 4  then Cast((Year(Date)) As Varchar(10)) +'-' + Cast((Year(Date)+1) As Varchar(10))  
                else Cast((Year(Date)-1) As Varchar(10)) +'-'+ Cast((Year(Date)) As Varchar(10))  
              end YearNo, ' ' PolicyStatus, Null MonthYr, Null YearYr, PolicyProposal.AgencyInternalNo  
             From PolicyProposal  
            Join InsuranceTransaction On PolicyProposal.POlicyPropInternalNo = InsuranceTransaction.TransactionInternalNo  
           ) TempAgencyPerformance  
      Join Agency on TempAgencyPerformance.AgencyInternalNo = Agency.AgencyInternalNo  
      
      
      
      left Join (select case  
                          when month(Date) >= 4   then Cast((Year(Date)) As Varchar(10)) +'-' + Cast((Year(Date)+1) As Varchar(10))  
                          else Cast((Year(Date)-1) As Varchar(10)) +'-'+ Cast((Year(Date)) As Varchar(10))  
                        end YearNo,  PolicyProposal.AgencyInternalNo, Sum(PolicyProposal.SumAssured) TotalSumAssured,  
                        Count(PolicyProposal.PolicyNo) NoOfPolicy, Sum(Premium) FPI  
                   from PolicyProposal  
                   join insuranceTransaction on PolicyProposal.PolicyPropInternalNo = insuranceTransaction.TransactionInternalNo  
                  Group By case  
                          when month(Date) >= 4   then Cast((Year(Date)) As Varchar(10)) +'-' + Cast((Year(Date)+1) As Varchar(10))  
                          else Cast((Year(Date)-1) As Varchar(10)) +'-'+ Cast((Year(Date)) As Varchar(10))  
                        end,  PolicyProposal.AgencyInternalNo  
                ) PolicySummary on TempAgencyPerformance.YearNo = PolicySummary.YearNo  
                               and TempAgencyPerformance.AgencyInternalNo = PolicySummary.AgencyInternalNo  
      
      
      
	  Left Join (Select YearNo, AgencyInternalNo, Count(distinct Lives) NoOfLives
					From
					(
						Select case  
									when month(Date) >= 4   then Cast((Year(Date)) As Varchar(10)) +'-' + Cast((Year(Date)+1) As Varchar(10))  
									else Cast((Year(Date)-1) As Varchar(10)) +'-'+ Cast((Year(Date)) As Varchar(10))  
								end  YearNo,
							PolicyProposal.AgencyInternalNo, 
							IsNull(PolicyProposalHolder.HolderInternalNo, PolicyProposal.HolderInternalNo) Lives 
						From PolicyProposal
		                   Join insuranceTransaction on PolicyProposal.PolicyPropInternalNo = insuranceTransaction.TransactionInternalNo
		                   Left Join PolicyProposalHolder On PolicyProposal.PolicyPropInternalNo = PolicyProposalHolder.PolicyPropInternalNo                 
						Union All		
						Select case  
									when month(Date) >= 4   then Cast((Year(Date)) As Varchar(10)) +'-' + Cast((Year(Date)+1) As Varchar(10))  
									else Cast((Year(Date)-1) As Varchar(10)) +'-'+ Cast((Year(Date)) As Varchar(10))  
								end  YearNo,
								PolicyProposal.AgencyInternalNo, 
						        PolicyProposal.JointHolderInternalNo Lives 
		                From PolicyProposal
					       Join insuranceTransaction on PolicyProposal.PolicyPropInternalNo = insuranceTransaction.TransactionInternalNo
						Where PolicyProposal.JointHolderInternalNo is not null
					) NoOfLives	
				Group By YearNo, AgencyInternalNo 
				)  LiveSummary on TempAgencyPerformance.YearNo = LiveSummary.YearNo
                               and TempAgencyPerformance.AgencyInternalNo = LiveSummary.AgencyInternalNo               
      
      
     
      
      left Join (select case  
                          when Month(cast(Commission.month + ' 1, ' + Cast(commission.Year as varchar(10)) as smalldatetime)) >= 4  then Cast(commission.year As Varchar(10)) +'-' + Cast(commission.year+1 As Varchar(10))  
                          else Cast((commission.Year-1) As Varchar(10)) +'-'+ Cast(commission.Year As Varchar(10))   
                        end YearNo,  Commission.AgencyInternalNo, Sum(commissionDetail.CommissionAmount) SumComm1  
                   From Commission  
                   Join CommissionDetail on commission.commissionInternalNo = commissionDetail.CommissionInternalNo  
                   Join InsuranceTransaction on commissionDetail.PolicyPropInternalNo = InsuranceTransaction.TransactionInternalNo  
                  Where DateDiff(mm, Insurancetransaction.Date, CommissionDetail.DueDate) < 12  
                  Group By case  
                          when Month(cast(Commission.month + ' 1, ' + Cast(commission.Year as varchar(10)) as smalldatetime)) >= 4  then Cast(commission.year As Varchar(10)) +'-' + Cast(commission.year+1 As Varchar(10))  
                          else Cast((commission.Year-1) As Varchar(10)) +'-'+ Cast(commission.Year As Varchar(10))   
                        end,  Commission.AgencyInternalNo  
                 ) CommissionFirstYear on TempAgencyPerformance.YearNo = CommissionFirstYear.YearNo  
                                      and TempAgencyPerformance.AgencyInternalNo = commissionFirstYear.AgencyInternalNo  
      left Join (select case  
                          when Month(cast(Commission.month + ' 1, ' + Cast(commission.Year as varchar(10)) as smalldatetime)) >= 4  then Cast(commission.year As Varchar(10)) +'-' + Cast(commission.year+1 As Varchar(10))  
                          else Cast((commission.Year-1) As Varchar(10)) +'-'+ Cast(commission.Year As Varchar(10))   
                        end YearNo,  Commission.AgencyInternalNo, Sum(commissionDetail.ActualCommission) SumComm2  
                   From Commission  
                   Join CommissionDetail on commission.commissionInternalNo = commissionDetail.CommissionInternalNo  
                   Join InsuranceTransaction on commissionDetail.PolicyPropInternalNo = InsuranceTransaction.TransactionInternalNo  
                  Where DateDiff(mm, Insurancetransaction.Date, CommissionDetail.DueDate) >= 12  
                  Group By case  
                          when Month(cast(Commission.month + ' 1, ' + Cast(commission.Year as varchar(10)) as smalldatetime)) >= 4  then Cast(commission.year As Varchar(10)) +'-' + Cast(commission.year+1 As Varchar(10))  
                          else Cast((commission.Year-1) As Varchar(10)) +'-'+ Cast(commission.Year As Varchar(10))   
                        end,  Commission.AgencyInternalNo  
                 ) CommissionSubsequentYear on TempAgencyPerformance.YearNo = CommissionSubsequentYear.YearNo  
                                         and TempAgencyPerformance.AgencyInternalNo = CommissionSubsequentYear.AgencyInternalNo  
      left Join (Select case  
                          when month(DepositDate) >= 4   then Cast((Year(DepositDate)) As Varchar(10)) +'-' + Cast((Year(DepositDate)+1) As Varchar(10))  
                          else Cast((Year(DepositDate)-1) As Varchar(10)) +'-'+ Cast((Year(DepositDate)) As Varchar(10))  
                     end YearNo, AgencyInternalNo, Sum(Premium) TotalPremium  
                   From (select IsNull(PremiumDepositIT.Date, DateAdd(mm, NoOfRecordInMode.NoOfMonth, DateAdd(yy, tmpYear.id-1, InsuranceTransaction.Date))) DepositDate,   
                                IsNull(PremiumDue.PremiumAmount, PolicyProposal.Premium) Premium, PolicyProposal.AgencyInternalNo  
                           from PolicyProposal   
                           Join InsuranceTransaction on PolicyProposal.PolicyPropInternalNo = InsuranceTransaction.TransactionInternalNo   
                           join NoOfRecordInMode on PolicyProposal.Mode = NoOfRecordInMode.Mode   
                           Cross Join (select distinct basicPremiumInternalNo id   
                                         from BasicPremium   
                                        Where basicPremiumInternalNo <= 100   
                                      )tmpYear   
                           left join premiumDue on PolicyProposal.PolicyPropInternalNo = premiumDue.PolicyPropInternalNo   
                                               and DateAdd(mm, NoOfRecordInMode.NoOfMonth, DateAdd(yy, tmpYear.id-1, InsuranceTransaction.Date)) = PremiumDue.DueDate   
                           left Join (Select Premiumdue.PolicyPropInternalNo, Min(Premiumdue.DueDate) FUP   
                                        From PremiumDue   
                                       Where PremiumDue.AdjustAmount < PremiumDue.PremiumAmount   
                                       Group By Premiumdue.PolicyPropInternalNo   
                                     ) tmpPremiumDue on PolicyProposal.PolicyPropInternalNo = tmpPremiumDue.PolicyPropInternalNo   
                           left Join PremiumDeposit on PremiumDue.PremiumDepositInternalNo = PremiumDeposit.PremiumDepositInternalNo   
                                                   and PremiumDue.PremiumDepositDetailInternalNo = PremiumDeposit.PremiumDepositDetailInternalNo   
                           left Join InsuranceTransaction PremiumDepositIT on PremiumDeposit.PremiumDepositInternalNo = PremiumDepositIT.TransactionInternalNo   
                          Where tmpYear.id <= PolicyProposal.PremiumPayiTerm   
                            and ((DateAdd(mm, NoOfRecordInMode.NoOfMonth, DateAdd(yy, tmpYear.id-1, InsuranceTransaction.Date)) < tmpPremiumdue.FUP) or PolicyProposal.Mode = 'P')
                            and PolicyProposal.AgencyInternalNo = 1  
                        ) tmpPremiumDeposit  
                  group by case  
                             when month(DepositDate) >= 4   then Cast((Year(DepositDate)) As Varchar(10)) +'-' + Cast((Year(DepositDate)+1) As Varchar(10))  
                             else Cast((Year(DepositDate)-1) As Varchar(10)) +'-'+ Cast((Year(DepositDate)) As Varchar(10))  
                           end, AgencyInternalNo  
                ) tmpPremiumDeposit on TempAgencyPerformance.YearNo = tmpPremiumDeposit.YearNo  
                                   and TempAgencyPerformance.AgencyInternalNo = tmpPremiumDeposit.AgencyInternalNo  
    Where Agency.AgencyName <> 'Others'  
    order by Agency.agencyname, 1 Desc  
  end else begin  
select TempAgencyPerformance.YearNo, TempAgencyPerformance.PolicyStatus, TempAgencyPerformance.MonthYr,   
           TempAgencyPerformance.YearYr, PolicySummary.TotalSumAssured, PolicySummary.NoOfPolicy, LiveSummary.NoOfLives,  
           Agency.AgencyName, CommissionFirstYear.SumComm1, CommissionSubsequentYear.SumComm2, tmpPremiumDeposit.TotalPremium,  
           PolicySummary.FPI  
      From (select distinct  
              case  
                when month(Date) >= @iMonth  then Cast((Year(Date)) As Varchar(10)) +'-' + Cast((Year(Date)+1) As Varchar(10))  
                else Cast((Year(Date)-1) As Varchar(10)) +'-'+ Cast((Year(Date)) As Varchar(10))  
              end YearNo, ' ' PolicyStatus, Null MonthYr, Null YearYr, PolicyProposal.AgencyInternalNo  
             From PolicyProposal  
            Join InsuranceTransaction On PolicyProposal.POlicyPropInternalNo = InsuranceTransaction.TransactionInternalNo  
           ) TempAgencyPerformance  
      Join Agency on TempAgencyPerformance.AgencyInternalNo = Agency.AgencyInternalNo  


      left Join (select case  
                          when month(Date) >= @iMonth   then Cast((Year(Date)) As Varchar(10)) +'-' + Cast((Year(Date)+1) As Varchar(10))  
                          else Cast((Year(Date)-1) As Varchar(10)) +'-'+ Cast((Year(Date)) As Varchar(10))  
                        end YearNo,  PolicyProposal.AgencyInternalNo, Sum(PolicyProposal.SumAssured) TotalSumAssured,  
                        Count(PolicyProposal.PolicyNo) NoOfPolicy, Sum(Premium) FPI  
                   from PolicyProposal  
                   join insuranceTransaction on PolicyProposal.PolicyPropInternalNo = insuranceTransaction.TransactionInternalNo  
                  Group By case  
                          when month(Date) >= @iMonth   then Cast((Year(Date)) As Varchar(10)) +'-' + Cast((Year(Date)+1) As Varchar(10))  
                          else Cast((Year(Date)-1) As Varchar(10)) +'-'+ Cast((Year(Date)) As Varchar(10))  
                        end,  PolicyProposal.AgencyInternalNo  
                ) PolicySummary on TempAgencyPerformance.YearNo = PolicySummary.YearNo  
                               and TempAgencyPerformance.AgencyInternalNo = PolicySummary.AgencyInternalNo  




	  Left Join (Select YearNo, AgencyInternalNo, Count(distinct Lives) NoOfLives
					From
					(
						select case  
                          when month(Date) >= @iMonth   then Cast((Year(Date)) As Varchar(10)) +'-' + Cast((Year(Date)+1) As Varchar(10))  
                          else Cast((Year(Date)-1) As Varchar(10)) +'-'+ Cast((Year(Date)) As Varchar(10))  
                        end YearNo,
							PolicyProposal.AgencyInternalNo, 
							IsNull(PolicyProposalHolder.HolderInternalNo, PolicyProposal.HolderInternalNo) Lives 
						From PolicyProposal
		                   Join insuranceTransaction on PolicyProposal.PolicyPropInternalNo = insuranceTransaction.TransactionInternalNo
		                   Left Join PolicyProposalHolder On PolicyProposal.PolicyPropInternalNo = PolicyProposalHolder.PolicyPropInternalNo                 
						Union All		
						select case  
                          when month(Date) >= @iMonth   then Cast((Year(Date)) As Varchar(10)) +'-' + Cast((Year(Date)+1) As Varchar(10))  
                          else Cast((Year(Date)-1) As Varchar(10)) +'-'+ Cast((Year(Date)) As Varchar(10))  
                        end YearNo,
								PolicyProposal.AgencyInternalNo, 
						        PolicyProposal.JointHolderInternalNo Lives 
		                From PolicyProposal
					       Join insuranceTransaction on PolicyProposal.PolicyPropInternalNo = insuranceTransaction.TransactionInternalNo
						Where PolicyProposal.JointHolderInternalNo is not null
					) NoOfLives	
				Group By YearNo, AgencyInternalNo 
				)  LiveSummary on TempAgencyPerformance.YearNo = LiveSummary.YearNo
                               and TempAgencyPerformance.AgencyInternalNo = LiveSummary.AgencyInternalNo               




      left Join (select case  
                          when Month(cast(Commission.month + ' 1, ' + Cast(commission.Year as varchar(10)) as smalldatetime)) >= @iMonth  then Cast(commission.year As Varchar(10)) +'-' + Cast(commission.year+1 As Varchar(10))  
                          else Cast((commission.Year-1) As Varchar(10)) +'-'+ Cast(commission.Year As Varchar(10))   
       end YearNo,  Commission.AgencyInternalNo, Sum(commissionDetail.CommissionAmount) SumComm1  
                   From Commission  
                   Join CommissionDetail on commission.commissionInternalNo = commissionDetail.CommissionInternalNo  
                   Join InsuranceTransaction on commissionDetail.PolicyPropInternalNo = InsuranceTransaction.TransactionInternalNo  
                  Where DateDiff(mm, Insurancetransaction.Date, CommissionDetail.DueDate) < 12  
                  Group By case  
                          when Month(cast(Commission.month + ' 1, ' + Cast(commission.Year as varchar(10)) as smalldatetime)) >= @iMonth  then Cast(commission.year As Varchar(10)) +'-' + Cast(commission.year+1 As Varchar(10))  
                          else Cast((commission.Year-1) As Varchar(10)) +'-'+ Cast(commission.Year As Varchar(10))   
                        end,  Commission.AgencyInternalNo  
                 ) CommissionFirstYear on TempAgencyPerformance.YearNo = CommissionFirstYear.YearNo  
                                      and TempAgencyPerformance.AgencyInternalNo = commissionFirstYear.AgencyInternalNo  
      left Join (select case  
                          when Month(cast(Commission.month + ' 1, ' + Cast(commission.Year as varchar(10)) as smalldatetime)) >= @iMonth  then Cast(commission.year As Varchar(10)) +'-' + Cast(commission.year+1 As Varchar(10))  
                          else Cast((commission.Year-1) As Varchar(10)) +'-'+ Cast(commission.Year As Varchar(10))   
                        end YearNo,  Commission.AgencyInternalNo, Sum(commissionDetail.ActualCommission) SumComm2  
                   From Commission  
                   Join CommissionDetail on commission.commissionInternalNo = commissionDetail.CommissionInternalNo  
                   Join InsuranceTransaction on commissionDetail.PolicyPropInternalNo = InsuranceTransaction.TransactionInternalNo  
                  Where DateDiff(mm, Insurancetransaction.Date, CommissionDetail.DueDate) >= 12  
                  Group By case  
                          when Month(cast(Commission.month + ' 1, ' + Cast(commission.Year as varchar(10)) as smalldatetime)) >= @iMonth  then Cast(commission.year As Varchar(10)) +'-' + Cast(commission.year+1 As Varchar(10))  
                          else Cast((commission.Year-1) As Varchar(10)) +'-'+ Cast(commission.Year As Varchar(10))   
                        end,  Commission.AgencyInternalNo  
                 ) CommissionSubsequentYear on TempAgencyPerformance.YearNo = CommissionSubsequentYear.YearNo  
                                         and TempAgencyPerformance.AgencyInternalNo = CommissionSubsequentYear.AgencyInternalNo  
      left Join (Select case  
                          when month(DepositDate) >= @iMonth  then Cast((Year(DepositDate)) As Varchar(10)) +'-' + Cast((Year(DepositDate)+1) As Varchar(10))  
                          else Cast((Year(DepositDate)-1) As Varchar(10)) +'-'+ Cast((Year(DepositDate)) As Varchar(10))  
                        end YearNo, AgencyInternalNo, Sum(Premium) TotalPremium  
                   From (select IsNull(PremiumDepositIT.Date, DateAdd(mm, NoOfRecordInMode.NoOfMonth, DateAdd(yy, tmpYear.id-1, InsuranceTransaction.Date))) DepositDate,   
                                IsNull(PremiumDue.PremiumAmount, PolicyProposal.Premium) Premium, PolicyProposal.AgencyInternalNo  
                           from PolicyProposal   
                           Join InsuranceTransaction on PolicyProposal.PolicyPropInternalNo = InsuranceTransaction.TransactionInternalNo   
                           join NoOfRecordInMode on PolicyProposal.Mode = NoOfRecordInMode.Mode   
                           Cross Join (select distinct basicPremiumInternalNo id   
                                         from BasicPremium   
                                        Where basicPremiumInternalNo <= 100   
                                      )tmpYear   
                           left join premiumDue on PolicyProposal.PolicyPropInternalNo = premiumDue.PolicyPropInternalNo   
                                               and DateAdd(mm, NoOfRecordInMode.NoOfMonth, DateAdd(yy, tmpYear.id-1, InsuranceTransaction.Date)) = PremiumDue.DueDate   
                left Join (Select Premiumdue.PolicyPropInternalNo, Min(Premiumdue.DueDate) FUP   
                                        From PremiumDue   
                                       Where PremiumDue.AdjustAmount < PremiumDue.PremiumAmount   
                                       Group By Premiumdue.PolicyPropInternalNo   
                                     ) tmpPremiumDue on PolicyProposal.PolicyPropInternalNo = tmpPremiumDue.PolicyPropInternalNo   
                           left Join PremiumDeposit on PremiumDue.PremiumDepositInternalNo = PremiumDeposit.PremiumDepositInternalNo   
                                                   and PremiumDue.PremiumDepositDetailInternalNo = PremiumDeposit.PremiumDepositDetailInternalNo   
                           left Join InsuranceTransaction PremiumDepositIT on PremiumDeposit.PremiumDepositInternalNo = PremiumDepositIT.TransactionInternalNo   
                          Where tmpYear.id <= PolicyProposal.PremiumPayiTerm   
                            and ((DateAdd(mm, NoOfRecordInMode.NoOfMonth, DateAdd(yy, tmpYear.id-1, InsuranceTransaction.Date)) < tmpPremiumdue.FUP) or PolicyProposal.Mode = 'P')
                        ) tmpPremiumDeposit  
                  group by case  
                             when month(DepositDate) >= @iMonth   then Cast((Year(DepositDate)) As Varchar(10)) +'-' + Cast((Year(DepositDate)+1) As Varchar(10))  
                             else Cast((Year(DepositDate)-1) As Varchar(10)) +'-'+ Cast((Year(DepositDate)) As Varchar(10))  
                           end, AgencyInternalNo  
                ) tmpPremiumDeposit on TempAgencyPerformance.YearNo = tmpPremiumDeposit.YearNo  
                                   and TempAgencyPerformance.AgencyInternalNo = tmpPremiumDeposit.AgencyInternalNo  
    Where Agency.AgencyInternalNo = @AgencyInternalNo  
    order by Agency.agencyname, 1 Desc  
  end  
end  


