SQL QUERY with subquery Optimization

Question

how can i optimize these query

SELECT
mp.ProviderName
,(SELECT count(mc.ClaimSubmissionID) FROM dbo.MST_Claim mc WHERE   mc.HeaderID=mpach.HeaderID AND mc.IsActive=1) AS total_claim
,(SELECT count(mc.ClaimSubmissionID) FROM dbo.MST_Claim mc WHERE mc.HeaderID=mpach.HeaderID AND mc.op=1) AS total_op
,(SELECT count(mc.ClaimSubmissionID) FROM dbo.MST_Claim mc WHERE mc.HeaderID=mpach.HeaderID AND mc.ip=1) AS total_ip

FROM dbo.MST_PriorAuthorization_Claim_Header mpach
INNER JOIN dbo.MS_Provider mp ON mp.Provider_ID = mpach.Provider_ID

please show us the structure of the tables involved..

catzilla
– catzilla

2016-01-04 06:26:00 +00:00
Commented Jan 4, 2016 at 6:26 — catzilla
– catzilla, Commented Jan 4, 2016 at 6:26

ChrisGuest · Accepted Answer · 2016-01-04 06:16:13Z

1

Use Sum of CASE Statements to avoid all those subqueries.

SELECT
    mp.ProviderName,
    SUM(CASE WHEN mc.IsActive=1 THEN 1 ELSE 0 END ) AS total_claim,
    SUM(CASE WHEN mc.op=1 THEN 1 ELSE 0 END ) AS total_op,
    SUM(CASE WHEN mc.ip=1 THEN 1 ELSE 0 END ) AS total_ip
FROM dbo.MST_PriorAuthorization_Claim_Header mpach
    INNER JOIN dbo.MS_Provider mp ON mp.Provider_ID = mpach.Provider_ID
    INNER JOIN dbo.MST_Claim mc ON  mc.HeaderID=mpach.HeaderID

answered Jan 4, 2016 at 6:16

ChrisGuest

3,6284 gold badges37 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

eldhopark Over a year ago

i tried these one but my problem is i have to use group by with provider name then my result is going wrong

ChrisGuest Over a year ago

I've frequently used GROUP BY that way. I don't follow how results would be wrong. Besides this was not mentioned in your individual question.

ChrisGuest Over a year ago

You should post a new question if your GROUP BY clause is giving you unexpected results.

Rahul · Accepted Answer · 2016-01-04 06:13:52Z

0

You can modify your query using a CASE statement like below

SELECT
mp.ProviderName
,sum(case when mc.IsActive=1 then mc.ClaimSubmissionID else 0 end) AS total_claim,
sum(case when mc.op=1 then mc.ClaimSubmissionID else 0  end) AS total_op
sum(case when mc.ip=1 then mc.ClaimSubmissionID else 0  end) AS total_ip

FROM dbo.MST_PriorAuthorization_Claim_Header mpach
INNER JOIN dbo.MS_Provider mp ON mp.Provider_ID = mpach.Provider_ID
JOIN dbo.MST_Claim mc ON mc.HeaderID = mpach.HeaderID;

answered Jan 4, 2016 at 6:13

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Collectives™ on Stack Overflow

SQL QUERY with subquery Optimization

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related