SELECT DISTINCT
		rs.DonorID
FROM	dbo.RSIntegration_EligibleDonors rs WITH(NOLOCK) 
		INNER JOIN dbo.RSIntegration_DonorListData rsdld (NOLOCK) ON rs.DonorID = rsdld.DonorID 
		INNER JOIN dbo.Donor d WITH(NOLOCK) ON rs.DonorID = d.DonorID 
		INNER JOIN dbo.DonorEligibilityDates deda WITH (NOLOCK) ON rs.DonorID = deda.DonorID 
		INNER JOIN dbo.OpTypeDefinitions dedot WITH (NOLOCK) ON deda.EligibilityTypeID = dedot.EligibilityTypeID 
		LEFT OUTER JOIN (
						SELECT 	DISTINCT d.DonorID
						FROM	dbo.Donor d WITH(NOLOCK)
								INNER JOIN dbo.DonorAssoc da WITH(NOLOCK) ON d.DonorID = da.DonorID
								INNER JOIN dbo.Donation do WITH(NOLOCK) ON da.DonorID = do.DonorID and da.HostID = do.HostID
								INNER JOIN dbo.Op o WITH(NOLOCK) ON da.HostID = o.HostID
								LEFT OUTER JOIN dbo.DonorDisAssoc dda WITH(NOLOCK) ON da.DonorID = dda.DonorID and da.HostID = dda.HostID
						WHERE	ISNULL(da.HostID,0) <> 0
								AND NOT do.ShowDateTime BETWEEN CONVERT(datetime,CONVERT(varchar(10),DATEADD(dd,-61,GETDATE()),(101))) AND CONVERT(datetime,CONVERT(varchar(10),GETDATE(),(101)))
								AND o.OpStartDateTime BETWEEN CONVERT(datetime,CONVERT(varchar(10),GETDATE(),(101))) and CONVERT(datetime,CONVERT(varchar(10),DATEADD(dd,60,GETDATE()),(101)))
								AND o.OpStatusID IN (1,3,8,9)
								AND dda.DonorID IS NULL
						) ms ON rs.DonorID = ms.DonorID
WHERE	d.Active=1 
		AND ISNULL(rs.Donor_Status,'') <> 'Deceased'
		AND (ISNULL(rs.LastVisitDate,'01/01/1900') >= DATEADD(dd,-730,CAST(CONVERT(varchar(50),GETDATE(),101) AS DATETIME)))
		AND ISNULL(d.isRecruitable,1) = 1
		AND d.DOB <= CONVERT(datetime,CONVERT(varchar(10),DATEADD(YYYY,-18,GETDATE()),(101))) 
		AND ISNULL(rs.HasPhone,'NO') = 'YES' 
		AND NOT ISNULL(rs.IsOptedOutPhone,'NO') = 'YES' 
		AND rs.Zip IN (SELECT ZipCode FROM dbo.fn_ZipsInRadius_Multi('32765,32701',10)) 
		AND ( dedot.OpTypeID in (1,2,4)
		AND (ISNULL(rs.WBElig,'01/01/1900') <= DATEADD(dd,0,CAST(CONVERT(varchar(50),GETDATE(),101) AS DATETIME))))
		AND ms.DonorID IS NULL
		AND NOT rs.DonorID IN (SELECT DISTINCT DonorID FROM dbo.DonorAttribute WHERE AttributeValueID = 112 )
		AND NOT ISNULL(rsdld.NextAppointmentDate, '01/01/1900') >= CONVERT(datetime,CONVERT(varchar(10),GETDATE(),(101)))

SELECT d.DonorID
FROM dbo.Donor d
WHERE d.Active = 1
	AND d.DOB <= DATEADD(year, - 18, CURRENT_TIMESTAMP)
	AND (d.isRecruitable = 1 OR d.isRecruitable IS NULL)
	AND EXISTS
	(
		SELECT 1
		FROM dbo.RSIntegration_EligibleDonors rs
		WHERE d.DonorID = rs.DonorID
			AND rs.Donor_Status <> 'Deceased'
			AND rs.LastVisitDate >= DATEADD(day, DATEDIFF(d, 0, CURRENT_TIMESTAMP) -730, 0)
			AND rs.HasPhone = 'YES'
			AND rs.IsOptedOutPhone = 'YES'
			AND NOT rs.WBElig > DATEADD(day, DATEDIFF(d, 0, CURRENT_TIMESTAMP), 0)
			AND EXISTS
			(
				SELECT 1
				FROM dbo.fn_ZipsInRadius_Multi('32765,32701',10) M
				WHERE M.ZipCode = rs.Zip
			)
			AND NOT EXISTS
			(
				SELECT 1
				FROM dbo.DonorAttribute Attrib
				WHERE Attrib.DonorID = rs.DonorID
					AND Attrib.AttributeValueID = 112
			)
	)
	AND EXISTS
	(
		SELECT 1
		FROM dbo.RSIntegration_DonorListData rsdld
		WHERE r.DonorID = rsdld.DonorID
			AND NOT rsdld.NextAppointmentDate >= DATEADD(day, DATEDIFF(d, 0, CURRENT_TIMESTAMP), 0
	)
	AND EXISTS
	(
		SELECT 1
		FROM dbo.DonorEligibilityDates deda
		WHERE deda.DonorID = d.DonorID
			AND EXISTS
			(
				SELECT 1
				FROM  dbo.OpTypeDefinitions dedot
				WHERE deda.EligibilityTypeID = dedot.EligibilityTypeID
					AND dedot.OpTypeID IN (1,2,4)
			)
	)
	AND NOT EXISTS
	(
		SELECT 1
		FROM dbo.DonorAssoc da
		WHERE da.DonorID = d.DonorID
			AND da.HostID <> 0
			AND EXISTS
			(
				SELECT 1
				FROM dbo.Donation do
				WHERE da.DonorID = do.DonorID
					AND da.HostID = do.HostID
					AND NOT do.ShowDateTime
						BETWEEN DATEADD(day, DATEDIFF(d, 0, CURRENT_TIMESTAMP) -61, 0)
						AND DATEADD(day, DATEDIFF(d, 0, CURRENT_TIMESTAMP), 0)
			)
			AND EXISTS
			(
				SELECT 1
				FROM dbo.Op o
				WHERE da.HostID = o.HostID
					AND o.OpStartDateTime
						BETWEEN DATEADD(day, DATEDIFF(d, 0, CURRENT_TIMESTAMP), 0)
						AND DATEADD(day, DATEDIFF(d, 0, CURRENT_TIMESTAMP) + 60, 0)
					AND o.OpStatusID IN (1,3,8,9)
			)
			AND NOT EXISTS
			(
				SELECT 1
				FROM dbo.DonorDisAssoc dda
				WHERE da.DonorID = dda.DonorID
					AND da.HostID = dda.HostID
			)
	);

quote:

---

Originally posted by GilaMonster

I assume, since you have nolock everywhere, that accuracy of data is not a major requirement here.

--
Gail Shaw
SQL Server MVP

---

quote:

---

Originally posted by denis_the_thief

quote:

---

Originally posted by GilaMonster

I assume, since you have nolock everywhere, that accuracy of data is not a major requirement here.

--
Gail Shaw
SQL Server MVP

---

It does look scary given the context (i.e. Organ Donors)

---

SELECT rs.[DonorID]
FROM
	dbo.RSIntegration_EligibleDonors AS rs
	JOIN dbo.RSIntegration_DonorListData AS rsdld ON rs.[DonorID] = rsdld.[DonorID]
	JOIN dbo.Donor AS d ON rs.[DonorID] = d.[DonorID]
	JOIN dbo.DonorEligibilityDates AS deda ON rs.[DonorID] = deda.[DonorID]
	JOIN dbo.OpTypeDefinitions AS dedot ON deda.[EligibilityTypeID] = dedot.[EligibilityTypeID]

	LEFT JOIN (
		SELECT da.DonorID -- Removing useless reference to dbo.Donor in derived table
		FROM
			dbo.DonorAssoc da 
			JOIN dbo.Op AS o ON da.HostID = o.HostID
			JOIN dbo.Donation do ON da.DonorID = do.DonorID AND da.HostID = do.HostID
		WHERE
			da.[HostID] <> 0 -- ISNULL(da.HostID,0) <> 0 This can't happen because of the inner join on da.[HostID] -> o.[HostID] This is probably useless

			-- GAH. Change those columns to DATETIME types. Then get rid of this CONVERT nonsense
			AND NOT do.ShowDateTime BETWEEN CONVERT(datetime,CONVERT(varchar(10),DATEADD(dd,-61,GETDATE()),(101))) AND CONVERT(datetime,CONVERT(varchar(10),GETDATE(),(101)))
			AND o.OpStartDateTime BETWEEN CONVERT(datetime,CONVERT(varchar(10),GETDATE(),(101))) and CONVERT(datetime,CONVERT(varchar(10),DATEADD(dd,60,GETDATE()),(101)))

			AND o.OpStatusID IN (1,3,8,9)

			-- AND dda.DonorID IS NULL (Moved Left JOIN to a NOT EXISTS clause, This has a big impact when the column(s) invoved can be null)
			AND NOT EXISTS (
				SELECT 1
				FROM dbo.DonorDisAddoc AS dda
				WHERE dda.[DonorID] = da.[DonorID]
				  AND dda.[HostID] = da.[HostID]
				)

		)
		AS ms ON rs.DonorID = ms.DonorID

WHERE	d.Active=1 
		AND ISNULL(rs.Donor_Status,'') <> 'Deceased'
		AND (ISNULL(rs.LastVisitDate,'01/01/1900') >= DATEADD(dd,-730,CAST(CONVERT(varchar(50),GETDATE(),101) AS DATETIME)))
		AND ISNULL(d.isRecruitable,1) = 1
		AND d.DOB <= CONVERT(datetime,CONVERT(varchar(10),DATEADD(YYYY,-18,GETDATE()),(101))) 
		AND ISNULL(rs.HasPhone,'NO') = 'YES' 
		AND NOT ISNULL(rs.IsOptedOutPhone,'NO') = 'YES' 
		AND rs.Zip IN (SELECT ZipCode FROM dbo.fn_ZipsInRadius_Multi('32765,32701',10)) 
		AND ( dedot.OpTypeID in (1,2,4)
		AND (ISNULL(rs.WBElig,'01/01/1900') <= DATEADD(dd,0,CAST(CONVERT(varchar(50),GETDATE(),101) AS DATETIME))))
		AND ms.DonorID IS NULL
		AND NOT rs.DonorID IN (SELECT DISTINCT DonorID FROM dbo.DonorAttribute WHERE AttributeValueID = 112 )
		AND NOT ISNULL(rsdld.NextAppointmentDate, '01/01/1900') >= CONVERT(datetime,CONVERT(varchar(10),GETDATE(),(101)))

quote:

---

Originally posted by GilaMonster

Which do you think would be worse there, double-reading rows or missing rows?

--
Gail Shaw
SQL Server MVP

---