SELECT 
PR.ClientID
,PR.NewDealEndDate
	INTO 
	  #OriginalRenewalDetails
	FROM  
		Shiva.dbo.ProductRemortgage PR WITH (NOLOCK) 
		LEFT JOIN Shiva.dbo.ClientLead AS CL WITH (NOLOCK) ON CONVERT(VARCHAR(50), CL.OriginatorReferenceID) = CONVERT(VARCHAR(50), PR.ClientID)
	WHERE -- 90 days from now. 
		
		PR.NewDealEndDate = DATEADD(dd, 90, DATEDIFF(dd, 00, GETDATE()))
		AND CL.ClientID IS NULL

	SELECT 
		PR.ClientID
		,PR.NewDealEndDate
	INTO 
	  #OriginalRenewalDetails
	FROM  
		Shiva.dbo.ProductRemortgage PR WITH (NOLOCK) 
		LEFT JOIN Shiva.dbo.ClientLead AS CL WITH (NOLOCK) ON CONVERT(VARCHAR(50), CL.OriginatorReferenceID) = CONVERT(VARCHAR(50), PR.ClientID)
	WHERE -- 90 days from now. 
		PR.NewDealEndDate BETWEEN DATEADD(dd, 88, DATEDIFF(dd, 00, GETDATE())) AND DATEADD(dd, 90, DATEDIFF(dd, 00, GETDATE()))
		AND CL.ClientID IS NULL

Got it; 
[Code]
DECLARE @dteNow AS DATE
SET    @dteNow = GETDATE() 

DECLARE @start SMALLDATETIME
DECLARE @end SMALLDATETIME 
DECLARE @strDate VARCHAR (10)  
SET @strDate = DATENAME(WEEKDAY, GETDATE())  
SET @end = DATEADD(DAY, 102, DATEDIFF(DAY, 0, GETDATE())); 
SET @start = CASE WHEN @strDate = 'Sunday' THEN DATEADD(DAY, -2, @end) ELSE @end END; 

-- Get the upcoming clients for renewal. 
SELECT 
	PR.ClientID
	,PR.NewDealEndDate
FROM  
	Shiva.dbo.ProductRemortgage PR WITH (NOLOCK) 
	LEFT JOIN Shiva.dbo.ClientLead AS CL WITH (NOLOCK) ON CONVERT(VARCHAR(50), CL.OriginatorReferenceID) = CONVERT(VARCHAR(50), PR.ClientID)
WHERE -- 90 days from now. 
	PR.NewDealEndDate BETWEEN @start AND @end
	AND CL.ClientID IS NULL