CODEWITHSUNDEEP
sql-server-2008
Brijesh
Brijesh

Reputation: 15

Can anyone help me to optimize my query?

 SELECT p.ID, Name
   FROM Policies p
        INNER JOIN ProgramYears py ON p.ProgramYearID = py.id
        INNER JOIN (SELECT MemberID, max(EffectiveDate) AS EffectiveDate
                      FROM Policies
                     GROUP BY MemberID) TEMP
                ON p.memberid = TEMP.MemberID
               AND p.EffectiveDate = TEMP.effectivedate
               AND p.memberid NOT IN (SELECT MemberID
                                        FROM InvoiceDetail
                                       WHERE ProgramYear = NAME)

Upvotes: 1

Views: 66

Answers (2)

Justin
Justin

Reputation: 9724

You can try:

 SELECT a.ID, a.Name
   FROM (SELECT p.ID, Name,
                ROW_NUMBER()OVER(PARTITION BY p.memberid ORDER BY p.EffectiveDate DESC) AS rnk
           FROM Policies p
                INNER JOIN ProgramYears py ON p.ProgramYearID = py.id
          WHERE NOT EXISTS (SELECT MemberID
                              FROM InvoiceDetail AS ID
                             WHERE ID.ProgramYear = NAME
                               AND p.MemberId = ID.MemberId)
        ) a
  WHERE a.rnk = 1

Upvotes: 0

Kevin Hogg
Kevin Hogg

Reputation: 1781

NOT EXISTS is usually a better substitute for NOT IN, but your choices largely depend on the data and the structure of your tables and indexes.

Try the query below, but compare its execution plan to that of your current query; what works for one scenario may not work for another.

 SELECT p.ID, Name
   FROM Policies p
        INNER JOIN ProgramYears py ON p.ProgramYearID = py.id
        INNER JOIN (SELECT MemberID, max(EffectiveDate) AS EffectiveDate
                      FROM Policies
                     GROUP BY MemberID) TEMP
                ON p.memberid = TEMP.MemberID
                   AND p.EffectiveDate = TEMP.effectivedate
  WHERE NOT EXISTS
            (SELECT MemberID
               FROM InvoiceDetail AS ID
              WHERE ID.ProgramYear = NAME
                AND p.MemberId = ID.MemberId)

Upvotes: 1

Related Questions