Need to run extract weekly on Tuesday

I need to build into a SQL query that this extract will run every Tuesday.  I was thinking I could use the date_dimension table that has Day_of_WEek column = Tuesday, or Day_of_Week_Index = 2.  But that is all I have.  Can someone help with this?

Right now my query is set up like this:
declare @yearmonth varchar(15) = '201709'

date_dimension ar_date
INNER JOIN hsp_bkt_snapshot bkt_snap on bkt_snap.snap_start_date <= ar_date.calendar_dt and bkt_snap.snap_end_date >= ar_date.calendar_dt
INNER JOIN hsp_har_snapshot har_snap on bkt_snap.HSP_ACCOUNT_ID = har_snap.HSP_ACCOUNT_ID AND har_snap.snap_start_date <= ar_date.CALENDAR_DT and har_snap.SNAP_END_DATE >= ar_date.CALENDAR_DT
Becky EdwardsEpic Clarity DeveloperAsked:
Who is Participating?
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Becky, just create a job to run the script every Tuesdays. Then you can use the GETDATE() function in your code as it will return always a Tuesday date.
0
 
Brian CroweDatabase AdministratorCommented:
I'm a little confused as to what you are asking for and the code provided is incomplete.  Are you looking for a task to run every tuesday or a query that pulls data for tuesdays or something else entirely?
0
 
Mark WillsTopic AdvisorCommented:
Agree with comments made by both Brian and Vitor.

And you would also need to add in after the joins, in addition to any other conditions...

WHERE ar_date.Day_of_Week_Index = 2

Open in new window

0
 
Becky EdwardsEpic Clarity DeveloperAuthor Commented:
Thank you!  This will work perfect.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.