November 3, 2010 at 10:56 am
Can anyone tell me a better way to write this query, these are both large tables and it is very slow... Any help would be appreciated.. Thank you in advance.
SQL:
select c.ClaimID, Modifier = isnull(c.Modifier1, null), ServiceDOS =c.DOS from @tmpAudit1 t,tblClaimCharge c with (NOLOCK) where t.ClaimID = c.ClaimID and c.Modifier1 is not null
union
select c.ClaimID, Modifier = isnull(c.Modifier2, null), ServiceDOS =c.DOS from @tmpAudit1 t,tblClaimCharge c with (NOLOCK) where t.ClaimID = c.ClaimID and c.Modifier2 is not null
union
select c.ClaimID, Modifier = isnull(c.Modifier3, null), ServiceDOS =c.DOS from @tmpAudit1 t,tblClaimCharge c with (NOLOCK) where t.ClaimID = c.ClaimID and c.Modifier3 is not null
union
select c.ClaimID, Modifier = isnull(c.Modifier4, null), ServiceDOS =c.DOS from @tmpAudit1 t,tblClaimCharge c with (NOLOCK) where t.ClaimID = c.ClaimID and c.Modifier4 is not null
November 3, 2010 at 11:03 am
It looks like it could all be one query, without unions at all, if you were to use either nested IsNull, or (better yet) Coalesce.
SELECT
c.ClaimID,
COALESCE(c.Modifier1, c.Modifier2, c.Modifier3, c.Modifier4) AS Modifier,
c.DOS AS ServiceDOS
FROM
@tmpAudit1 AS t
INNER JOIN tblClaimCharge AS c
ON t.ClaimID = c.ClaimID
WHERE
c.Modifier1 IS NOT NULL
OR c.Modifier2 IS NOT NULL
OR c.Modifier3 IS NOT NULL
OR c.Modifier4 IS NOT NULL ;
I formatted it for readability, updated the join to the current standard, and used Coalesce. Does it do what you need?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 3, 2010 at 11:09 am
You rock! This worked great and it knocked the scan count down substainsially! Thank you
November 3, 2010 at 11:17 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 3, 2010 at 2:22 pm
GSquared, your solution is not quite the same as the original unions. With the unions - if modifier1 and modifier2 both have values - and those values are different there would be two rows. In your solution, the second row for modifier2 would be eliminated.
And, if all modifier fields have values - instead of the expected 4 rows for the claim, your solution would only have a single row.
I don't think this can be done without the unions - but it depends on what the OP is actually looking for in the results. For efficiency, I would change the UNION to a UNION ALL - guessing that you would want a row for each modifier and that you cannot have the same modifier multiple times on a single claim.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 3, 2010 at 2:24 pm
True. I made some assumptions about use that may not be valid.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply