• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 773
  • Last Modified:

Msg 102, Level 15, State 1, Line 217 I ncorrect syntax near 'end'.

I keep getting the above error and i cant seem to find the problem  please can u assist me


select	AppYear
		,AppMonth
		,ValueStream
		,Region_Name
		,ApplicationType_Name
		,ReSubmitted
		,measure
		,BankGroup
		,Value_Bracket
		,BusinessChannel = case when Measure in ('Branch Banking','Bank Assurance', 'Estate Agent','Network Sales') 
									then 'Internal Channel'   
								when Measure in ('Bond Alliance','Bond Choice','Bondstreet','Loan Link','MO Other','OOBA','PA Better Bond','MSA') 
									then 'External Channel'                  
								when Measure in ('Vitality','White Label','Direct Channel','My Branch') 
									then 'Direct Channel'   
								when Measure in ('Development') 
									then 'Development'
										else 'Not Specified' end

       ,Count(application_ID) as Volume

from
(             
		select	YEAR(a.application_Date) as AppYear
				,DATENAME(mm,a.application_Date) as AppMonth
				,ValueStream = (case when
										(CASE a.applicanttype_id 
											when 1 then
										(select top 1 es.employmentstatus_name
												from nas.dbo.applicantemployment ae with(nolock), nas.dbo.employmentstatus es with(nolock)
													where ae.employmentstatus_id = es.employmentstatus_id
															and ae.applicant_id = 

                                         (select top 1 applicant_id from nas.dbo.applicationapplicant with(nolock)

                                         where application_id = a.application_id))

                                                else   

                                  (select top 1 es.employmentstatus_name

                                    from nas.dbo.applicantemployment ae with(nolock), nas.dbo.employmentstatus es with(nolock)

                                    where ae.employmentstatus_id = es.employmentstatus_id

                                    and ae.applicant_id =

                                         (select top 1 applicant_id from nas.dbo.applicantcompany with(nolock)

                                         where application_id = a.application_id))

                                                end) = 'Self-Employed' THEN 'Self-Employed'

                                                ELSE 'Salaried'

                                                END)

            ,Region_Name
			,ApplicationType_Name
			,ReSubmitted =  case when a.Application_ID In (select Application_ID

                                                                from Nas..ApplicationStatusChange apsc

                                                                where apsc.Application_ID = a.Application_ID

                                                                     and apsc.ApplicationStatusChange_New_Status = 33--ReSubs

                                                                     )

                                  then 'Y' Else '' end

              ,case when bs.Area = 'Premier' then 'Premier'

                           else (

                                   nas.dbo.fn_payout_getmeasure(    source.source_name, 

                                  applicationchannel.applicationchannel_name,

                                  coalesce(mortgageoriginator.mortgageoriginator_name,eta.estateagency_name),

                                  isnull(loandetails.branch_id, case when isnumeric(a.application_branchcode) = 1

                                  then a.application_branchcode end))

                                  ) end as measure

              ,case when nas.dbo.LoanDetails_PurchasePrice (a.Application_ID) < 500000 then 'a. < R500k'
					when nas.dbo.LoanDetails_PurchasePrice (a.Application_ID) >= 500000 and nas.dbo.LoanDetails_PurchasePrice (a.Application_ID) <= 1000000 then 'b. R500k - R1m'    
					when nas.dbo.LoanDetails_PurchasePrice (a.Application_ID) > 1000000 and nas.dbo.LoanDetails_PurchasePrice (a.Application_ID) <= 1200000 then 'c. R1m - R1.2m'    
					when nas.dbo.LoanDetails_PurchasePrice (a.Application_ID) > 1200000 and nas.dbo.LoanDetails_PurchasePrice (a.Application_ID) <= 1500000 then 'd. R1.2m - R1.5m'
					when nas.dbo.LoanDetails_PurchasePrice (a.Application_ID) > 1500000 and nas.dbo.LoanDetails_PurchasePrice (a.Application_ID) <= 2000000 then 'e. R1.5m - R2m'
					when nas.dbo.LoanDetails_PurchasePrice (a.Application_ID) > 2000000 and nas.dbo.LoanDetails_PurchasePrice (a.Application_ID) <= 3000000 then 'f. R2m - R3m'
					when nas.dbo.LoanDetails_PurchasePrice (a.Application_ID) > 3000000 and nas.dbo.LoanDetails_PurchasePrice (a.Application_ID) <= 4000000 then 'g. R3m - R4m'
					when nas.dbo.LoanDetails_PurchasePrice (a.Application_ID) > 4000000 then 'h. > R4m'
						end as Value_Bracket

              ,case when Bank_Name = 'First National Bank' then 'FNB'

                                  else 'Other' 

                 end as BankGroup

              --,BusinessChannel = NULL

              ,a.application_ID

       from Nas..Application a

              left join mis.dbo.MIS_ApplicationRegion AR on AR.Application_ID = a.Application_ID

              left join Nas..ApplicationType at on at.ApplicationType_ID = a.ApplicationType_ID

              left join nas..applicationChannel applicationChannel on applicationChannel.applicationChannel_ID = a.applicationChannel_ID

              left join nas..homeloan homeloan on homeloan.Homeloan_ID = a.Application_ID

              left join nas..MortgageOriginator MortgageOriginator on MortgageOriginator.MortgageOriginator_ID = homeloan.MortgageOriginator_ID

              left join nas..loandetails loandetails on loandetails.loandetails_ID = a.loandetails_ID

              left join nas..source source on source.source_ID = a.source_ID

              left join  nas.dbo.branch branch with(nolock) on branch.branch_id = loandetails.branch_id

              left join  nas.dbo.branchbanking_branch branchbanking with (nolock) on branchbanking.branchcode = substring(a.application_branchcode, 3, 3)

              left join (select Cost_centre, Branch, Province, Area

                              from mis.dbo.BranchStructure_April2009_new BranchStructure_April2009) bs on bs.cost_centre = ltrim(convert(bigint,mis.dbo.mis_branchcode(a.source_id,a.application_branchcode)))

              left join nas..estateagent ea with (nolock) on ea.estateagent_id = homeloan.estateagent_id

              left join (select max(estateagency_id) as estateagency_id,

                                         estateagent_id

                                  from nas..agencyestateagent agencyestateagent with (nolock)

                                  group by estateagent_id) as agencyestateagentmax on agencyestateagentmax.estateagent_id = ea.estateagent_id

              left join nas..estateagency eta with (nolock) on eta.estateagency_id = agencyestateagentmax.estateagency_id

              left join (select Application_ID,Applicant_ID

                                                   from nas.dbo.ApplicationApplicant with (nolock)

                                                   union

                                                   select Application_ID,Applicant_ID

                                                   from nas.dbo.ApplicantCompany with (nolock)) as AllApplicants on AllApplicants.Application_ID = a.Application_ID

              left join nas.dbo.Applicant Applicant with (nolock) on Applicant.Applicant_ID = AllApplicants.Applicant_ID

 

              left join (Select MBDID = Max(ApplicantBankingDetails_ID),Applicant_ID from nas.dbo.ApplicantBankingDetails with (nolock) group by Applicant_ID)  MAXBDID on MAXBDID.Applicant_ID = AllApplicants.Applicant_ID

              left join nas.dbo.ApplicantBankingDetails ABD with (nolock) on MAXBDID.MBDID = ABD.ApplicantBankingDetails_ID

              left join nas.dbo.BankingDetails bankingDetails with (nolock) on BankingDetails.BankingDetails_ID = abd.BankingDetails_ID

              left join nas.dbo.Bank Bank with (nolock) on Bank.Bank_ID = BankingDetails.Bank_ID

 

       where Application_Date between '01 October 2011' and '10 October 2011'
	
	/*and Region_Name in (@Region_Name)	
	
	

       ) as Temp

where ValueStream in (@ValueStream)

and ApplicationType_Name in (@ApplicationType_Name)*/



group by  AppYear

       ,AppMonth

       ,ValueStream

       ,Region_Name

       ,ApplicationType_Name

       ,ReSubmitted

       ,measure

       ,Value_Bracket

       ,BankGroup

       ,case when Measure in ('Branch Banking','Bank Assurance', 'Estate Agent','Network Sales') then

                                  'Internal Channel'   

                when Measure in ('Bond Alliance','Bond Choice','Bondstreet','Loan Link','MO Other','OOBA','PA Better Bond','MSA') then 

                                  'External Channel'                  

                           when Measure in ('Vitality','White Label','Direct Channel','My Branch') then 

                                  'Direct Channel'   

                           when Measure in ('Development') then 

                                   'Development'

                            else 'Not Specified' 
                            end 

Open in new window

0
Davesm
Asked:
Davesm
1 Solution
 
sachinpatil10dCommented:
check this out
select	AppYear
		,AppMonth
		,ValueStream
		,Region_Name
		,ApplicationType_Name
		,ReSubmitted
		,measure
		,BankGroup
		,Value_Bracket
		,BusinessChannel = case when Measure in ('Branch Banking','Bank Assurance', 'Estate Agent','Network Sales') 
									then 'Internal Channel'   
								when Measure in ('Bond Alliance','Bond Choice','Bondstreet','Loan Link','MO Other','OOBA','PA Better Bond','MSA') 
									then 'External Channel'                  
								when Measure in ('Vitality','White Label','Direct Channel','My Branch') 
									then 'Direct Channel'   
								when Measure in ('Development') 
									then 'Development'
										else 'Not Specified' end
       ,Count(application_ID) as Volume
from
(             
		select	YEAR(a.application_Date) as AppYear
				,DATENAME(mm,a.application_Date) as AppMonth
				,ValueStream = (case when
										(CASE a.applicanttype_id 
											when 1 then
										(select top 1 es.employmentstatus_name
												from nas.dbo.applicantemployment ae with(nolock), nas.dbo.employmentstatus es with(nolock)
													where ae.employmentstatus_id = es.employmentstatus_id
															and ae.applicant_id = 

                                         (select top 1 applicant_id from nas.dbo.applicationapplicant with(nolock)

                                         where application_id = a.application_id))

                                                else   

                                  (select top 1 es.employmentstatus_name

                                    from nas.dbo.applicantemployment ae with(nolock), nas.dbo.employmentstatus es with(nolock)

                                    where ae.employmentstatus_id = es.employmentstatus_id

                                    and ae.applicant_id =

                                         (select top 1 applicant_id from nas.dbo.applicantcompany with(nolock)

                                         where application_id = a.application_id))

                                                end) = 'Self-Employed' THEN 'Self-Employed'

                                                ELSE 'Salaried'
                                                END)
            ,Region_Name
			,ApplicationType_Name
			,ReSubmitted =  case when a.Application_ID In (select Application_ID
                                                                from Nas..ApplicationStatusChange apsc
                                                                where apsc.Application_ID = a.Application_ID
                                                                     and apsc.ApplicationStatusChange_New_Status = 33--ReSubs
                                                                     )
                                  then 'Y' Else '' end
              ,case when bs.Area = 'Premier' then 'Premier'
                           else (
                                   nas.dbo.fn_payout_getmeasure(    source.source_name, 
                                  applicationchannel.applicationchannel_name,
                                  coalesce(mortgageoriginator.mortgageoriginator_name,eta.estateagency_name),
                                  isnull(loandetails.branch_id, case when isnumeric(a.application_branchcode) = 1
                                  then a.application_branchcode end))
                                  ) end as measure
              ,case when nas.dbo.LoanDetails_PurchasePrice (a.Application_ID) < 500000 then 'a. < R500k'
					when nas.dbo.LoanDetails_PurchasePrice (a.Application_ID) >= 500000 and nas.dbo.LoanDetails_PurchasePrice (a.Application_ID) <= 1000000 then 'b. R500k - R1m'    
					when nas.dbo.LoanDetails_PurchasePrice (a.Application_ID) > 1000000 and nas.dbo.LoanDetails_PurchasePrice (a.Application_ID) <= 1200000 then 'c. R1m - R1.2m'    
					when nas.dbo.LoanDetails_PurchasePrice (a.Application_ID) > 1200000 and nas.dbo.LoanDetails_PurchasePrice (a.Application_ID) <= 1500000 then 'd. R1.2m - R1.5m'
					when nas.dbo.LoanDetails_PurchasePrice (a.Application_ID) > 1500000 and nas.dbo.LoanDetails_PurchasePrice (a.Application_ID) <= 2000000 then 'e. R1.5m - R2m'
					when nas.dbo.LoanDetails_PurchasePrice (a.Application_ID) > 2000000 and nas.dbo.LoanDetails_PurchasePrice (a.Application_ID) <= 3000000 then 'f. R2m - R3m'
					when nas.dbo.LoanDetails_PurchasePrice (a.Application_ID) > 3000000 and nas.dbo.LoanDetails_PurchasePrice (a.Application_ID) <= 4000000 then 'g. R3m - R4m'
					when nas.dbo.LoanDetails_PurchasePrice (a.Application_ID) > 4000000 then 'h. > R4m'
						end as Value_Bracket

              ,case when Bank_Name = 'First National Bank' then 'FNB'
                                  else 'Other' 
                 end as BankGroup
              --,BusinessChannel = NULL
              ,a.application_ID
       from Nas..Application a
              left join mis.dbo.MIS_ApplicationRegion AR on AR.Application_ID = a.Application_ID
              left join Nas..ApplicationType at on at.ApplicationType_ID = a.ApplicationType_ID
              left join nas..applicationChannel applicationChannel on applicationChannel.applicationChannel_ID = a.applicationChannel_ID
              left join nas..homeloan homeloan on homeloan.Homeloan_ID = a.Application_ID
              left join nas..MortgageOriginator MortgageOriginator on MortgageOriginator.MortgageOriginator_ID = homeloan.MortgageOriginator_ID
              left join nas..loandetails loandetails on loandetails.loandetails_ID = a.loandetails_ID
              left join nas..source source on source.source_ID = a.source_ID
              left join  nas.dbo.branch branch with(nolock) on branch.branch_id = loandetails.branch_id
              left join  nas.dbo.branchbanking_branch branchbanking with (nolock) on branchbanking.branchcode = substring(a.application_branchcode, 3, 3)
              left join (select Cost_centre, Branch, Province, Area
                              from mis.dbo.BranchStructure_April2009_new BranchStructure_April2009) bs on bs.cost_centre = ltrim(convert(bigint,mis.dbo.mis_branchcode(a.source_id,a.application_branchcode)))
              left join nas..estateagent ea with (nolock) on ea.estateagent_id = homeloan.estateagent_id
              left join (select max(estateagency_id) as estateagency_id,
                                         estateagent_id
                                  from nas..agencyestateagent agencyestateagent with (nolock)
                                  group by estateagent_id) as agencyestateagentmax on agencyestateagentmax.estateagent_id = ea.estateagent_id
              left join nas..estateagency eta with (nolock) on eta.estateagency_id = agencyestateagentmax.estateagency_id
              left join (select Application_ID,Applicant_ID
                                                   from nas.dbo.ApplicationApplicant with (nolock)
                                                   union
                                                   select Application_ID,Applicant_ID
                                                   from nas.dbo.ApplicantCompany with (nolock)) as AllApplicants on AllApplicants.Application_ID = a.Application_ID
              left join nas.dbo.Applicant Applicant with (nolock) on Applicant.Applicant_ID = AllApplicants.Applicant_ID
              left join (Select MBDID = Max(ApplicantBankingDetails_ID),Applicant_ID from nas.dbo.ApplicantBankingDetails with (nolock) group by Applicant_ID)  MAXBDID on MAXBDID.Applicant_ID = AllApplicants.Applicant_ID
              left join nas.dbo.ApplicantBankingDetails ABD with (nolock) on MAXBDID.MBDID = ABD.ApplicantBankingDetails_ID
              left join nas.dbo.BankingDetails bankingDetails with (nolock) on BankingDetails.BankingDetails_ID = abd.BankingDetails_ID
              left join nas.dbo.Bank Bank with (nolock) on Bank.Bank_ID = BankingDetails.Bank_ID
       where Application_Date between '01 October 2011' and '10 October 2011'
group by  AppYear
       ,AppMonth
       ,ValueStream
       ,Region_Name
       ,ApplicationType_Name
       ,ReSubmitted
       ,measure
       ,Value_Bracket
       ,BankGroup
       ,case when Measure in ('Branch Banking','Bank Assurance', 'Estate Agent','Network Sales') then
                                  'Internal Channel'   
            when Measure in ('Bond Alliance','Bond Choice','Bondstreet','Loan Link','MO Other','OOBA','PA Better Bond','MSA') then 
                                  'External Channel'                  
			when Measure in ('Vitality','White Label','Direct Channel','My Branch') then 
                                  'Direct Channel'   
			when Measure in ('Development') then 
                                   'Development'
			else 'Not Specified' 
		end ) as tmp

Open in new window

0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Tackle projects and never again get stuck behind a technical roadblock.
Join Now