I am trying to write a program that will find the Saturday and Sunday of Each week when given a start date, and will end when it reached the end date. I am automating an old SQR program so we don't have to go in there and manually put in the dates for each Saturday and Sunday within the date range. I know SQL has a weekday() function, and I was wondering if there was a way to achieve a similar result in SQR. And I know that currently my code is FILLED with syntax errors because I have been trying to fix one piece at a time. But any advice/help works.
LET $start_day = WEEKDAY(&AC.FROM_DATE)
LET $end_day = WEEKDAY(&AC.TO_DATE)
!LET $start_day = &AC.FROM_DATE
!LET $end_day = &AC.TO_DATE
!LET $start_day = &AC.FROM_DATE(1, 'YYYY-MM-DD')
!LET $end_day = &AC.TO_DATE(1, 'YYYY-MM-DD')
LET $sat1 = DATEADD(&AC.FROM_DATE, 'day' ,5 - $start_day)
LET $sun1 = DATEADD(&AC.FROM_DATE, 'day' ,6 - $start_day)
!I KNOW THAT MY DATEADD FUNCTION IS NOT CORRECT...I THINK
IF $sun1 < $end_day
LET $sat2 = DATEADD(&AC.FROM_DATE, 5 - $start_day + 7, 'day')
LET $sun2 = DATEADD(&AC.FROM_DATE, 6 - $start_day + 7, 'day')
IF $sun2 < $end_day
LET $sat3 = DATEADD(&AC.FROM_DATE, 5 - $start_day + 14, 'day')
LET $sun3 = DATEADD(&AC.FROM_DATE, 6 - $start_day + 14, 'day')
IF $sun3 < $end_day
LET $sat4 = DATEADD(&AC.FROM_DATE, 5 - $start_day + 21, 'day')
LET $sun4 = DATEADD(&AC.FROM_DATE, 6 - $start_day + 21, 'day')
IF $sun4 < $end_day
LET $sat5 = DATEADD(&AC.FROM_DATE, 6 - $start_day + 28, 'day')
LET $sun5 = DATEADD(&AC.FROM_DATE, 7 - $start_day + 28, 'day')
IF $sun5 < $end_day
LET $sat6 = DATEADD(&AC.FROM_DATE, 6 - $start_day + 35, 'day')
LET $sun6 = DATEADD(&AC.FROM_DATE, 7 - $start_day + 35, 'day')
IF $sun6 < $end_day
LET $sat7 = DATEADD(&AC.FROM_DATE, 6 - $start_day + 42, 'day')
LET $sun7 = DATEADD(&AC.FROM_DATE, 7 - $start_day + 42, 'day')
IF $sun7 < $end_day
LET $sat8 = DATEADD(&AC.FROM_DATE, 6 - $start_day + 49, 'day')
LET $sun8 = DATEADD(&AC.FROM_DATE, 7 - $start_day + 49, 'day')
IF $sun8 < $end_day
LET $sat9 = DATEADD(&AC.FROM_DATE, 6 - $start_day + 56, 'day')
LET $sun9 = DATEADD(&AC.FROM_DATE, 7 - $start_day + 56, 'day')
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
END-IF
I don't have SQR on this computer, but if I remember correctly, you can do something like this.
create-array name=weekend size=100 extent=10
field=sun:date
field=sat:date
let #weekend = 0
let $FD = dateToStr(&AC.FROM_DATE, 'YYYYMMDD')
let $TD = dateToStr(&AC.TO_DATE, 'YYYYMMDD')
while $FD <= $TD
let $FD = strToDate($FD, 'YYYYMMDD')
evaluate dateToStr($FD, 'D')
when = '1'
let weekend.sun(#weekend) = $FD
let $FD = dateToStr(dateAdd($FD, 'DAY', 6), 'YYYYMMDD')
break
when = '2'
let weekend.sat(#weekend) = $FD
let $FD = dateToStr(dateAdd($FD, 'DAY', 1), 'YYYYMMDD')
add 1 to #weekend
break
when-other
let $FD = dateToStr(dateAdd($FD, 'DAY', 1), 'YYYYMMDD')
end-evaluate
end-while
The main take-away here is that you can use a date mask of 'D' to return the day number of the week. Sunday = 1, Saturday = 7, etc.