Greetings Ginny and all the rest of you smart folks out there! I have yet another challenge. The customer asked that the 'monthly' report, which I now have working very nicely thanks to your help, be subtotaled by 'weekly' intervals in a new Weekly Totals Report. They want the report to be exactly the same except instead of reporting by MONTH/YEAR, it will be reported by WEEK/MONTH/YEAR. I'm not able to use the MATCH logic as there are not exact matches for the full date as there were in MONTH/YEAR. Any suggestions would be grately appreciated! Here is the code of the existing MONTH/YEAR Report...

-* File Ginny2.fex
-SET &FROM_YRMTH_INPUT = '12/2007';
-SET &TO_YRMTH_INPUT = '02/2009';
-SET &FROM_YRMTH = EDIT('&FROM_YRMTH_INPUT.EVAL','$$$9999')|EDIT('&FROM_YRMTH_INPUT.EVAL','99');
-SET &TO_YRMTH = EDIT('&TO_YRMTH_INPUT.EVAL','$$$9999')|EDIT('&TO_YRMTH_INPUT.EVAL','99');
-SET &MnthCount = YM(&FROM_YRMTH,&TO_YRMTH,'I3') ;
-TYPE FROM_YRMTH &FROM_YRMTH
-TYPE TO_YRMTH &TO_YRMTH
-TYPE MnthCount &MnthCount

-* Create a temporary master for all the required months.
FILEDEF MOLISTMAS DISK monthlist.mas
-RUN
-WRITE MOLISTMAS FILENAME=MONTHLIST,SUFFIX=FIX
-WRITE MOLISTMAS SEGNAME=DATESEG,SEGTYPE=S0
-WRITE MOLISTMAS FIELDNAME=DATEYYM,,FORMAT=YYM,ACTUAL=A6,$

-* Load the temp file with all the months. To be used in the match later.
FILEDEF MONTHLIST DISK monthlist.ftm
-RUN
-REPEAT LOADLOOP FOR &Cntr FROM 0 TO &MnthCount ;
-SET &LdCnYrMth = AYM(&FROM_YRMTH, &Cntr , 'I6') ;
-WRITE MONTHLIST &LdCnYrMth
-TYPE MnthList &LdCnYrMth
-LOADLOOP

SET ASNAMES=ON

DEFINE FILE TBLBEREAVEMENTBURIAL
RPTDTCLSD/YYM = HDATE(DATECLOSED, 'YYMD');
RPTDTRCVD/YYM =
IF DATERECEIVED GT DT(18000101000000) THEN
HDATE(DATERECEIVED, 'YYMD')
ELSE
HDATE(DATEADDED, 'YYMD');
END

TABLE FILE TBLBEREAVEMENTBURIAL
COUNT
RPTDTCLSD AS DATECNT1
BY RPTDTCLSD AS DATEYYM
WHERE RPTDTCLSD FROM &FROM_YRMTH TO &TO_YRMTH
WHERE RPTDTRCVD LT RPTDTCLSD
ON TABLE HOLD AS LASTWKCO FORMAT ALPHA
END
-*-EXIT
-RUN
? HOLD LASTWKCO

TABLE FILE TBLBEREAVEMENTBURIAL
COUNT
RPTDTRCVD AS DATECNT2
BY RPTDTRCVD AS DATEYYM
WHERE RPTDTRCVD FROM &FROM_YRMTH TO &TO_YRMTH
ON TABLE HOLD AS NEWREFERRALS FORMAT ALPHA
END
-*-EXIT
-RUN
? HOLD NEWREFERRALS

TABLE FILE TBLBEREAVEMENTBURIAL
COUNT
RPTDTRCVD AS DATECNT3
BY RPTDTRCVD AS DATEYYM
WHERE RPTDTRCVD FROM &FROM_YRMTH TO &TO_YRMTH
WHERE DATECLOSED IS MISSING
ON TABLE HOLD AS NXTWKCO FORMAT ALPHA
END
-*-EXIT
-RUN
? HOLD NXTWKCO

MATCH FILE MONTHLIST
BY DATEYYM
RUN
FILE LASTWKCO
PRINT DATECNT1
BY DATEYYM
RUN
FILE NEWREFERRALS
PRINT DATECNT2
BY DATEYYM
RUN
FILE NXTWKCO
PRINT DATECNT3
BY DATEYYM
AFTER MATCH HOLD AS DATECOUNTS OLD-OR-NEW
END

TABLE FILE DATECOUNTS
PRINT DATECNT1 AS 'Last Week Carry Overs'
DATECNT2 AS 'New Referrals'
DATECNT3 AS 'Next Week Carry Overs'
BY DATEYYM AS 'Year / Month'
END


Thanks!
SamIam

Just define another field where you have used DATEMOV to convert DATECLOSED to a beginning of week (BOW) or end of week (EOW). Then add that as an additional sort. The trick, however, is getting it to look right when a week is split up by the end of a month.

sam
read this thread calculating weeks
the HPART function has some wierd wrinkles, but you can get a week number for each date in your original data pull..
Having said that tho, i'm now really liking Darin's idea of just defining a new field that takes every day and assigns it to a date that is beginnig of week..that gets around the problems with HPART...

say Sam, who do you work for?

Darin, that worked like a charm! Thank you very kindly!!

Suzannah, thanks for the HPART input. I'll put that in my notes! Also, I work for Harris County, Texas. Why do you ask?

Here is my final code for any newbies out there who are following along...

-* File weeklyreport.fex
-SET &FR_MDYY_INPUT = '01/01/2007';
-SET &TO_MDYY_INPUT = '02/01/2007';
-SET &FR_YYMD = EDIT('&FR_MDYY_INPUT.EVAL','$$$$$$9999')|EDIT('&FR_MDYY_INPUT.EVAL','99')|EDIT('&FR_MDYY_INPUT.EVAL','$$$99');
-SET &PREV_YYMD = &FR_YYMD;
-SET &TO_YYMD = EDIT('&TO_MDYY_INPUT.EVAL','$$$$$$9999')|EDIT('&TO_MDYY_INPUT.EVAL','99')|EDIT('&TO_MDYY_INPUT.EVAL','$$$99');
-SET &LdYYMD = &FR_YYMD;
-SET &MonthName = CHGDAT('YYMD', 'MX', &FR_YYMD, 'A15');
-SET &DSPL_FRDT = EDIT(&MonthName)|' '|EDIT('&FR_MDYY_INPUT.EVAL','$$$99')|' '|EDIT('&FR_MDYY_INPUT.EVAL','$$$$$$9999');
-SET &MonthName = CHGDAT('YYMD', 'MX', &TO_YYMD, 'A15');
-SET &DSPL_TODT = EDIT(&MonthName)|' '|EDIT('&TO_MDYY_INPUT.EVAL','$$$99')|' '|EDIT('&TO_MDYY_INPUT.EVAL','$$$$$$9999');
-SET &Cntr = 1;


-* Create a temporary master for all the required Weeks.
FILEDEF WKLISTMAS DISK weeklist.mas
-RUN
-WRITE WKLISTMAS FILENAME=WEEKLIST,SUFFIX=FIX
-WRITE WKLISTMAS SEGNAME=DATESEG,SEGTYPE=S0
-WRITE WKLISTMAS FIELDNAME=DATEYYMD,,FORMAT=YYMD,ACTUAL=A8,$

-* Load the temp file with all the Weeks. To be used in the match later.
FILEDEF WEEKLIST DISK weeklist.ftm
-RUN
-REPEAT LOADLOOP WHILE &LdYYMD LE &TO_YYMD;
-WRITE WEEKLIST &LdYYMD
-SET &LdYYMD = AYMD(&PREV_YYMD, 7, 'I8');
-TYPE To &LdYYMD
-SET &PREV_YYMD = &LdYYMD;
-LOADLOOP

SET ASNAMES=ON

DEFINE FILE TBLBEREAVEMENTBURIAL
RPTDTCLSD/YYMD =
IF DATECLOSED LT DT(00010101000000) THEN
'17990101'
ELSE
HDATE(DATECLOSED, 'YYMD');
RPTDTBURY/YYMD =
IF DATEBURIAL LT DT(00010101000000) THEN
'17990101'
ELSE
HDATE(DATEBURIAL, 'YYMD');
RPTDTRCVD/YYMD =
IF DATERECEIVED LT DT(00010101000000) THEN
HDATE(DATEADDED, 'YYMD')
ELSE
HDATE(DATERECEIVED, 'YYMD');
CNTR/I4 = 0;
END

TABLE FILE TBLBEREAVEMENTBURIAL
COUNT RPTDTBURY AS WKCOUNT1 AND
COMPUTE DATEYYMD/YYMD = DATEMOV(RPTDTBURY, 'BOW');
BY RPTDTBURY
WHERE RPTDTBURY FROM &FR_YYMD TO &TO_YYMD
WHERE CEMETERY = 'Harris County Cemetery'
WHERE TYPE = 'Adult Burial'
ON TABLE HOLD AS ADLTBURY FORMAT ALPHA
-*ON TABLE SUBTOTAL
END
-*-EXIT
-RUN
? HOLD ADLTBURY


TABLE FILE TBLBEREAVEMENTBURIAL
COUNT RPTDTBURY AS WKCOUNT2 AND
COMPUTE DATEYYMD/YYMD = DATEMOV(RPTDTBURY, 'BOW');
BY RPTDTBURY
WHERE RPTDTBURY FROM &FR_YYMD TO &TO_YYMD
WHERE CEMETERY = 'Harris County Cemetery'
WHERE TYPE = 'Baby Burial'
ON TABLE HOLD AS BABYBURY FORMAT ALPHA
-*ON TABLE SUBTOTAL
END
-*-EXIT
-RUN
? HOLD BABYBURY

MATCH FILE WEEKLIST
BY DATEYYMD
RUN
FILE ADLTBURY
PRINT WKCOUNT1
BY DATEYYMD
RUN
FILE BABYBURY
PRINT WKCOUNT2
BY DATEYYMD
AFTER MATCH HOLD AS DATECOUNTS OLD-OR-NEW
END

TABLE FILE DATECOUNTS
PRINT
WKCOUNT1 AS 'Adults
Buried'
WKCOUNT2 AS 'Babies
Buried'
COMPUTE 'Total Burials
at HC Cemetery'/D3 = WKCOUNT1 + WKCOUNT2;
BY DATEYYMD AS 'For The
Week Of'
HEADING
""
"Run Date: &DATEtrMDYY <+0> "
""
"Harris County Social Services"
"Bereavement Summary Report"
"&DSPL_FRDT To &DSPL_TODT"
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET STYLE *
UNITS=IN,
PAGESIZE='Legal',
SQUEEZE=ON,
ORIENTATION='Landscape',
WRAP=ON,
$
TYPE=HEADING,
LINE=2,
SIZE=9,
STYLE=NORMAL,
JUSTIFY=LEFT,
$
TYPE=HEADING,
LINE=2,
OBJECT=TEXT,
ITEM=1,
SIZE=9,
$
TYPE=HEADING,
LINE=4,
SIZE=11,
STYLE=NORMAL,
JUSTIFY=CENTER,
$
TYPE=HEADING,
LINE=4,
OBJECT=TEXT,
ITEM=1,
SIZE=11,
STYLE=BOLD,
$
TYPE=HEADING,
LINE=5,
SIZE=11,
STYLE=NORMAL,
JUSTIFY=CENTER,
$
TYPE=HEADING,
LINE=5,
OBJECT=TEXT,
ITEM=1,
SIZE=11,
STYLE=BOLD,
$
TYPE=HEADING,
LINE=6,
SIZE=11,
STYLE=NORMAL,
JUSTIFY=CENTER,
$
TYPE=HEADING,
LINE=6,
OBJECT=TEXT,
ITEM=1,
SIZE=11,
STYLE=BOLD,
$
TYPE=HEADING,
LINE=7,
SIZE=11,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=HEADING,
LINE=7,
OBJECT=TEXT,
ITEM=1,
SIZE=11,
$
TYPE=DATA,
FONT='CALIBRI',
SIZE=11,
JUSTIFY=CENTER,
$
TYPE=FOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=SUBHEAD,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
SIZE=9,
$
TYPE=ACROSSTITLE,
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
BACKCOLOR=RGB(210 210 210),
STYLE=BOLD,
$
ENDSTYLE
END

Thanks very much once again!!
Sam

sam, i ask because
TABLE FILE TBLBEREAVEMENTBURIALS
is a lot more colorful than
TABLE FILE CAR.

I agree Susannah...I was thinking funeral home.

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