Create a job to run twice a week

[mGis]

I need to run a job twice a week to gather users that have joined since the previous run date?

I want to run the job every Tuesday morning at 3:00am and every Friday at 3:00am.
on TblAnswers with the datetime field being Ans_AnsweredTime

So I need to run:
Friday 05/04/2001 and collect all users since Tuesday 05/01/2001
Tuesday 05/11/2001 and collect all users since Friday 05/04/2001
Friday 05/11/2001 and collect all users since Tuesday 05/11/2001
etc.[\code]

SELECT * 
FROM TblAnswers
WHERE Ans_AnsweredTime BETWEEN DateAdd(dd, -4,GetDate()) and GetDate();

I think will give me the user for every four(4) days, but will then have to manually edit the report.

Is there anyway to set up a [COLOR=blue]LastDateRun[/color] variable, so I can say something like:
SELECT * 
FROM TblAnswers
WHERE Ans_AnsweredTime BETWEEN [COLOR=blue]LastDateRun[/color] and GetDate();

 

[mGis]

Forgot to mention that I'm using SQL Serber 7.0
Thanks, any help is greatly appreciated.
Michael

 

[tlbroadbent]

Declare @Begin_date datetime

Select @Begin_date=
Case datepart(dw,getdate()) /* detremine day of the week */
When 3 Then DateAdd(dd, -4,GetDate()) /* Tuesday run */
When 6 Then DateAdd(dd, -3,GetDate()) /* Friday run */
Else getdate() /* running on the wrong day */

SELECT *
FROM TblAnswers
WHERE Ans_AnsweredTime BETWEEN @Begin_date and GetDate();

This assumes that Sunday is the first day of the week. Terry

http://members.home.net/tlbroadbent/index.htm