-****************************************************************************************************** -* Rolling 12 months logic -****************************************************************************************************** -SET &QT = ''''; -SET &BEG_YR = DATECVT((DATEADD((DATECVT (&YYMD, 'I8YYMD', 'YYMD')),'M', -12)), 'YYMD','I8YYMD'); -SET &END_YR = DATECVT((DATEADD((DATECVT (&BEG_YR, 'I8YYMD', 'YYMD')),'M', 11)), 'YYMD','I8YYMD'); -SET &LSTBOM = DATECVT(DATEMOV(DATECVT(&BEG_YR, 'I8YYMD', 'YYMD'), 'BOM'),'YYMD', 'I8YYMD'); -SET &LSTEOM = DATECVT(DATEMOV(DATECVT(&END_YR, 'I8YYMD', 'YYMD'), 'EOM'),'YYMD', 'I8YYMD'); -SET &FROMDATE = EDIT(&LSTBOM,'$$$$99/') | EDIT(&LSTBOM,'$$$$$$99/') | EDIT(&LSTBOM,'9999'); -SET &TODATE = EDIT(&LSTEOM,'$$$$99/') | EDIT(&LSTEOM,'$$$$$$99/') | EDIT(&LSTEOM,'9999'); -SET &PGM_MO = EDIT(&LSTBOM,'$$$$99'); -SET &PGM_YY = EDIT(&LSTBOM,'9999'); -SET &XPGM_YY = &PGM_YY + 1; -SET &CNTR_MO = &PGM_MO; -SET &COLS = 'COLUMNS '; -SET &XCNTR = 1; -REPEAT MO_LOOP3 12 TIMES -SET &MO_NAME = DECODE &CNTR_MO ( - 01 'Jan' - 02 'Feb' - 03 'Mar' - 04 'Apr' - 05 'May' - 06 'Jun' - 07 'Jul' - 08 'Aug' - 09 'Sep' - 10 'Oct' - 11 'Nov' - 12 'Dec' ELSE 'XXX'); -SET &AND = IF &XCNTR EQ 12 THEN ' ' ELSE ' AND '; -SET &COLS = &COLS | &QT | &MO_NAME | ' ' | &PGM_YY | &QT | ∧ -SET &XCNTR = &XCNTR + 1; -SET &CNTR_MO = IF &CNTR_MO EQ 12 THEN 1 ELSE &CNTR_MO + 1; -SET &CNTR_MO = IF &CNTR_MO LE 9 THEN '0' | &CNTR_MO ELSE &CNTR_MO; -SET &PGM_YY = IF &CNTR_MO LT &PGM_MO THEN &XPGM_YY ELSE &PGM_YY; -MO_LOOP3 -TYPE &COLS -TYPE &BEG_YR TO &END_YR -TYPE &LSTBOM TO &LSTEOM -TYPE &FROMDATE TO &TODATE Then, in the program, SQL SQLMSS SET SERVER XXXXXXXXXX SQL SQLMSS PREPARE SQLOUTX FOR SELECT a.COLUMN_NAME1, a.COLUMN_NAME2, a.COLUMN_NAME3, b.DATE_COLUMN, datename(month, b.DATE_COLUMN) as emonth FROM DATABASE.dbo.TABLENAME a JOIN DATABASE.dbo.TABLENAME b ON a.COLUMN_NAME = b.COLUMN_NAME ; END -RUN ?FF SQLOUTX -RUN -DO_RPT3 -****************************************************************************************************** DEFINE FILE SQLOUTX MON/A3 = EDIT(emonth, '999'); XDATE1/YYMD = HDATE(DATE_COLUMN,'YYMD'); XDATE2/I8YYMD = XDATE1; XDATE3/A8 = EDIT(XDATE2); MO_CCYY/A8 = MON | ' ' | EDIT(XDATE3,'9999'); END TABLE FILE SQLOUTX SUM COLUMN_NAME1 COLUMN_NAME2 COLUMN_NAME3 ACROSS MO_CCYY AS '' &COLS etc.
-* File dgraff.fex DEFINE FILE CAR PROD/DMYY= 63* 365 + LENGTH * 10 + HEIGHT * 10 + WIDTH * 10; MPROD/MtYY=PROD; M/M=MPROD; END TABLE FILE CAR SUM SALES ACROSS M NOPRINT ACROSS MPROD WHERE MPROD FROM '07/1971' TO '06/1972' END
DEFINE FILE XXX YEAR/YY=YOURDATE; MONTH=tM/YOURDATE; YEARMND/YYtM=YOURDATE; END TABLE FILE XXX SUM whatever BY xxxx ACROSS HIGHEST YEAR NOPRINT ACROSS MONTH NOPRINT ACROSS YEARMND END
Frank | |
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7 |