Focal Point
[SOLVED] Need help with a date comparison

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/3501035872

March 31, 2008, 06:12 PM
webmeister
[SOLVED] Need help with a date comparison
In a FOCUS project I inherited, in the master file, I have a date field that has a format of P06YYM. I also have JCL that inputs a Year field of 4 digits. Currently, the FOCEXEC appends a two-digit month to the inputted year field, giving a date of, say, 200706.

What I want to do is to take the computed date field of 200706 and compute a prior date that is 16 months in the past. After I do that, I want to modify my FOCEXEC so that I can check to see if my datafield is between the date of 200706 and the date that is 16 months in the past.

I've not had any luck yet in writing correct code to do this....I've tried the AYM function and the CHGDAT function, but think I'm probably making this more difficult than it has to be.

Can anyone possibly help me out? Thank you so much in advance!

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


Mainframe FOCUS 7.0
VM/CMS and MVS/TSO
April 01, 2008, 02:57 AM
GamP
Well, I don't have any mainframe at hand currently, but I've tried to piece some things together using my webfocus 713 release.
Here's what I did, and with is succesfull for me:
SET BYDISPLAY=ON, EMPTYREPORT=ANSI
FILEDEF DATES DISK dates.ftm
FILEDEF MATES DISK dates.mas
-RUN
-WRITE MATES FILENAME=DATES, SUFFIX=FIX
-WRITE MATES SEGNAME=DATES
-WRITE MATES FIELDNAME=DATEFLD, FORMAT=P6YYM, ACTUAL=A6, $

-WRITE DATES 200812
-WRITE DATES 200811
-WRITE DATES 200810
-WRITE DATES 200809
-WRITE DATES 200808
-WRITE DATES 200807
-WRITE DATES 200806
-WRITE DATES 200805
-WRITE DATES 200804
-WRITE DATES 200803
-WRITE DATES 200802
-WRITE DATES 200801
-WRITE DATES 200712
-WRITE DATES 200711
-WRITE DATES 200710
-WRITE DATES 200709
-WRITE DATES 200708
-WRITE DATES 200707
-WRITE DATES 200706
-WRITE DATES 200705
-WRITE DATES 200704
-WRITE DATES 200703
-WRITE DATES 200702
-WRITE DATES 200701
-WRITE DATES 200612
-WRITE DATES 200611
-WRITE DATES 200610
-WRITE DATES 200609
-WRITE DATES 200608
-WRITE DATES 200607
-WRITE DATES 200606
-WRITE DATES 200605
-WRITE DATES 200604
-WRITE DATES 200603
-WRITE DATES 200602
-WRITE DATES 200601
-RUN
-SET &INPDATE = '2008';

-* Appending current month to input field
-SET &TESTDATE = &INPDATE | EDIT(&MDYY,'99');
DEFINE FILE DATES
TESTDATE/I6YYM = &TESTDATE;
PRIORDATE/I6YYM = AYM(TESTDATE,-16,PRIORDATE);
END

TABLE FILE DATES
HEADING
"&TESTDATE"
PRINT DATEFLD
      TESTDATE
	  PRIORDATE
WHERE DATEFLD GE PRIORDATE AND DATEFLD LE TESTDATE
END

Output is a list of all records that are within the selection period.

Hope this helps you get the job done ...


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
April 01, 2008, 08:37 AM
PBrightwell
Please notice in GamP's response that he used I6YYM as the format. Have you tried changing the P06YYM to I6YYM in a define and then doing the AYM function? I think that is your key.


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
April 01, 2008, 09:21 AM
webmeister
GamP and Pat,

Thaks for your replies and suggestions! I'll put them to work and hopefully come up with a working solution.

I appreciate you both for taking the time to help. Smiler


Mainframe FOCUS 7.0
VM/CMS and MVS/TSO
April 01, 2008, 09:36 AM
GinnyJakes
Webmeister,

Please be careful if the file you are reporting against is a relational DB. Comparing a data base field to a defined field will cause the date screen to not be passed to the data base so all the data will be returned and WebFOCUS will have to do the date screen.

Why couldn't you create your dates this way>
-SET &INPTYR='2007';
-SET &ENDYM=&INPTYR||'06';
-SET &BEGYM = AYM(&ENDYM,-16,'I6YYM');
-TYPE &ENDYM &BEGYM  

and then use these variables in your WHERE clause.

Am I missing something?


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
April 01, 2008, 10:53 AM
webmeister
Ginny,

No, you aren't missing something.... I was having difficulty wrapping my head around the date comparisons and the first two replies to my plight helped me solve my problem. I am not using relational databases.... these are VSAM files and the previous suggestions worked beautifully. I'm sure your suggestion would also work.

Thanks for replying!


Mainframe FOCUS 7.0
VM/CMS and MVS/TSO
April 01, 2008, 11:37 AM
GinnyJakes
You are most welcome. However, it is still more efficient to do WHERE clauses against literals as opposed to defined fields. That way the record is rejected before all the defines are applied. If you don't have very many defines then the discussion is moot. However, if you have a bunch of defines then the efficiency will become more apparent.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
April 01, 2008, 03:30 PM
webmeister
Thank you, Ginny,

I'll keep what you said about the efficiency aspect in mind for follow-on work.

I appreciate your comments Smiler


Mainframe FOCUS 7.0
VM/CMS and MVS/TSO