I am interested in building a table of historical data for the purposes of trending, I have a table of data that contains gifts and pledges stored by fiscal year and giving date. currently i am running programs that on a given day give me the current years data through today and the previous years data through the calendar same day. I count distinct ID's, distinct gifts, sum the gifts and then do a compute for the AVG.

I am going to show my code below for one line of the 15 different pieces of data that I look at. I hope that you can see how I am doing what i have up to this point. I would like to expand this but the code becomes rather cumbersome and I am looking for a more efficient way, something that would loop for 365 days(don't forget leap year) and loop for 10 years, any help is appreciated, I am not a SQL wizard so WF solutions are preferred.

-* fex to set current year , last year, current date and current date a year ago today
-* MODIFIED TO ADD ADDITIONAL YEAR DATA
-* for passing to other FEXES for Friday Report caster report, EOM BOT in VSE format and EOM BOT G&C format
-* based ofn values from ATVFISC which has beginning andending dates for fiscal year
-* Generates paramters that are then passe to approximately 30 other rreports when then are merged with More
-* 2 sets of data one for current Year and on for a year ago today
-* MODIFIED DATES BECAUSE NEW VIEWS do NOT HAVE MONTH SPELLED OUT

DEFINE FILE ATVFISC
SYDATE/YYMD=&YYMD;
-*SYDATE/YYMD=20170630;

-*SYDATEPY/YYMD=DATEADD(SYDATE, 'D', -365);
-*SYDATELY/YYMD=DATEADD(SYDATE, 'D', -366);
SYFISCLY/P5=ATVFISC.ATVFISC.ATVFISC_CODE - 1;
SYFISCLY1/P5=ATVFISC.ATVFISC.ATVFISC_CODE - 2;

END
TABLE FILE ATVFISC
PRINT
ATVFISC.ATVFISC.ATVFISC_CODE


ATVFISC.ATVFISC.SYFISCLY
ATVFISC.ATVFISC.SYFISCLY1
WHERE ( SYDATE GE ATVFISC.ATVFISC.ATVFISC_START_DATE ) AND ( SYDATE LE ATVFISC.ATVFISC.ATVFISC_END_DATE);
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLD_FY FORMAT ALPHA
END
TABLE FILE AGBGIFT
SUM
AGBGIFT.AGBGIFT.AGBGIFT_GIFT_DATE/YYMD AS 'DATE1'
AGBGIFT.AGBGIFT.AGBGIFT_GIFT_DATE/M AS 'MONTH1'
COMPUTE LY_GIFT_DATE/YYMD = DATEADD(AGBGIFT.AGBGIFT.AGBGIFT_GIFT_DATE, 'M', -12); AS 'DATE2'
COMPUTE LY_GIFT_DATE1/YYMD = DATEADD(AGBGIFT.AGBGIFT.AGBGIFT_GIFT_DATE, 'M', -24); AS 'DATE3'
BY HIGHEST 1 AGBGIFT.AGBGIFT.AGBGIFT_GIFT_DATE NOPRINT
WHERE AGBGIFT.AGBGIFT.AGBGIFT_GIFT_DATE LE '&YYMD';


ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS HOLD_YR FORMAT ALPHA
-*IF READLIMIT EQ 5
END
-RUN
-READ HOLD_YR &DATE1.A8. &MONTH1.A2. &DATE2.A8. &DATE3.A8.
-READ HOLD_FY &ATVFISC_CODE.A5. &SYFISCLY.A5. &SYFISCLY1.A5.
-SET &MONTH1X = DECODE &MONTH1('01' 'JAN' '02' 'FEB' '03' 'MAR' '04' 'APR' '05' 'MAY' '06' 'JUN' '07' 'JUL' '08' 'AUG' '09' 'SEP' '10' 'OCT' '11' 'NOV' '12' 'DEC' );
-SET &DATE1X = &DATE1;
-SET &DATE2X = &DATE2;
-SET &DATE3X = &DATE3;
-SET &FISCYRX = &ATVFISC_CODE;
-SET &FISPYRX = &SYFISCLY;
-SET &FISPYRX1 = &SYFISCLY1;


-*TYPE &DATE1X
-*TYPE &DATE2X
-*TYPE &DATE3X

-*TYPE &FISCYRX
-*TYPE &FISPYRX
-*TYPE &FISPYRX1


-*EXIT


-*component_type report
-INCLUDE IBFS:/WFC/Repository/University_Advancement/universityad/AutoYR_DT.fex
SET NODATA = .00
SET ASNAMES = ON
JOIN
LEFT_OUTER FILE AWVFRG1 AT AWVFRG1.AWVFRG1.AGBGIFT_PIDM TO UNIQUE FILE APRCATG
AT APRCATG.APRCATG.APRCATG_PIDM TAG J0 AS J0
WHERE AWVFRG1.AWVFRG1.AGBGIFT_PIDM EQ J0.APRCATG.APRCATG_PIDM;
END
DEFINE FILE AWVFRG1
ROWNM/I2=31;
SUBNAME/A50='Gifts and Pledge Payments Received';
END
TABLE FILE AWVFRG1
SUM
AWVFRG1.AWVFRG1.AGRGDES_AMT AS 'AGRGDES_AMT_CY'
AWVFRG1.AWVFRG1.CURRENT_RESTRICTED_AMT AS 'RESTRICTED_AMT_CY'
AWVFRG1.AWVFRG1.ENDOWMENT_FUNDS_AMT AS 'ENDOWMENT_AMT_CY'
AWVFRG1.AWVFRG1.UNRESTRICTED_AMT AS 'UNRESTRICTED_AMT_CY'
BY LOWEST ROWNM
BY LOWEST SUBNAME
WHERE ( AWVFRG1.AWVFRG1.AGBGIFT_FISC_CODE EQ '&FISCYRX' ) AND ( AWVFRG1.AWVFRG1.AGBGIFT_GIFT_DATE LE '&DATE1X' ) AND ( AWVFRG1.AWVFRG1.AGBGIFT_PIDM NE 577167 ) AND ( AWVFRG1.AWVFRG1.GIFT_GROUP_DESC NE 'BEQ PROP' OR 'BEQUEST' OR 'CONT/GRANT' ) AND ( J0.APRCATG.APRCATG_DONR_CODE EQ 'ALCM' OR 'ALUG' OR 'ALND' OR 'ALSF' );
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS H_GAP_CY
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/PRDODSP/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END
JOIN
LEFT_OUTER FILE AWVFRG1 AT AWVFRG1.AWVFRG1.AGBGIFT_PIDM TO UNIQUE FILE APRCATG
AT APRCATG.APRCATG.APRCATG_PIDM TAG J0 AS J0
WHERE AWVFRG1.AWVFRG1.AGBGIFT_PIDM EQ J0.APRCATG.APRCATG_PIDM;
END
DEFINE FILE AWVFRG1
ROWNM/I2=31;
SUBNAME/A50='Gifts and Pledge Payments Received';
END
TABLE FILE AWVFRG1
SUM
AWVFRG1.AWVFRG1.AGRGDES_AMT AS 'AGRGDES_AMT_LY'
AWVFRG1.AWVFRG1.CURRENT_RESTRICTED_AMT AS 'RESTRICTED_AMT_LY'
AWVFRG1.AWVFRG1.ENDOWMENT_FUNDS_AMT AS 'ENDOWMENT_AMT_LY'
AWVFRG1.AWVFRG1.UNRESTRICTED_AMT AS 'UNRESTRICTED_AMT_LY'
BY LOWEST ROWNM
BY LOWEST SUBNAME
WHERE ( AWVFRG1.AWVFRG1.AGBGIFT_FISC_CODE EQ '&FISPYRX' ) AND ( AWVFRG1.AWVFRG1.AGBGIFT_GIFT_DATE LE '&DATE2X' ) AND ( AWVFRG1.AWVFRG1.AGBGIFT_PIDM NE 577167 ) AND ( AWVFRG1.AWVFRG1.GIFT_GROUP_DESC NE 'BEQ PROP' OR 'BEQUEST' OR 'CONT/GRANT' ) AND ( J0.APRCATG.APRCATG_DONR_CODE EQ 'ALCM' OR 'ALUG' OR 'ALND' OR 'ALSF' );
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS H_GAP_LY
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/PRDODSP/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END
MATCH FILE H_GAP_CY
SUM H_GAP_CY.H_GAP_CY.AGRGDES_AMT_CY
BY H_GAP_CY.H_GAP_CY.ROWNM
BY H_GAP_CY.H_GAP_CY.SUBNAME
RUN
FILE H_GAP_LY
SUM H_GAP_LY.H_GAP_LY.AGRGDES_AMT_LY
BY H_GAP_LY.H_GAP_LY.ROWNM
BY H_GAP_LY.H_GAP_LY.SUBNAME
AFTER MATCH HOLD OLD-OR-NEW
END
TABLE FILE HOLD
PRINT
HOLD.HOLD.ROWNM
HOLD.HOLD.SUBNAME
HOLD.HOLD.AGRGDES_AMT_CY
HOLD.HOLD.AGRGDES_AMT_LY
ON TABLE NOTOTAL
ON TABLE HOLD AS H_GAP_UG
END
SET NODATA = .00
SET ASNAMES = ON
JOIN
LEFT_OUTER FILE AWVFRG1 AT AWVFRG1.AWVFRG1.AGBGIFT_PIDM TO UNIQUE FILE APRCATG
AT APRCATG.APRCATG.APRCATG_PIDM TAG J0 AS J0
WHERE AWVFRG1.AWVFRG1.AGBGIFT_PIDM EQ J0.APRCATG.APRCATG_PIDM;
END
DEFINE FILE AWVFRG1
ROWNM/I2=35;
SUBNAME/A50='Gifts and Pledge Payments Received';
END
TABLE FILE AWVFRG1
SUM
AWVFRG1.AWVFRG1.AGRGDES_AMT AS 'AGRGDES_AMT_CY'
AWVFRG1.AWVFRG1.CURRENT_RESTRICTED_AMT AS 'RESTRICTED_AMT_CY'
AWVFRG1.AWVFRG1.ENDOWMENT_FUNDS_AMT AS 'ENDOWMENT_AMT_CY'
AWVFRG1.AWVFRG1.UNRESTRICTED_AMT AS 'UNRESTRICTED_AMT_CY'
BY LOWEST ROWNM
BY LOWEST SUBNAME
WHERE ( AWVFRG1.AWVFRG1.AGBGIFT_FISC_CODE EQ '&FISCYRX' ) AND ( AWVFRG1.AWVFRG1.AGBGIFT_GIFT_DATE LE '&DATE1X' ) AND ( AWVFRG1.AWVFRG1.AGBGIFT_PIDM NE 577167 ) AND ( AWVFRG1.AWVFRG1.GIFT_GROUP_DESC NE 'BEQ PROP' OR 'BEQUEST' OR 'CONT/GRANT' ) AND ( J0.APRCATG.APRCATG_DONR_CODE EQ 'ALCM' OR 'ALUG' OR 'ALND' OR 'ALSF' OR 'ALGR' );
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS H_GAP_CY2
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/PRDODSP/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END
JOIN
LEFT_OUTER FILE AWVFRG1 AT AWVFRG1.AWVFRG1.AGBGIFT_PIDM TO UNIQUE FILE APRCATG
AT APRCATG.APRCATG.APRCATG_PIDM TAG J0 AS J0
WHERE AWVFRG1.AWVFRG1.AGBGIFT_PIDM EQ J0.APRCATG.APRCATG_PIDM;
END
DEFINE FILE AWVFRG1
ROWNM/I2=35;
SUBNAME/A50='Gifts and Pledge Payments Received';
END
TABLE FILE AWVFRG1
SUM
AWVFRG1.AWVFRG1.AGRGDES_AMT AS 'AGRGDES_AMT_LY'
AWVFRG1.AWVFRG1.CURRENT_RESTRICTED_AMT AS 'RESTRICTED_AMT_LY'
AWVFRG1.AWVFRG1.ENDOWMENT_FUNDS_AMT AS 'ENDOWMENT_AMT_LY'
AWVFRG1.AWVFRG1.UNRESTRICTED_AMT AS 'UNRESTRICTED_AMT_LY'
BY LOWEST ROWNM
BY LOWEST SUBNAME
WHERE ( AWVFRG1.AWVFRG1.AGBGIFT_FISC_CODE EQ '&FISPYRX' ) AND ( AWVFRG1.AWVFRG1.AGBGIFT_GIFT_DATE LE '&DATE2X' ) AND ( AWVFRG1.AWVFRG1.AGBGIFT_PIDM NE 577167 ) AND ( AWVFRG1.AWVFRG1.GIFT_GROUP_DESC NE 'BEQ PROP' OR 'BEQUEST' OR 'CONT/GRANT' ) AND ( J0.APRCATG.APRCATG_DONR_CODE EQ 'ALCM' OR 'ALUG' OR 'ALND' OR 'ALSF' OR 'ALGR' );
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS H_GAP_LY2
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/PRDODSP/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END
MATCH FILE H_GAP_CY2
SUM H_GAP_CY2.H_GAP_CY.AGRGDES_AMT_CY
BY H_GAP_CY2.H_GAP_CY.ROWNM
BY H_GAP_CY2.H_GAP_CY.SUBNAME
RUN
FILE H_GAP_LY2
SUM H_GAP_LY2.H_GAP_LY.AGRGDES_AMT_LY
BY H_GAP_LY2.H_GAP_LY.ROWNM
BY H_GAP_LY2.H_GAP_LY.SUBNAME
AFTER MATCH HOLD OLD-OR-NEW
END
TABLE FILE HOLD
PRINT
HOLD.HOLD.ROWNM
HOLD.HOLD.SUBNAME
HOLD.HOLD.AGRGDES_AMT_CY
HOLD.HOLD.AGRGDES_AMT_LY
ON TABLE NOTOTAL
ON TABLE HOLD AS H_GAP_ALL
END

This message has been edited. Last edited by: FP Mod Chuck,

Hi Geoff

I suggest using dialog manager loop coding to handle the days and years. Looking at your code it is hard to tell I really can't suggest where this code would go but hopefully that suggestion will help.

Geoff,

do you really need to loop?
I this is possible to just do it in one query.

...but there is so much code to understand what you are trying to do.

TABLE FILE stuff
   SUM CNT.DST.GIFTS
       CNT.DST.PLEDGES
   BY  YEAR
   BY  MONTH
   BY DAY
ON TABLE HOLD AS H01
END

MATCH FILE H01
   SUM GIFTS AS GIFTS_CY
       PLEDGES AS PLEDGES_CY
   BY  MONTH
   BY  DAY
WHERE YEAR EQ &CY
RUN
FILE H01
   SUM GIFTS AS GIFTS_LY
       PLEDGES AS PLEDGES_LY
   BY  MONTH
   BY  DAY
WHERE YEAR EQ &LY
AFTER MATCH HOLD AS H02
END

This will give you for each month/day the data from CY and LY, without much code.

Thanks to Chuck and Dave for the suggestions, Chuck I'm not sure I understand yours but I am searching for looping on focal point so perhaps I will be able to leverage that.

Dave your solution I understand pretty readily except that I am not sure that I need month. I would assume that I would need a define/compute for that correct? However if I have the date in my hold file I can get that at run time right? Also I would only need to create this file once and then I can append data from that point on for each new day.

Re: the code I stated that I was looking for a better way and both these have possibilities

Oh.well..

I added MONTH because DAY isn't unique ( unless it's a 1-365 counter ).
I assumed "DAY OF THE MONTH".

You just cannot use "DATE" because they differ in CY and LY, because of the year component.

Just use whatever you need.

Geoff

I think Dave is right, I was overthinking this. Stick with simple if you can..