Eric M Russell (2/18/2015)

---

Resender (2/2/2015)

---

ok this is something of a best practice of mine in my queries

I always do

WHERE 1 = 1

I can't remember where I picked this habit up...

Velveeta22 (2/18/2015)

---

ChrisM@Work (2/18/2015)

---

In the t4 part of the query, there are significant differences between actual and estimated number of rows / number of executions, indicating that statistics may be wildly...

In the t4 part of the query, there are significant differences between actual and estimated number of rows / number of executions, indicating that statistics may be wildly out of...

Velveeta22 (2/17/2015)

---

Matched the column names to the original stuck query and added friendly alias'. Results pull matching data in both test and prod (fresh data). My hope is...

Here's another way:

SELECT

a.[Pax ID],

a.[Flight Date],

a.[Flight Number],

[Last Mod Date] = CASE WHEN x.[Pax ID] IS NULL THEN a.[Last Mod Date] ELSE x.[Last Mod Date] END,

[Status] = CASE WHEN x.[Pax ID]...

Velveeta22 (2/17/2015)

---

This filter AND O.CompanyClientMemberID IS NOT NULL won't work in the join, if the filter must be active then it must be in the WHERE clause. I'd mark it...

Can you post the *actual* plan?

rodjkidd (2/17/2015)

---

ChrisM@Work (2/17/2015)

---

dwain.c (2/16/2015)

---

ChrisM@Work (2/16/2015)

---

I've booked the day off, mate - need 5 minutes to...

dwain.c (2/16/2015)

---

ChrisM@Work (2/16/2015)

---

I've booked the day off, mate - need 5 minutes to book the...

rodjkidd (2/16/2015)

---

ChrisM@Work (2/16/2015)

---

rodjkidd (2/16/2015)

---

ChrisM@Work (2/16/2015)

---

I've booked the day off, mate - need 5 minutes to...

TomThomson (2/16/2015)

---

ChrisM@Work (2/16/2015)

---

Stop digging, Tom! For goodness' sake, you're coming across as a cranky old fella with a short fuse. This is not what we are used to seeing...

Velveeta22 (2/16/2015)

---

This produces the same result set in both the test environment and production. Does anyone see any logic fail on the re-write?

DECLARE @p0 DATETIME = '2099-09-09 00:00:00.000';

DECLARE @p1...

sgmunson (2/16/2015)

---

The following part of the LEFT JOIN isn't needed:

AND O.CompanyClientMemberID IS NOT NULL

That field is already part of the rest of the JOIN condition (the specific line follows) based...

First off, here's a modified ddl / dml script. It's a little more compressed than the original, and possibly easier to adjust for data changes:

CREATE DATABASE OutBox;

GO

USE [OutBox]

GO

---------------------------------------------------------------------------------------------------

-- [dbo].[Outbox]

---------------------------------------------------------------------------------------------------

SET ANSI_NULLS...

rodjkidd (2/16/2015)

---

ChrisM@Work (2/16/2015)

---

I've booked the day off, mate - need 5 minutes to book the...