I bet I'm not the only one around here who sits on the next potential iteration of the OP's query waiting for responses before posting. Here's one of those:

SELECT --DISTINCT...

Have you tried this?

SELECT Distinct Systems.SiteNumber AS 'SiteNumber',

...

mbavabohrude (4/26/2016)

---

I just commented and ran it fetches around 1074733 in 9 mins .When i run the inner query it fetches one row

when i run the inner join seperately...

Comment out the derived table, then selectively remove and restore the filters from the WHERE clause. Observe what happens. How many rows are in table dbo.PolicyAudit with p.Status ...

mbavabohrude (4/26/2016)

---

Thanks for your info . When i just run the query i am still getting one record alone . Why i am not getting all the policy records in...

Try this alternative to your query. The significant change is removing the right join. Most folks avoid them - a mix of right joins and left joins in the same...

There are numerous RID lookups in the plan. It's rare for a table to play better without a clustered index than with one. There's even an ordinary index called PK_...

Manchester. That's where my ex wife lives.

There are a couple of ways to rewrite this code, discussed here. Choose one, document it, and encourage folks to stick with it.

Another way:

WITH SampleData ([Employee No.], [Rag Rating], [Timestamp]) AS (

SELECT 1, 'Green', CONVERT(DATE,'21/04/2016',103) UNION ALL

SELECT 1, 'Yellow', CONVERT(DATE,'20/04/2016',103) UNION ALL

SELECT 1, 'Yellow', CONVERT(DATE,'19/04/2016',103) UNION ALL

SELECT 1, 'Green', CONVERT(DATE,'18/04/2016',103) UNION ALL

SELECT...

There's some discussion of it following this post.

below86 (4/20/2016)

---

Not sure it will make a difference, but this is what I would do, I also added comments on a few things that looked questionable to me. try...

VSSGeorge (4/19/2016)

---

I am attaching the actual execution plan available.

The full execution plan is not available as an error message is encountered.

Msg 1105, Level 17, State 2, Line 61

Could not allocate...

Can you post it as a .sqlplan file attachment please?

Those two UNIONed queries would probably be more efficient run as one query. also, is that left-joined table actually necessary except for the outer SELECT? This is what I mean:

SELECT

GLItems.GLChartOfAccount,

GLItemValue...