CODEWITHSUNDEEP
sql
eldhopark
eldhopark

Reputation: 31

SQL QUERY with subquery Optimization

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

Upvotes: 0

Views: 49

Answers (2)

ChrisGuest
ChrisGuest

Reputation: 3608

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

Upvotes: 1

Rahul
Rahul

Reputation: 77866

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;

Upvotes: 0

Related Questions