Automic Workload Automation

I have a job that runs daily between May and September. It requires a static input date of the 1st Friday of July - so the date for 2019 will be July 5th for each run. For 2020 ( a leap year), the date will be July 3rd, etc. Is there a way (there has to be a way), to set up a calendar keyword or automic code that will take the date value of the 1st friday of july and insert it into a variable table? I need to take that date value and use it to modify a peoplesoft run control with the new date every year. 

Figured it out myself and I'll just calendar this job to run on july 1st every year....

:SET &CURDATE# = SYS_DATE()
:SET &DATE# = FIRST_OF_PERIOD ("&CURDATE#:","MM","YYYYMMDD")
:PRINT &DATE#
:SET &DAY# = WEEKDAY_XX(&DATE#)
:PRINT &DAY#
:IF &DAY# = 'MO'
:SET &FIRST_FRIDAY_JULY = ADD_DAYS('&DATE#', 4)
:PRINT &FIRST_FRIDAY_JULY
:PUT_VAR UCB_TERM,"SRB_FALL_POD2_DATE","&FIRST_FRIDAY_JULY"
:ELSE
:IF &DAY# = 'DI'
:SET &FIRST_FRIDAY_JULY = ADD_DAYS('&DATE#', 3)
:PUT_VAR UCB_TERM,"SRB_FALL_POD2_DATE","&FIRST_FRIDAY_JULY"
:PRINT &FIRST_FRIDAY_JULY
:ELSE
:IF &DAY# = 'MI'
:SET &FIRST_FRIDAY_JULY = ADD_DAYS('&DATE#', 2)
:PUT_VAR UCB_TERM,"SRB_FALL_POD2_DATE","&FIRST_FRIDAY_JULY"
:PRINT &FIRST_FRIDAY_JULY
:ELSE
:IF &DAY# = 'DO'
:SET &FIRST_FRIDAY_JULY = ADD_DAYS('&DATE#', 1)
:PUT_VAR UCB_TERM,"SRB_FALL_POD2_DATE","&FIRST_FRIDAY_JULY"
:PRINT &FIRST_FRIDAY_JULY
:ELSE
:IF &DAY# = 'SA'
:SET &FIRST_FRIDAY_JULY = ADD_DAYS('&DATE#', 6)
:PUT_VAR UCB_TERM,"SRB_FALL_POD2_DATE","&FIRST_FRIDAY_JULY"
:PRINT &FIRST_FRIDAY_JULY
:ELSE
: IF &DAY# = 'SO'
:SET &FIRST_FRIDAY_JULY = ADD_DAYS('&DATE#', 5)
:PUT_VAR UCB_TERM,"SRB_FALL_POD2_DATE","&FIRST_FRIDAY_JULY"
:PRINT &FIRST_FRIDAY_JULY
:ELSE
: IF &DAY# = 'FR'
:SET &FIRST_FRIDAY_JULY = &DAY#
:PRINT &FIRST_FRIDAY_JULY
:PUT_VAR UCB_TERM,"SRB_FALL_POD2_DATE","&FIRST_FRIDAY_JULY"
:ENDIF
:ENDIF
:ENDIF
:ENDIF
:ENDIF
:ENDIF
:ENDIF

There's probably cleaner ways to do this, but I found the following that pulls the first Friday of every month (I have it set for 2019 - 2029)

with tmp(plant_date,dw) as
(
select cast('20190101' as datetime),DATENAME(weekday,'20190101')
union all
select plant_date + 1,DATENAME (weekday,plant_date+1)
from tmp
where plant_date < '20291231'
)
select MIN(plant_date)
from tmp where dw='Friday'
GROUP BY DATEADD(m,DATEDIFF(m,0,plant_date),0)
option (maxrecursion 0)

I was able to toss that into a variable, and then you could parse through it and pull whatever you wanted out. 

I'll play with it some more and see if I can find a better way.

Oh thank you so much for trying to help me with this. I have wrecked my brain on it! I did figure out some automic code just a few minutes ago that I just got done testing out. It might be little clunky and there are probably better ways to do it – I wish I knew how to write the sql you wrote – I wish I had a sql agent up where I could run the sql codes! Anyway – this is what I came up:

!the current date should be july 1st of any year so this code sets the date as the first

!of the period (july)

:SET &CURDATE# = SYS_DATE()

:SET &DATE# = FIRST_OF_PERIOD ("&CURDATE#:","MM","YYYYMMDD")

:PRINT &DATE#

!this line determines what day of the week the 1st falls on

:SET &DAY# = WEEKDAY_XX(&DATE#)

:PRINT &DAY#

!if the day of the week is a monday, the code adds four days to the 1st and stores the new

!date in the variable table

:IF &DAY# = 'MO'

:SET &FIRST_FRIDAY_JULY = ADD_DAYS('&DATE#', 4)

:PRINT &FIRST_FRIDAY_JULY

:PUT_VAR UCB_TERM,"SRB_FALL_POD2_DATE","&FIRST_FRIDAY_JULY"

:ELSE

!if the day of the week is a tuesday, the code adds three days to the 1st and stores the new

!date in the variable table

:IF &DAY# = 'DI'

:SET &FIRST_FRIDAY_JULY = ADD_DAYS('&DATE#', 3)

:PUT_VAR UCB_TERM,"SRB_FALL_POD2_DATE","&FIRST_FRIDAY_JULY"

:PRINT &FIRST_FRIDAY_JULY

:ELSE

!if the day of the week is a wednesday, the code adds two days to the 1st and stores the new

!date in the variable table

:IF &DAY# = 'MI'

:SET &FIRST_FRIDAY_JULY = ADD_DAYS('&DATE#', 2)

:PUT_VAR UCB_TERM,"SRB_FALL_POD2_DATE","&FIRST_FRIDAY_JULY"

:PRINT &FIRST_FRIDAY_JULY

:ELSE

!if the day of the week is a thursday, the code adds one day to the 1st and stores the new

!date in the variable table

:IF &DAY# = 'DO'

:SET &FIRST_FRIDAY_JULY = ADD_DAYS('&DATE#', 1)

:PUT_VAR UCB_TERM,"SRB_FALL_POD2_DATE","&FIRST_FRIDAY_JULY"

:PRINT &FIRST_FRIDAY_JULY

:ELSE

!if the day of the week is a saturday, the code adds six days to the 1st and stores the new

!date in the variable table

:IF &DAY# = 'SA'

:SET &FIRST_FRIDAY_JULY = ADD_DAYS('&DATE#', 6)

:PUT_VAR UCB_TERM,"SRB_FALL_POD2_DATE","&FIRST_FRIDAY_JULY"

:PRINT &FIRST_FRIDAY_JULY

:ELSE

!if the day of the week is a sunday, the code adds five days to the 1st and stores the new

!date in the variable table

:  IF &DAY# = 'SO'

:SET &FIRST_FRIDAY_JULY = ADD_DAYS('&DATE#', 5)

:PUT_VAR UCB_TERM,"SRB_FALL_POD2_DATE","&FIRST_FRIDAY_JULY"

:PRINT &FIRST_FRIDAY_JULY

:ELSE

!if the day of the week is a friday, the code just stores the date in the variable table

:  IF &DAY# = 'FR'

:SET &FIRST_FRIDAY_JULY = &DAY#

:PRINT &FIRST_FRIDAY_JULY

:PUT_VAR UCB_TERM,"SRB_FALL_POD2_DATE","&FIRST_FRIDAY_JULY"

:ENDIF

:ENDIF

:ENDIF

:ENDIF

:ENDIF

:ENDIF

:ENDIF

Christine Bauder

Assistant Director of Production Services, University Information Services

University of Colorado

1800 Grant Street, Suite 200

Denver, CO  80203

t  303 860 4334

c 303 263 3288

www.cu.edu<http://www.cu.edu/>

<http://www.cu.edu/uis>

You know I dont think I'd ever used weekday_xx

I cleaned up the version you had and I think this would work a little more compactly:

:SET &CURDATE# = SYS_DATE()
:SET &DATE# = FIRST_OF_PERIOD("&CURDATE#:", "MM", "YYYYMMDD")
:PRINT &DATE#
:SET &DAY# = WEEKDAY_XX(&DATE#)
:SWITCH &DAY#
: CASE MO
: SET &FIRST_FRIDAY_JULY = ADD_DAYS('&DATE#', 4)
: CASE DI
: SET &FIRST_FRIDAY_JULY = ADD_DAYS('&DATE#', 3)
: CASE MI
: SET &FIRST_FRIDAY_JULY = ADD_DAYS('&DATE#', 2)
: CASE DO
: SET &FIRST_FRIDAY_JULY = ADD_DAYS('&DATE#', 1)
: CASE SA
: SET &FIRST_FRIDAY_JULY = ADD_DAYS('&DATE#', 6)
: CASE SO
: SET &FIRST_FRIDAY_JULY = ADD_DAYS('&DATE#', 5)
: CASE FR
: SET &FIRST_FRIDAY_JULY = &DAY#
:ENDSWITCH
:PUT_VAR UCB_TERM, "SRB_FALL_POD2_DATE", "&FIRST_FRIDAY_JULY"

I think I still like the SQL method better, but limitations.

I’ve never used the weekday_xx before either, nor have I used :CASE – throwing your cleaner version into my test environment now – thank you so much!!

Christine Bauder

Assistant Director of Production Services, University Information Services

University of Colorado

1800 Grant Street, Suite 200

Denver, CO  80203

t  303 860 4334

c 303 263 3288

www.cu.edu<http://www.cu.edu/>

Your cleaner version works too. Thanks so much for your help on this.

Christine Bauder

Assistant Director of Production Services, University Information Services

University of Colorado

1800 Grant Street, Suite 200

Denver, CO  80203

t  303 860 4334

c 303 263 3288

www.cu.edu<http://www.cu.edu/>