April 7, 2016 at 8:47 am
I need to Concat my last to fields of the Select CONCAT( Vedit.ruleid, qr.description [, Edit_Code ] )
I belive this is correct format but it is tossing errors:
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ', 'Edit Code' '.
Msg 102, Level 15, State 1, Line 36
Incorrect syntax near 'cpcp'.
Msg 102, Level 15, State 1, Line 44
Incorrect syntax near 'netw'.
select top 2000 p.description 'Program', bp.description 'Plan',ek.carriermemid 'Member ID',m.fullname 'Member Name',et.county 'Member County',c.controlnmb 'Patient Account',cd.claimid,cd.claimline,cd.status 'Status',cast(cd.dosfrom as date) 'Inc From', Cast(cd.dosto as date) 'Inc TO',
cast(c.paiddate as date) 'Paid Date',cd.location,c.facilitycode + c.billclasscode + c.frequencycode 'BillType',cd.revcode'Revenue',cd.servcode'Procedure'
,cd.modcode 'Modifier',cd.servunits 'Quanity',cd.claimamt 'Charged Amount',cd.copay 'Copay',cd.deductible 'deducible',cd.memamt 'Cost Share'
,cd.claimamt - cd.contractpaid 'Ineligible',cd.ineligibleamt 'Misc Inelig',crm.msgnumber 'RemitID',mes.description 'Remit Message',(cd.amountpaid + replace (cd.paydiscount,'-','')) 'Paid Amount',cd.ProvMedicareMandatedAdjust 'Sequestration'
,pay.fedid 'Tax Id', pay.fullname 'Payto Name',rend.fullname 'Service Provider',rend.npi 'Servicing NPI',con.description 'Contract Name',ci.contracted 'Contracted?', pt.description 'Provider Type',
spec.description 'Specialty',cpcp.fullname 'PCP',netw.Network,cpcp.PCPCounty,
q.description 'Fund',pc.checknbr,pc.advanceapplied 'Advance Per Check',c.reimbursemember 'Member Reimbursement Flag', CONCAT( edit.ruleid, qr.description [, 'Edit Code' ] )
from claimdetail cd
left join claimremit crm (nolock) on cd.claimid =crm.claimid and cd.claimline = crm.claimline
left join claimexplain ce (nolock) on cd.claimid = ce.claimid
left join claimedit edit (nolock) on cd.claimid =edit.claimid and cd.claimline = edit.claimline and edit.status in ('okay','deny')
left join qrule qr (nolock) on qr.ruleid = edit.ruleid
left join message mes (nolock) on crm.msgnumber = mes.messageid
left join benefitplan bp (nolock) on bp.planid=cd.planid
left join claim c (nolock) on c.claimid = cd.claimid
left join enrollkeys ek (nolock) on ek.enrollid = c.enrollid
left join member m (nolock) on m.memid = c.memid
left join entity et on m.entityid = et.entid
left join program p (nolock) on p.programid = ek.programid
left join provider rend (nolock) on rend.provid = c.provid
left join providertype pt (nolock) on rend.provtype = pt.provtype
left join provspecialty ps (nolock) on rend.provid = ps.provid and ps.spectype ='PRIMARY' and cast (ps.termdate as date) >= '2078-12-31'
left join specialty spec (nolock) on spec.specialtycode = ps.specialtycode
left join affiliation a (nolock) on a.affiliationid = c.affiliationid
--left join memberpcp mp (nolock) on ek.enrollid = mp.enrollid
--left join affiliation apcp (nolock) on apcp.affiliationid = mp.aWelcomeffiliationid
left join (select cpcp.claimid,pcp2.fullname,pcp2.provid,cast (apcp.effdate as date)'pcpeffdte',etp.county 'PCPCounty'
from claim cpcp (nolock)
left join enrollkeys ekpcp (nolock) on ekpcp.enrollid = cpcp.enrollid
left join memberpcp mp (nolock) on ekpcp.enrollid = mp.enrollid
left join affiliation apcp (nolock) on apcp.affiliationid = mp.affiliationid
left join provider pcp2 (nolock) on apcp.provid = pcp2.provid
left join entity etp on pcp2.entityid = etp.entid
where CAST(cpcp.startdate as date) between cast(mp.effdate as date) and cast(mp.termdate as date))
cpcp on cpcp.claimid = c.claimid
--left join provider pcp (nolock) on apcp.provid = pcp.provid and CAST(c.startdate as date) between cast(mp.effdate as date) and cast(mp.termdate as date)
left join (select netw.provid ,network.fullname 'Network',p.fullname'PCP'
from affiliation netw (nolock)
left join affiliation apcp (nolock) on apcp.affiliationid = netw.affiliationid
left join provider network (nolock) on netw.affiliateid = network.provid
left join provider p (nolock) on netw.provid = p.provid
where netw.affiltype ='NETWORK' and cast (apcp.effdate as date) between cast(netw.effdate as date) and cast(netw.termdate as date)) netw on cpcp.provid = netw.provid
--left join provider network (nolock) on netw.affiliateid = network.provid
left join provider pay (nolock) on a.affiliateid = pay.provid
left join qfund q (nolock) on cd.fundid = q.fundid
left join payvoucher pv (nolock) on pv.claimid = c.claimid
left join paycheck pc (nolock) on pc.paymentid = pv.paymentid and cd.fundid = pc.fundid
left join contractinfo ci (nolock) on ci.affiliationid=a.affiliationid and ek.programid = ci.programid and CAST (c.startdate as date) between cast(ci.effdate as date) and cast(ci.termdate as date)
left join contract con (nolock) on ci.contractid = con.contractid
Where cast(c.paiddate as date) between '01-01-2016' and '02-29-2016'
--pay.fedid = '710892430' and c.paiddate is not null
order by claimid,claimline
April 7, 2016 at 8:55 am
Please do not double post.
As I noted for the other post, CONCAT is not a 2008 command.
April 7, 2016 at 8:59 am
Those square brackets that you copied from Books Online mean that the parameter is optional. You're not meant to leave them in your code.
For the other errors, double-click on the error message in the results plan and it'll take you to the line where the error occurred. That's easier than us trying to count the lines in the code you posted.
John
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply