harshada_joshi - Monday, November 27, 2017 8:39 PM
You have a date range filter which uses crazy "alternative" logic. Change it to something more standard and it's likely to be SARGable (Google it). Does this - '4/27/17' - get passed in as a variable? You want to end up with something like this:WHERE (@CompareDate IS NULL OR Schedule.StartDate >= @CompareDate)
AND (@CompareDate IS NULL OR Schedule.EndDate < DATEADD(DAY,1,@CompareDate))
You have another crazy filter in your WHERE clause which is based on the variable @ChecklistTypeID. I think it can be changed to this:WHERE (@ChecklistTypeID IS NULL AND Checklist.AudittypeID = 20)
OR (Checklist.ChecklistTypeID = @ChecklistTypeID)
Whether or not you're planning to show your code to the world, it's always good to have it nicely formatted and commented for readability.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden