Selection Based on Day of the Week

By Hans Hendriks, Robelle Technical Support

Lynda Bechel from Meyer Distributing asked:

"Is there a way to pick dates that will include only the dates between the last day of the month ($date(*/*-1/last) and the prior Monday? This is for a report that looks at only the days from a Monday to the last day of the month for partial week. "

Well, yes there is. The trick is to realize that Julian Day 0 was a Monday. So if you convert any date to a Juliandays value (via Suprtool's "$days" function), and divide by 7, the modulus would tell you how many days that date is from the previous Monday.

So:

1. Get the date of the last day of the month
2. Convert that date to a juliandays value
3. Divide the juliandays by 7, the modulus shows how many days the date was beyond the previous Monday.
4. Deduct that number of days from the date in 1) (above), to calculate the date of the last Monday.

Here's the Suprtool code to insert at the beginning of the jobstream:

   purge dtfile
   purge dtfile2
   purge dtfile3
   input catalog.pub.sys     {any file with at least 1 record}
   def lastdate,1,8,display
   ite lastdate,date,yyyymmdd
   ext lastdate = $date(*/*-1/last) {output the date for last day of last month}
   num 1                     {only need 1 output record}
   out dtfile,link
   x
   in dtfile
   def lastmonday,1,4,int
   extract lastdate
   ext lastmonday = ($days(lastdate) - ($days(lastdate) mod 7))
                   {calculate juliandays value for the previous Monday}
   out dtfile2,link
   x
   in dtfile2
   extract 'setvar lastdayoflastmonth,', lastdate
         {create a "setvar" command for the last day of previous month}
   :file dtfile3;rec=-80
   out dtfile3,ascii
   num 2    {leave space for a second record}
   set squeeze off
   xeq
   in dtfile2 {create a "setvar" command for the previous Monday}
   item lastmonday,date,julianday
   extract 'setvar previousmonday,'
   ext lastmonday = $stddate(lastmonday)
   out dtfile3,ascii,append
   x
   use dtfile3

DTFILE3 now contains 2 setvar commands:

/l dtfile3
    1     setvar lastdayoflastmonth,20001231
    2     setvar previousmonday,  20001225

... and the file has been "use"d in Suprtool, so the variables have been set. They can then be referenced further down in the jobstream, as follows:

   >set varsub on
   >if mydate >= !previousmonday and mydate <= !lastdayoflastmonth
   >verify if
   IF mydate >= 20001225 and mydate <= 20001231
   >

Note that the variables will insert the actual numeric values into the IF command, so will make for efficient data selection. We could reduce the number of passes in the above script to generate an IF command directly, like:

    > if $days(mydate) <= {juliandays value of lastdayofmonth} &
    >    and $days(mydate) >= {juliandays value of previous monday}

... but this would mean that Suprtool would have to calculate the juliandays value for every record read at runtime, so would be substantially less efficient.

Hans.Hendriks@robelle.com
January 19, 2001