How do I pull records based on the day of the week and time it was entered. Specifically, I want to pull manual transactions made during 'after hours' for the previous day. After hours being after 4 p.m. weekdays and all day weekends.

Here is my code so far:
-SET &TODAY=EDIT(&YYMD, '99999999');
DEFINE FILE CONTACT
TODAY/I8YYMD=&TODAY;
DAY1/A8=DOWK(TODAY, DAY1);
TRANSDATE1/I8YYMD=GREGDT(CNDATE, 'I8YYMD');
TRANSDAY2/A8=DOWK(TRANSDATE1, DAY2);
END
TABLE FILE CONTACT
PRINT
CNCODE
CNDATE
CNTIME
CNINIT
DAY1
TRANSDAY2
WHERE (CNCODE EQ 'L') AND (CNINIT NE 'QLC');
WHERE date value ??????

This message has been edited. Last edited by: Kerry,

Can you post the formats for CNDATE and CNTIME please?

I'd like to see that code as well... But, without the code, cosider this:

...
WHERE TRANSDAY2 EQ 'SAT' OR 'SUN'
   OR CNTIME GT 1600
...

BTW: TRANSDAY2 cand be A3. We'd need the field formats, as Ginny asked, to ensure the correct usage in the WHERE statements.

Let's not forget about HOLIDAYS...

CNDATE was originally julian (2009238), but I have changed it to gregorian (2009/08/26).
CNTIME is A4 (1600).
And I only want the records for the previous day during after hours. Not all after hours for all days. That is why I am getting the current date in the set statment.

Sorry. I just noticed that I have a miskey in my code. DAY2 should be TRANSDAY2.

Still didn't get the format of CNDATE. I can't assume that it is YYMD. If it is not, you will have to retool the DM variable that you test with after you subtract 1 so that it looks like your date.

-SET &YESTERDAY=AYMD(&YYMD,-1,'I8YYMD');
TABLE FILE ...
...
WHERE CNDATE EQ &YESTERDAY AND CNTIME GT '1600';
END

For everything since 4pm the previous weekday,

-DEFAULT &TODAY= &YYMD.EVAL ;

-* date manipulation to obtain 4pm prior weekday:
-*  convert display date to relative day ("smart date"), 
-*  (WD+) move to next weekday if not a weekday, 
-*  (PWD) move to prior weekday, 
-*  convert back to display date, 
-*  append 1600=4pm

-SET &4PM=EDIT( DATECVT( DATEMOV( DATEMOV( DATECVT( &TODAY,'I8YYMD','YYMD' ), 'WD+' ), 'PWD' ), 'YYMD','I8YYMD' ), '999999991600' );

DEFINE FILE CONTACT
TIMESTAMP/A12= ['YYYYMMDDHHMM' value based on CNDATE and CNTIME];
END

TABLE FILE CONTACT
...
WHERE TIMESTAMP GT '&4PM';
...
END

You may need additional screening conditions -- for when the report is run during after-hours the same day, or during business hours the following weekday -- but those adjustments are straight-forward.

j.gross,
But I also need all day Saturday and Sunday. Scenario - extended hours dispatcher comes in Monday at 4 p.m. to begin shift. Wants to know all manual l calls done last extended hours shift, which would have been Friday 4 p.m. thru Monday 7 a.m. Same person on Tuesday at 4 p.m. needs data Monday 4 p.m. to Tuesday 7 a.m.

GinnyJakes,
No, CNDATE is not YYMD it's Julian - P7

So: If 'today' is a Monday, take 4pm Friday to 7am Monday.
(Same if today is Sat. or Sun., though part of the range will be in the future).
On other weekdays, 4pm yesterday to 7am today.

In short, the timespan is from 4pm on the weekday before "today", up to 7am of the weekday after that.

Here is it, on a silver platter:

-DEFAULT &TODAY=&YYMD.EVAL;

-SET &X0DATE=&TODAY;
-SET &X2DATE=   DATECVT( DATEMOV( DATECVT( &X0DATE,'I8YYMD','YYMD' ), 'WD+' ), 'YYMD','I8YYMD' );
-SET &X1DATE=   DATECVT( DATEMOV( DATECVT( &X2DATE,'I8YYMD','YYMD' ), 'PWD' ), 'YYMD','I8YYMD' );
-SET &X1TIME=&X2DATE | '1600' ;
-SET &X2TIME=&X2DATE | '0700' ;
     WHERE TIMESTAMP GE '&X1TIME';
     WHERE TIMESTAMP LT '&X2TIME';

As before, I use DATECVT with WD+ to adjust a weekend date forward to Monday; and then DATECVT with PWD to get the prior weekday.
(Note that PWD applied directly to a Saturday or Sunday yields the prior Thursday!)

Think I have a solution. I just put some flags in my where statement. Here's what I ended up doing:

WHERE (CNCODE EQ 'L') AND (CNINIT NE 'QLC') AND (EXTENED EQ 'Y');
-IF DAY1 EQ 'MON' GOTO WEEKEND;
-WEEKDAY
WHERE TRANSDT2 GE 4PM AND TRANSDT2 LE 7AM;
-GOTO THEEND
-WEEKEND
WHERE TRANSDT2 GE WEEKENDS AND TRANSDT2 LE 7AM;
-THEEND