date range. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

date range.

Author

Topic

Pace
Constraint Violating Yak Guru

264 Posts

Posted - 2012-05-21 : 09:58:14

Hi folks,

Struggling to simplify the following proc;

For Mon to Thur I need;

 	
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

But if it's a sunday I need it to do;


	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

Is there a way I can do this off 1 proc and 1 job?

"Impossible is Nothing"

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-05-21 : 10:49:57

perhaps a CASE in the where clause checking the day of the week?

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Pace
Constraint Violating Yak Guru

264 Posts

Posted - 2012-05-21 : 11:21:06

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

[/code]

Thanks for your time viewing etc! :) Happy Coding.

"Impossible is Nothing"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources