Help with SSRS
this is the error i get when i try run the bellow script in ssrs (Conversion failed when converting date and/or time from character string.)
How can I fix it
DECLARE @start_date char(5) DECLARE @end_Date char(5)
set @start_date = convert(datetime, convert(char(5), year(dateadd(mm,-1,getdate()))) + '/'+ Right(Convert(char(5), Month(dateadd(mm,-1,getdate()))),2)+ '/01')
set @end_date = convert(datetime, convert(char(5), year(dateadd(mm,-0,getdate()))) + '/'+ Right(Convert(char(5), Month(dateadd(mm,-0,getdate()))),2)+ '/01')
select a.application_id
,ApplicationReference as NasRefNo
,Application_ILPNo
,at.ApplicationType_Name
,PropertyType_Name
,Region_Name
,employmentstatus= (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)
,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)) as measure
,Missing_Docs = NULL
,MO_EA = coalesce(MortgageOriginator_Name,EstateAgency_Name)
,Attorney_Name
,Attorney_TelephoneCode
,Attorney_TelephoneNumber
,Attorney_EMail
,bs.Branch as NewBranchName
,bs.Cost_centre as NewCostCentre
,bs.Province as NewProvince
,bs.Area as NewArea
,Applicationtype_Rollup
,Application_Date
,WorkHours_CAPSTART_CAPCOMP = mis.dbo.mis_Workhours((select min(a1.ApplicationstatusChange_Date)
from nas..ApplicationstatusChange a1 with(nolock)
where a1.ApplicationStatusChange_New_Status in (2) and a1.application_id = a.application_id),
(select min(a1.ApplicationstatusChange_Date)
from nas..ApplicationstatusChange a1 with(nolock)
where a1.ApplicationStatusChange_New_Status in (3) and a1.application_id = a.application_id))
,WorkHours_CAPCOMP_VALCOMP = mis.dbo.mis_Workhours((select min(a1.ApplicationstatusChange_Date)
from nas..ApplicationstatusChange a1 with(nolock)
where a1.ApplicationStatusChange_New_Status in (3) and a1.application_id = a.application_id),
(select min(a1.ApplicationstatusChange_Date)
from nas..ApplicationstatusChange a1 with(nolock)
where a1.ApplicationStatusChange_New_Status in (17) and a1.application_id = a.application_id))
,WorkHours_VALCOMP_AIP = mis.dbo.mis_Workhours((select min(a1.ApplicationstatusChange_Date)
from nas..ApplicationstatusChange a1 with(nolock)
where a1.ApplicationStatusChange_New_Status in (17) and a1.application_id = a.application_id),
(select min(a1.ApplicationstatusChange_Date)
from nas..ApplicationstatusChange a1 with(nolock)
where a1.ApplicationStatusChange_New_Status in (24,26,124) and a1.application_id = a.application_id))
,WorkHours_AIP_VALREQ = mis.dbo.mis_Workhours((select min(a1.ApplicationstatusChange_Date)
from nas..ApplicationstatusChange a1 with(nolock)
where a1.ApplicationStatusChange_New_Status in (24,26,124) and a1.application_id = a.application_id),
(select min(a1.ApplicationstatusChange_Date)
from nas..ApplicationstatusChange a1 with(nolock)
where a1.ApplicationStatusChange_New_Status in (36) and a1.application_id = a.application_id))
,WorkHours_VALREQ_VALRECD = mis.dbo.mis_Workhours((select min(a1.ApplicationstatusChange_Date)
from nas..ApplicationstatusChange a1 with(nolock)
where a1.ApplicationStatusChange_New_Status in (36) and a1.application_id = a.application_id),
(select min(a1.ApplicationstatusChange_Date)
from nas..ApplicationstatusChange a1 with(nolock)
where a1.ApplicationStatusChange_New_Status in (37) and a1.application_id = a.application_id))
,WorkHours_VALRECD_FINDECS = mis.dbo.mis_Workhours((select min(a1.ApplicationstatusChange_Date)
from nas..ApplicationstatusChange a1 with(nolock)
where a1.ApplicationStatusChange_New_Status in (37) and a1.application_id = a.application_id),
(select min(a1.ApplicationstatusChange_Date)
from nas..ApplicationstatusChange a1 with(nolock)
where a1.ApplicationStatusChange_New_Status in (47,48,117,125) and a1.application_id = a.application_id))
,WorkHours_FINDESC_CUSTACCFINDECS = mis.dbo.mis_Workhours((select min(a1.ApplicationstatusChange_Date)
from nas..ApplicationstatusChange a1 with(nolock)
where a1.ApplicationStatusChange_New_Status in (47,48,117,125) and a1.application_id = a.application_id),
(select min(a1.ApplicationstatusChange_Date)
from nas..ApplicationstatusChange a1 with(nolock)
where a1.ApplicationStatusChange_New_Status in (127) and a1.application_id = a.application_id))
,WorkHours_CUSTACCFINDECS_ElECTINST = mis.dbo.mis_Workhours((select min(a1.ApplicationstatusChange_Date)
from nas..ApplicationstatusChange a1 with(nolock)
where a1.ApplicationStatusChange_New_Status in (127) and a1.application_id = a.application_id),
(select min(a1.ApplicationstatusChange_Date)
from nas..ApplicationstatusChange a1 with(nolock)
where a1.ApplicationStatusChange_New_Status in (301,54) and a1.application_id = a.application_id))
,WorkHours_ELECINST_BONDREG = mis.dbo.mis_Workhours((select min(a1.ApplicationstatusChange_Date)
from nas..ApplicationstatusChange a1 with(nolock)
where a1.ApplicationStatusChange_New_Status in (301,54) and a1.application_id = a.application_id),
(select min(a1.ApplicationstatusChange_Date)
from nas..ApplicationstatusChange a1 with(nolock)
where a1.ApplicationStatusChange_New_Status in (114,316) and a1.application_id = a.application_id))
,WorkHours_BONDREG_PAYOUT = mis.dbo.mis_Workhours((select min(a1.ApplicationstatusChange_Date)
from nas..ApplicationstatusChange a1 with(nolock)
where a1.ApplicationStatusChange_New_Status in (114,316) and a1.application_id = a.application_id),
(select min(Payout_EntryDate)
from nas..Payout p with(nolock)
where p.application_id = a.application_id))
from nas..Application a
left join nas_Frontier..Step_Running_Singularity srs on srs.ApplicationReferenceID = a.Application_ID
left join nas..applicationType at on at.applicationType_ID = a.applicationType_ID
left join mis..MIS_ApplicationRegion AR on AR.Application_ID = a.Application_ID
left join nas..source source on source.source_ID = a.source_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..applicationAttorney applicationAttorney on applicationAttorney.Application_ID = a.Application_ID
left join nas..Attorney Attorney with (nolock) on attorney.attorney_ID = applicationAttorney.attorney_ID
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 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..BranchStructure_April2009 BranchStructure_April2009) bs on bs.cost_centre = ltrim(convert(bigint,mis.dbo.mis_branchcode(a.source_id,a.application_branchcode)))
left join nas..ApplicationCAV cav on a.application_id=cav.application_id
left join mis..MIS_ApplicationtypeRollup R with(nolock) on r.applicationtype_id=a.applicationtype_id
left join nas..Property Property on Property.Property_ID = a.Property_ID
left join nas..PropertyType PropertyType on PropertyType.PropertyType_ID = Property.PropertyType_ID
--where (a.Application_Date BETWEEN @start_date AND @end_date )
where a.Application_ID in ( select Application_ID
from Nas..ApplicationStatusChange
where ApplicationStatusChange_Date BETWEEN @start_date AND @end_date )How can I fix it
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
hi when i try put this script into SSRS i get the following error (An Error occured while the query design method was being saved. Incorrect syntax near 'applicationstatuschange_d
ASKER
I also dont understand what set start_Date and End_date are trying to do