help with where clause - 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 2000 Forums

Transact-SQL (2000)

help with where clause

Author

Topic

blackX
Posting Yak Master

102 Posts

Posted - 2009-09-08 : 08:52:07

I have the following SQL in a where clause in a database view

(ContactDate BETWEEN DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) AND DATEADD(dd, 1, DATEDIFF(dd, 0,GETDATE())))

I need to change it to say that if the current time is after 1AM then execute the code above, otherwise replace getdate with the day before. (ContactDate BETWEEN DATEADD(dd, 0, DATEDIFF(dd, 0, dateadd(day,-1,getdate()))) AND DATEADD(dd, 1, DATEDIFF(dd, 0,dateadd(day,-1,getdate()))))

I can get this to work using by declaring a variable a variable don't work in a view. There is an production application that uses this view so it cannot change to much, just the underlysing data that it pulls. I tried wraping the condition in a case statement but that didn't work. Any help would be great.

Travis

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-08 : 09:01:09

where
(
getdate()<=dateadd(day,datediff(day,0,getdate()),0)+'01:00:00'
and
(ContactDate BETWEEN DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) AND DATEADD(dd, 1, DATEDIFF(dd, 0,GETDATE())))
)
or
(
getdate()>dateadd(day,datediff(day,0,getdate()),0)+'01:00:00'
and
(ContactDate BETWEEN DATEADD(dd, 0, DATEDIFF(dd, 0, dateadd(day,-1,getdate()))) AND DATEADD(dd, 1, DATEDIFF(dd, 0,dateadd(day,-1,getdate()))))
)

Madhivanan

Failing to plan is Planning to fail

blackX
Posting Yak Master

102 Posts

Posted - 2009-09-09 : 08:33:05

Thanks for your help. However this returns both conditions all the time. I just need one or the other depending on what time it is. If the current time at the server is between 12am and 1am then return data based on getdate - 1 day, otherwise return data based on the current date.

I hope I explained this clearly

Travis

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-09 : 08:50:14

<<
If the current time at the server is between 12am and 1am then return data based on getdate - 1 day
>>

This contradicts with your original post

Madhivanan

Failing to plan is Planning to fail

blackX
Posting Yak Master

102 Posts

Posted - 2009-09-09 : 10:10:43

I don't see the contractdiction but to be clear, I will try to explain one more time. The current where clause contains

(ContactDate BETWEEN DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) AND DATEADD(dd, 1, DATEDIFF(dd, 0,GETDATE())))

I would like to slightly change this to the following:
If the time part of getdate() returns a time before 1AM (between midnight and 1AM) then I want the following

(ContactDate BETWEEN DATEADD(dd, 0, DATEDIFF(dd, 0, dateadd(day,-1,getdate()))) AND DATEADD(dd, 1, DATEDIFF(dd, 0,dateadd(day,-1,getdate()))))

if the time is after 1AM then I need the original where clause. I have been able to successfully do it with a variable, but I cannot use a variable inside a View.

To give a little insight, this view is used inside a call center application. Amoungst other things it is used to count the number of contacts in the database. The reporting is sometimes done after midnight. The easiest remedy to the problem is changing the view rather than the application itself.

The SQL that you provided works, but it pulls both the current day and the previous day. We only need the current day, unless it is before 1AM then we want to pull the previous day(this leaves a 1 hr window to complete the reporting should on the previous days data)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-10 : 02:34:53

Have you tried this?


if
	(
	 getdate()>=dateadd(day,datediff(day,0,getdate()),0)+'00:00:00' 
	and 
	getdate()<=dateadd(day,datediff(day,0,getdate()),0)+'01:00:00' 
	)
		select .........
		where
		(ContactDate BETWEEN DATEADD(dd, 0, DATEDIFF(dd, 0, dateadd(day,-1,getdate()))) AND DATEADD(dd, 1, DATEDIFF(dd, 0,dateadd(day,-1,getdate())))) 
else
		select .........
		where
		(ContactDate BETWEEN DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) AND DATEADD(dd, 1, DATEDIFF(dd, 0,GETDATE())))

Madhivanan

Failing to plan is Planning to fail

blackX
Posting Yak Master

102 Posts

Posted - 2009-09-11 : 09:00:58

As a query it works, however it needs to be a view. When I try to Alter the view with the SQL, I get this error:

Incorrect syntax near the keyword 'if'.

If I comment out the Alter View DailyContacts as

it works. Any ideas?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-11 : 09:41:29

Try this


where
(
(
	 getdate()>=dateadd(day,datediff(day,0,getdate()),0)+'00:00:00' 
	and 
	getdate()<=dateadd(day,datediff(day,0,getdate()),0)+'01:00:00' 
)

and 
(
ContactDate BETWEEN DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) AND DATEADD(dd, 1, DATEDIFF(dd, 0,GETDATE()))
)
)
or
(
(
getdate()>dateadd(day,datediff(day,0,getdate()),0)+'01:00:00' 
and 
(ContactDate BETWEEN DATEADD(dd, 0, DATEDIFF(dd, 0, dateadd(day,-1,getdate()))) AND DATEADD(dd, 1, DATEDIFF(dd, 0,dateadd(day,-1,getdate())))) 
)
)

Madhivanan

Failing to plan is Planning to fail

blackX
Posting Yak Master

102 Posts

Posted - 2009-09-11 : 09:59:06

That appears to work. I will have to wait until after midnight to fully test it.

thanks for all your help

Travis

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources