Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     create where "date" statement for limited output
Go
New
Search
Notify
Tools
Reply
  
create where "date" statement for limited output
 Login/Join
 
Master
posted
I am trying to create a report from a
fiscal year file to show only the data
for the months from the start of FY11
to the present.

(Present = today's date).

This is the synonym layout of my data:

?FF AGEDATA_11_A
-EXIT

FILENAME= AGEDATA_11_A
AGE AGE A60
_TYPE_ _TYPE_ D20.2
JUL10 jul10 D20.2
AUG10 aug10 D20.2
SEP10 sep10 D20.2
OCT10 oct10 D20.2
NOV10 nov10 D20.2
DEC10 dec10 D20.2
JAN11 jan11 D20.2
FEB11 feb11 D20.2
MAR11 mar11 D20.2
APR11 apr11 D20.2
MAY11 may11 D20.2
JUN11 jun11 D20.2

I'd like to create a date field which
I'd put into a WHERE statement
to print only the fields from July 2010
to the previous month (current_month - 1).

Anyone have any ideas?

Thanks, Cool


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 553 | Location: Baltimore, MD | Registered: July 06, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
Try the following:
-* FocalPoint:  create where "date" statement for limited output
-SET &TODAYIS = &YYMD ;
-* Test with different date values that are in the report as 'HIREDATE20'
-SET &TODAYIS = 20090329 ;
-SET &TODAYIS = 20090529 ;
-TYPE REPORT FROM BOQ TO &TODAYIS
-* -TYPEs are option
-* CONVERT TODAYS DATE FOR DATE MATH, IN TO: &CURDATE 
-SET &CURDATE   = DATECVT(&TODAYIS,'I8YYMD','YYMD');
-TYPE &CURDATE
-* Use &CURDATE to get the Beg Of Qtr (BOQ)
-SET &BEGTQTY0 = DATEMOV(&CURDATE, 'BOQ');
-TYPE &BEGTQTY0
-* Convert the BOQ to a useable date in the WHERE statement
-* Legend for variable, use the Capital letters: BEG This Quarter This Year
-SET &BEGTQTY  = DATECVT(&BEGTQTY0,'YYMD','A8YYMD');
-TYPE &BEGTQTY
DEFINE FILE EMPDATA
-* Convert the YY/MM/DD to an I8 for date calcs
HIREDATEI8/I8 = HIREDATE ; 
-* Add 20 YEARS to the HIREDATE to bring these dates current (just for testing)
HIREDATE20X/I8 = DATEADD(HIREDATEI8, 'Y', +20); 
HIREDATE20/I8  = DATECVT(HIREDATE20X,'YYMD','I8YYMD');
-* GET THE BOQ (BEGINNING OF THE QUARTER) FOR THE HIREDATE. (SEE WHAT QTR IT'S IN)
BEGTQTY0/I8 = DATEMOV(HIREDATEI8, 'BOQ');
-* CONVERT IT TO A YYYYMMDD FORMAT
BEGTQTY/I8  = DATECVT(BEGTQTY0,'YYMD','I8YYMD');
END
TABLE FILE EMPDATA
HEADING
"Report for hire dates from &BEGTQTY to &TODAYIS"
PRINT HIREDATE20 AS 'HireDate' FIRSTNAME AS 'First Name' LASTNAME AS 'Last Name'
-* Use the next line to verify without using the WHERE statement.
COMPUTE InThisQTR/A1 = IF HIREDATE20 FROM '&BEGTQTY' TO '&TODAYIS' THEN 'Y' ELSE '-' ; AS '?'
BY HIREDATE
-* Select only dates from BOQ to &TODAYIS (there's only one as of 10/22/2010)
-* Use the next line to limit these records.
-*WHERE HIREDATE20B FROM '&BEGTQTY' TO '&TODAYIS'
ON TABLE SET PAGE OFF
END


   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Most recent: 8204 Gen 48 in Test and Production.
 
Posts: 3045 | Location: Middle Tennessee [8204M Gen48 in Test&Prod] | Registered: February 23, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Something like this might work:

-SET &CURYRMTH = EDIT(&YYMD,'999999$$');
-SET &RPTYRMTH = AYM(&CURYRMTH,-1,'I6YYM');
-SET &CURYEAR  = EDIT(&YYMD,'9999$$$$');
-SET &CURMONTH = EDIT(&YYMD,'$$$$99$$');
-SET &FISCALYR = IF (&CURMONTH LE 6) THEN (&CURYEAR - 1) ELSE &CURYEAR ;
-SET &COLYRMTH = &FISCALYR | '07';
-*
TABLE FILE AGEDATA_11_A
 PRINT
-*
-REPEAT :ENDREPEAT1 13 TIMES
-SET &COLNAME = EDIT(CHGDAT('YYM','MTY',&COLYRMTH,'A17'),'999$99');
 &COLNAME.EVAL
-SET &COLYRMTH = AYM(&COLYRMTH,1,'I6YYM');
-IF (&COLYRMTH GT &RPTYRMTH) GOTO :EXITREPEAT1 ;
-:ENDREPEAT1
-:EXITREPEAT1
-*
END


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
Thanks Dan... We're always open to alternative methods... Now, it's up to Tomsweb to pick one, or some of each merged in with his own expertise...
 
Posts: 3045 | Location: Middle Tennessee [8204M Gen48 in Test&Prod] | Registered: February 23, 2005Reply With QuoteReport This Post
Master
posted Hide Post
Dan,

I like your method, but in this case I need to sort by the first 2 fields in the master file, i.e.,

quote:

TABLE FILE AGEDATA_11_A
PRINT
JUL10
AUG10
BY SEX
BY _TYPE_
ETC.
END



The confounding aspect of this is that I cannot be that I will have just one sort field or multiple sort fields in other requests which use this data.

I have added the sort fieds like so:

quote:

TABLE FILE AGEDATA_11_A
PRINT
-*
-REPEAT :ENDREPEAT1 13 TIMES
-SET &COLNAME = EDIT(CHGDAT('YYM','MTY',&COLYRMTH,'A17'),'999$99');
&COLNAME.EVAL
-SET &COLYRMTH = AYM(&COLYRMTH,1,'I6YYM');
-IF (&COLYRMTH GT &RPTYRMTH) GOTO :EXITREPEAT1 ;
-:ENDREPEAT1
-:EXITREPEAT1
-*
BY SEX
BY _TYPE_
END


Thanks,
Tom


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 553 | Location: Baltimore, MD | Registered: July 06, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     create where "date" statement for limited output

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.