Challenge: running a report for last week

I'm looking to run a report for last Monday to last Sunday. I'm thinking I'll determine this week's number, subtract one, determine the Beginning of Week of that week, then determine the End of Week of that week. Is this the proper approach and how would I write such code?

Into the Memory Hole!

Posts: 9 | Registered: March 16, 2007

TexasStingray

Master

posted

Hide Post

It depends on how the date you are going to create a where statement against is stored in the database does it store a week number or an actual date?

Scott

Posts: 865 | Registered: May 24, 2004

Ignored post by TexasStingray posted

Show Post

Glenda

Guru

posted

Hide Post

If your data contains an actual date:

-SET &TODAY = &YYMD; -*THE FORMAT OF YOUR DATES MAY BE DIFFERENT;
-SET &FROMDT = &TODAY - 11; -*TODAY IS FRIDAY 7/13 SO LAST MONDAY IS 11 DAYS AGO;
-SET &THRUDT = &TODAY - 5; -*TODAY IS FRIDAY 7/13 SO LAST SUNDAY IS 5 DAYS AGO;
UNIX echo &FROMDT
UNIX echo &THRUDT
TABLE FILE FILENAME
PRINT
*
WHERE DATEFIELD EQ &FROMDT
WHERE DATEFIELD EQ &THRUDT
END

Glenda

In FOCUS Since 1990
Production 8.2 Windows

Posts: 301 | Location: Galveston, Texas | Registered: July 07, 2004

Ignored post by Glenda posted

Show Post

FrankDutch

Virtuoso

posted

Hide Post

When you run the report let's say on Monday there are several ways to do this

but an easy way is to create a start and end date based on the actual day (from today-7 till today-1)

Then run the report that says

WHERE DAY FROM &FIRSTDAY TO &LASTDAY;

	*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

Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006

Ignored post by FrankDutch posted

Show Post

susannah

Expert

posted

Hide Post

-SET &dowk      = DOWK(&YYMD,'A3');
-SET &adj       = DECODE &dowk( 'SUN' 0 'MON' 1 'TUE' 3 'WED' 4 'THU' 5 'FRI' 6 'SAT' 7);
-SET &adj       = (-1) * &adj ;
-SET &LASTDAY   = AYMD( &YYMD    , &adj , 'I8YYMD');
-SET &FIRSTDAY  = AYMD( &LASTDAY , -6   , 'I8YYMD');

In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID

Posts: 3811 | Location: Manhattan | Registered: October 28, 2003

Ignored post by susannah posted

Show Post

jgelona

Master

posted

Hide Post

Try this. No matter what day you run the report, begin and end dates will get you dates for the previous 2 Sundays.

-SET &DAYS=IF &DATEW EQ 7 THEN 0 ELSE 0-&DATEW;
-SET &ENDDT=AYMD(&YYMD,&DAYS,'I8');
-SET &BEGDT=AYMD(&ENDDT,-7,'I8');
-? &

Then your where statement is

WHERE TESTDT GE '&BEGDT' AND TESTDT LT '&ENDDT'

The reason for the LT '&ENDDT' is because many databases (ours being Oracle), date fields are really datetime fields. If you define a datetime field as YYMD,DATE in the .mas and use LE with a date, you won't get any dates after midnight if the WHERE clause is passed to the database. For example, if you used &ENDDT as 20070714, then all the dates for Saturday fail the LE '20070714' test if they have any time except midnight (00:00:00.00000).

Now if your .mas has the dates defined as HYYMDS (datetime), the WHERE clause becomes:

WHERE TESTDT GE HINPUT(8,'&BEGDT',8, 'HYYMDS')
AND TESTDT LT HINPUT(8,'&ENDDT',8, 'HYYMDS')

The problem with this is that because of the use of the HINPUT function, the WHERE clause does not get passed to the data base. To avoid this I usually find it usefull to have a single database date fields defined as 2 fields in the .mas, one as smart date and the other as datetime.

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

In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.

Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006

Ignored post by jgelona posted

Show Post

Doug

Expert

posted

Hide Post

Thanks... It's always nice to have place to come to thaw out your brain when you have some brain-freeze... This has been around since Jul 16 2007 and is still as good as ever...

Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005

Ignored post by Doug posted

Show Post

Wep5622

Virtuoso

posted

Hide Post

quote:
Originally posted by WinstonSmith6076:
I'm looking to run a report for last Monday to last Sunday. I'm thinking I'll determine this week's number, subtract one, determine the Beginning of Week of that week, then determine the End of Week of that week. Is this the proper approach and how would I write such code?

And what if you'd do that in week 1?
ALWAYS use date arithmetic functions to modify dates.

In this case you could use DATEADD or HADD (depending on the type of date) to add -(DOWK + 7 -1) days.

WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :

Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010

Ignored post by Wep5622 posted

Show Post

j.gross

Virtuoso

posted

Hide Post

-SET &adj       = DECODE &dowk( 'SUN' 0 'MON' 1 'TUE' 3 'WED' 4 'THU' 5 'FRI' 6 'SAT' 7);

should be

-SET &adj       = DECODE &dowk( 'SUN' 0 'MON' 1 'TUE' 2 'WED' 3 'THU' 4 'FRI' 5 'SAT' 6);

This message has been edited. Last edited by: j.gross,

Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005

Ignored post by j.gross posted

Show Post

Joni Campbell - Europa Sports

Silver Member

posted

Hide Post

How about something like this:
COMPUTE TODAY_YYMD/YYMD = &YYMD;
COMPUTE BOW_THISWEEK/YYMD = DATEMOV(TODAY_YYMD, 'BOW');
COMPUTE LASTWK_MON/YYMD = BOW_THISWEEK - 7;

8009
Windows, HTML, AHTML, Excel
In FOCUS since 1983

Posts: 41 | Location: Charlotte, NC | Registered: January 06, 2012

Ignored post by Joni Campbell - Europa Sports posted

Show Post

susannah

Expert

posted

Hide Post

yep, but datemov doesn't seem to work on &vars, tho. alas.

In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID

Posts: 3811 | Location: Manhattan | Registered: October 28, 2003

Ignored post by susannah posted

Show Post

Alan B

Virtuoso

posted

Hide Post

quote:
yep, but datemov doesn't seem to work on &vars, tho. alas.

This is true, and documented. I have got into the habit now of using routines based around multiple date and date time functions. These seem to work well, so keeps me happy!

-SET &YYMD_BGN      = HCNVRT(HDTTM(DATEADD(DATEMOV(HDATE(HINPUT(8,&YYMD,8,'HYYMDs'),'YYMD'),'BOW'),'D',-7),8,'HYYMDS'),'(HYYMD-)',10,'A10') ;
-SET &YYMD_END      = HCNVRT(HDTTM(DATEADD(DATEMOV(HDATE(HINPUT(8,&YYMD,8,'HYYMDs'),'YYMD'),'BOW'),'D',-1),8,'HYYMDS'),'(HYYMD-)',10,'A10') ;
-TYPE &YYMD_BGN
-TYPE &YYMD_END
 
-SET &YYMDT_BGN      = HCNVRT(HDTTM(DATEADD(DATEMOV(HDATE(HINPUT(8,&YYMD,8,'HYYMDs'),'YYMD'),'BOW'),'D',-7),8,'HYYMDS'),'(HYYMD-s)',27,'A27') ;
-SET &YYMDT_END      = HCNVRT(HDTTM(DATEMOV(HDATE(HINPUT(8,&YYMD,8,'HYYMDs'),'YYMD'),'BOW'),8,'HYYMDS'),'(HYYMD-s)',27,'A27') ;
-TYPE &YYMDT_BGN
-TYPE &YYMDT_END

Using internal date formats allows Dialogue Manager to work okay.

Alan.
WF 7.705/8.007

Posts: 1451 | Location: Portugal | Registered: February 07, 2007

Ignored post by Alan B posted

Show Post

jgelona

Master

posted

Hide Post

After reading a post from Francis Mariani, I have created DEFINE FUNCTIONS to use DATEMOV, DATEADD and DATEDIF in Dialogue Manager.

See my post here:

Using DATEMOV, DATEADD and DATEDIF in DM

The only short coming is they accept DM dates in YYYYMMDD format. They could be modified to accept other formats but they suit our needs as they are.

In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.

Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006

Ignored post by jgelona posted

Show Post

susannah

Expert

posted

Hide Post

clever!

In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID

Posts: 3811 | Location: Manhattan | Registered: October 28, 2003

Ignored post by susannah posted