In FOCUS since 1986 | WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2 | ||
WebFOCUS App Studio 8.2.06 standalone on Windows 10 |
declare @date_given datetime = '2020-04-17' select (DATEPART(wk,@date_given)-DATEPART(wk,dateadd(d,1-day(@date_given),@date_given)))+1
DEFINE FILE CAR MONTH_DATE/I2 = (DPART(&YYMD, 'WEEK', 'I11')-DPART(DATEADD(&YYMD, 'D', +1), 'WEEK', 'I11'))+1;
In FOCUS since 1986 | WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2 | ||
WebFOCUS App Studio 8.2.06 standalone on Windows 10 |
FILEDEF DATEMAS DISK DATERNG.MAS -RUN -WRITE DATEMAS FILE=DATERNG,SUFFIX=XFOC -WRITE DATEMAS SEGNAME=SEG1 -WRITE DATEMAS FIELD=DATE_KEY, ,DMYY ,DMYY , $ -RUN -* Now create it CREATE FILE DATERNG -* and add all possible dates within a month MODIFY FILE DATERNG FREEFORM DATE_KEY.A8. LOG FORMAT MSG OFF LOG TRANS MSG OFF LOG INVALID MSG OFF -* Any invalid dates will be rejected. -DEFAULT &Year = 2020 -SET &Year = EDIT(&YYMD,'9999$'); DATA -* This repeat loop will ensure at least a whole year is input, -SET &Month = 1; -SET &Day = 1; -REPEAT :Loop0 12 TIMES; -SET &Month = FPRINT(&Month, 'I2L', 'A2'); -REPEAT :Loop1 31 TIMES; -SET &Date = FPRINT(&Day, 'I2L', 'A2'); &Date&Month&Year -SET &Day = &Day + 1; -:Loop1 -SET &Day = 1; -SET &Month = &Month + 1; -:Loop0 END -RUN
-* Set the first day of week to Mondays SET WEEKFIRST = 2 TABLE FILE DATERNG SUM COMPUTE FSTOFMNTH/YYMD = DATEMOV(DATE_KEY, 'BOM'); NOPRINT COMPUTE FSTWEEK/I2 = DTPART(FSTOFMNTH, WEEK); NOPRINT COMPUTE CURWEEK/I2 = DTPART(DATE_KEY, WEEK); NOPRINT COMPUTE DAYOFWK/A3 = DOWK(DATECVT(DATE_KEY, 'YYMD', 'I8YYMD'), 'A3'); COMPUTE WEEKINMNTH/I2 = IF FSTWEEK GE 52 THEN CURWEEK - ((CURWEEK GE 52) * CURWEEK) + 1 ELSE CURWEEK - FSTWEEK + 1; BY DATE END -RUN
In FOCUS since 1986 | WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2 | ||
WebFOCUS App Studio 8.2.06 standalone on Windows 10 |
SET WEEKFIRST = 1 SET WEEKFIRST = ISO2 ... DEFINE FILE InputFile RPTDATE/A8MDYY = TRANSDATE ; ... -* Define MONTH... -* The day of week (DOW) gives us the column number but this is Sunday thru Saturday -* and we require Monday thru Sunday DOWX/W = TRANSDATE; DOWNMBR/W = IF DOWX = 7 THEN 1 ELSE DOWX + 1 ; DAYNAME/A9 = DECODE DOW (1 Sunday 2 Monday 3 Tuesday 4 Wednesday 5 Thursday 6 Friday 7 Saturday) ; ... END TABLE FILE InputFile SUM FIELD1 FIELD2 ... BY MONTH ACROSS DOWNMBR NOPORINT ACROSS DOWNAME AS 'Whatever' END ...
In FOCUS Since 1983 ~ from FOCUS to WebFOCUS. Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206 |
COMPUTE DT2/A20 = FPRINT(SFRSTCR_ADD_DATE, 'HYYMDS', 'A20'); COMPUTE MONTH_NUM2/A2 = SUBSTR(20, DT2, 6, 7, 2, 'A2'); COMPUTE MONTH2/A11 = DECODE MONTH_NUM2( '01' 'January' '02' 'Febuary' '03' 'March' '04' 'April' '05' 'May' '06' 'June' '07' 'July' '08' 'August' '09' 'September' '10' 'October' '11' 'November' ELSE 'December' ); NOPRINT COMPUTE ISODATE2/A10 = HYYWD(SFRSTCR_ADD_DATE, 'A10'); COMPUTE WEEK2/I2 = EDIT(SUBSTR(10, ISODATE2, 7, 9, 2, 'A2')); COMPUTE monthdate/I11 = DPART(AddDateYYMD, 'WEEK', 'I11'); COMPUTE BOMDT/HYYMD = DATEMOV(SFRSTCR.SFRSTCR.AddDateYYMD, 'BOM'); COMPUTE DT1/A20 = FPRINT(BOMDT, 'HYYMDS', 'A20'); COMPUTE MONTH_NUM1/A2 = SUBSTR(20, DT1, 6, 7, 2, 'A2'); COMPUTE MONTH1/A11 = DECODE MONTH_NUM1('01' 'January' '02' 'Febuary' '03' 'March' '04' 'April' '05' 'May' '06' 'June' '07' 'July' '08' 'August' '09' 'September' '10' 'October' '11' 'November' ELSE 'December'); NOPRINT COMPUTE ISODATE1/A10 = HYYWD(BOMDT, 'A10'); COMPUTE WEEK1/I2 = EDIT(SUBSTR(10, ISODATE1, 7, 9, 2, 'A2')); COMPUTE WEEKOFMONTH/I1=WEEK2-WEEK1+1;
COMPUTE DT2/A20 = FPRINT(SFRSTCR_ADD_DATE, 'HYYMDS', 'A20'); COMPUTE ISODATE2/A10 = HYYWD(SFRSTCR_ADD_DATE, 'A10'); COMPUTE WEEK2/I2 = EDIT(SUBSTR(10, ISODATE2, 7, 9, 2, 'A2')); COMPUTE BOMDT/HYYMD = DATEMOV(SFRSTCR.SFRSTCR.AddDateYYMD, 'BOM'); COMPUTE DT1/A20 = FPRINT(BOMDT, 'HYYMDS', 'A20'); COMPUTE ISODATE1/A10 = HYYWD(BOMDT, 'A10'); COMPUTE WEEK1/I2 = EDIT(SUBSTR(10, ISODATE1, 7, 9, 2, 'A2')); COMPUTE WEEKOFMONTH/I1=WEEK2-WEEK1+1;
quote:Am I missing something?
COMPUTE WEEKOFMNTH/I2 = IF WEEK1 GE 52 THEN WEEK2 - ((WEEK2 GE 52) * WEEK2) + 1 ELSE WEEK2 - WEEK1 + 1;
In FOCUS since 1986 | WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2 | ||
WebFOCUS App Studio 8.2.06 standalone on Windows 10 |
/* Can set the day that represents the first day of the week. */ /* Week Start Day 1=Mon 2=Tue 3=Wed 4=Thu 5=Fri 6=Sat 7=Sun */ -SET &Weekstart = 6; -SET &StartDate = &DATEYY | '0101'; -SET &EndDate = &DATEYY | '1231'; -SET &Date = &StartDate; -SET &i = 1; SQL CREATE TABLE calendar ( KEY INTEGER, DATEYYMD DATE ); END MODIFY FILE calendar MATCH KEY ON MATCH REJECT ON NOMATCH INCLUDE DATA -REPEAT ENDREPEAT WHILE &EndDate GE &Date ; &i,'&Date',$ -SET &i = &i + 1; -SET &Date = AYMD(&Date, 1, 'I8YYMD'); -ENDREPEAT END DEFINE FILE calendar DATEMYY/MYY = DATEYYMD; END TABLE FILE calendar PRINT COMPUTE DATEW/W = DATEYYMD; COMPUTE DATEWtr/Wtr = DATEYYMD; COMPUTE DATED/D = DATEYYMD; NOPRINT COMPUTE DATEBOMW/W = DATEMOV(DATEYYMD, 'BOM'); NOPRINT COMPUTE Week/I2 = ( DATED - 1 + MOD(DATEBOMW - &Weekstart + 7, 7) ) / 7 + 1 ; BY DATEMYY NOPRINT BY DATEYYMD ON DATEMYY PAGE-BREAK END