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 ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.