Query based on Day of Week - 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

SQL Server Development (2000)

Query based on Day of Week

Author

Topic

ddobbertin
Yak Posting Veteran

51 Posts

Posted - 2006-02-28 : 09:52:44

Have a query that I would like to run based on the day of the week for example:

Select * from t_table where
(
if day = 'Monday' then pull previous 'Thursday' data,
if day = 'Tuesday' then pull previous 'Friday' data,
if day = 'Wednesday' then pull previous 'Monday', etc, etc)

Basically I want to pull day from 2 weekdays prior to the date my query is running. Any and all help would be appreciated.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-02-28 : 11:07:28

Using the F_TABLE_DATE function on the link below, this code will return the last two weekdays before today, assuming you consider a weekday to be Monday to Friday.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519&whichpage=1


declare @start_date	datetime
declare @end_date	datetime
select @start_date	= getdate()-5
select @end_date	= getdate()-1

select	top 2
	[DATE]
from 
	dbo.F_TABLE_DATE ( @start_date,@end_date)
where
	[DAY_OF_WEEK]	 between 2 and 6
order by
	[DATE] desc

CODO ERGO SUM

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-02-28 : 19:16:09

Here's another way:

SELECT DATEADD(d, CASE WHEN DATEPART(dw, getdate()) IN (2, 3) THEN -4 ELSE -2 END, getdate())

This doesn't handle cases where today is a Saturday or Sunday because you didn't mention what to do about those days, if they matter.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources