May 13, 2016 at 5:12 pm
Need assistance. My code is returning nothing. I think I left join out of place. I have been looking at this for two days anyone who thinks they can see a mistake in the code any help would be appreciated. If you also can see a way to stream line this that would be great input too. The Format on CHAR and CAST and CASE are Required cant change.
Select
Cast(e.firstname As [Char](50)) 'Benefiticary First Name',
ci.contracted,
Cast(e.lastname As [Char](60)) 'Benefit Last Name',
ek.carriermemid 'Cardholder ID',
bp.CmsContractId 'Contract ID',
bp.upid 'Plan ID',
c.claimid,
Case When ci.contracted = 'Y' Then 'CP' Else 'NCP' End As 'Provider Type',
Case When cp.claimid = c.claimid Then 'N' Else 'Y' End As Clean,
Convert([Char](10),c.cleandate,111) 'Date the request was received',
ICD.codeid 'Diagnosis',
Convert([Char](10),c.paiddate,111)
'Date written notification provided to provider',
Case When claimdetail.paydiscount = '0.00' Then 'N' Else 'Y'
End As [Was intrest paid on the claim],
con.description 'First Tiet Downstream and Related Entity',
c.status As [Request Disposition],
c.claimid,
Replace(Replace(Stuff((Select
*
From
(Select
CEM.overridemessage As '_'
From
claimeditmessage CEM With(NoLock)
Where
c.claimid = CEM.claimid
Union All
Select
Convert(varchar(255),OM.overridemessage) As '_'
From
claimremit OM With(NoLock)
Where
c.claimid = OM.claimid
Union All
Select
CE.remitoverridemessage As '_'
From
claimedit CE
Where
c.claimid = CE.claimid) As k
For Xml Path('')), 1, 0, ''), '<_>', ''), '</_>', Char(13) + Char(10)) As
[Remit Description],
Convert([Char](10),c.okpaydate,111) 'Date the claim was paid or denied',
categoryservice.description As description1
From
claim c Left Join
benefitplan bp With(NoLock)
On bp.planid = c.planid Left Join
(Select Distinct
cp.claimid
From
claim cp With(NoLock) Join
claimedit ce With(NoLock)
On cp.claimid = ce.claimid Join
claimpendhistory ch With(NoLock)
On ce.claimid = ch.claimid
Where
cp.status Not In ('VOID', 'REVERSED', 'REVSYNCH') And
ce.ruleid = '913' And
ch.pendreasonid In ('CN', 'OR', 'OI')) cp
On cp.claimid = c.claimid Left Join
enrollkeys ek With(NoLock)
On ek.enrollid = c.enrollid Left Join
claimdiag ICD With(NoLock)
On c.claimid = ICD.claimid And ICD.diagtype = '1' Left Join
(Select
ch.claimid,
ch.orgclaimid
From
claim ch) ch
On ch.claimid = c.claimid Left Join
member m With(NoLock)
On m.memid = c.memid Left Join
entity e With(NoLock)
On e.entid = m.entityid Left Join
program p With(NoLock)
On p.programid = ek.programid Left Join
affiliation a With(NoLock)
On a.affiliationid = c.affiliationid Left Join
contractinfo ci With(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 With(NoLock)
On ci.contractid = con.contractid Left Join
provider rend With(NoLock)
On rend.provid = c.provid Inner Join
payment
On payment.memid = m.memid And payment.claimmemid = m.memid And
payment.programid = p.programid And payment.provid = rend.provid Inner Join
provcos
On provcos.provid = rend.provid Inner Join
categoryservice
On provcos.cosgrp = categoryservice.cosgrp Inner Join
claimdetail
On claimdetail.claimid = c.claimid
Where
c.status In ('PAID', 'DENIED') And
Cast(c.paiddate As date) Between '2016-03-03' And '2016-04-14' And
ek.carriermemid <> ' ' And
c.resubclaimid = ' ' And
Left(c.claimid, 11) Not In (Select
Left(c.claimid, 11)
From
claim
Where
Left(Right(RTrim(c.claimid), 2), 1) In ('R', 'A'))
Order By
'Contract ID'
May 13, 2016 at 7:24 pm
Maybe someone else will dig through your code but after 64 visits and 42 posts I'm sure you've heard the lecture about DDL and consumable data. Not providing that makes it much harder than it should be to help you. That said, here's some general tips:
Start form the beginning of your query and end at "FROM claim c". Comment out any columns that don't come from claim. You getting data? If not, then there's your problem, if so, add the the "Left Join benefitplan bp On bp.planid = c.planid "... Still getting data? No? then there's your problem. Yes? then keep adding stuff until you stop getting data. That's how you troubleshoot this type of thing. You can also comment out the conditions in your WHERE clause; e.g. comment out "c.status In ('PAID', 'DENIED') " and see if that helps isolate the problem, widen your date range, stuff like that.
Some things I'd fix if you want good, accurate code that returns data in a timely fashion:
1. The NOLOCK hints prevent you from getting the correct data every time the query is run. If that's ok then leave them there.
2. Order By 'Contract ID'... What problem does that solve? If you don't have a good answer that, lose it.
3. Stuff like "Left(c.claimid, 11) Not In..." in your WHERE clause keeps you query from being able to get an index seek or even utilize otherwise helpful indexes which slows you down a makes your query a bigger drag on your system.
4. When you have a dozen Left joins to return a dozen columns there's something tragically wrong with your data model. If you can't fix it the consider some indexed views or ETL into a reporting DB, data mart or something similar.
-- Itzik Ben-Gan 2001
May 14, 2016 at 9:55 am
This:
ek.carriermemid <> ' '
In the WHERE clause is going to turn that LEFT JOIN into an INNER JOIN. Move it into the ON clause in order to retain the LEFT JOIN
Casting your date the way you're doing is absolutely going to create performance issues that you can't get around. It's going to scan the table, no choice. I actually wrote a blog post[/url] about just this issue this week.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply