I know we've discussed Date_time fields before but I can't seem to make this work efficiently.

I have an Oracle table with a Date-Time field, as well as a numeric field containing the Year.

I'd like to efficiently SUM a measure by the Date portion of the Date-Time field with a selection on the Year field. I would like to have 365 rows as output.

Something like this:

TABLE FILE ORACLE_TABLE
SUM MEASURE
BY DATE_PART_OF_DATE_TIME_FIELD
WHERE YEAR_FIELD EQ 2003
END

Thanks.

This is probably a silly question, but do you use the time portion of your date/time fields in other reports?

If not, SET DATETIME OFF, and re-gen the masters. Makes life easier.

Regardless of the fact that WebFOCUS has a large number of date and time build-in functions the most convenient way of dealing with the date and time is to use the Time Dimension Technique. This technique is well described in the following article: It's Time for Time . The article assumes that you have the appropriate privileges to create the Time Dimension Table within a data source platform, which is not always true.

If you have the Time Dimension Table as the source platform table you can join it to the "fact tables" within the metadata layer of WebFOCUS (master and access files), and than conveniently use it.

There is a FOCUS variant of the technique:

• Create the Time Dimension Table as the FOCUS TABLE
  
• Fill it with the data for the appropriate time span (the past, and the future). The 10 years of history is just 3650 records in the table - not a very large amount.
  
• The FOCUS script (quite basic) for creating the table is included below.
  
• Within the reports: query the source platform (TABLE or SQL passthrough), hold the results, join the holded result to the Time Dimension, perform the final reporting from the prepared source. It is still more convenient than performing date calculations.
  
• The main benefit of the technique is using sophisticated date and time calculations (they have to be sophisticated, just because the "nature of the problem" to solve - date and time is very important, but not very easy dimension) only once - during the creation of the Time Dimension. After that you just using simple queries and joins.
  

Hope it is helpful

Merry Christmas and Happy New Year to You and the whole WebFOCUS Community !

Grzegorz

--------------------------------
-* Initialization: -----------------------------
-SET &ECHO=ALL;
SET ASNAMES=ON
SET HOLDLIST=PRINTONLY
APP HOLD
-* Configuration section: ----------------------------------------------
-* The time span of the Time Dimension:
-SET &DATE_BEGIN = '20020101';
-SET &DATE_END = '20091231';
-* The name of the Time Dimension table:
-SET &CALNAME = 'dim_cal';
-* Application where the Time Dimension will reside:
-SET &HOLDAPP = 'szkolenie';
-* The text date field separator:
-SET &DTSEP = '-';
-* The first day of the week (1 - Sunday, 2 - Monday, ..., 7 - Saturday):
SET WEEKFIRST=2
-*---------------------------------------------------------------------
-* Creating the empty "base" table: ---------
TABLE FILE SYSTABLE
SUM COMPUTE CALDT/YYMD = '&DATE_BEGIN'; AS 'CALDT'
BY NAME NOPRINT
WHERE 1 = 2
ON TABLE HOLD AS TMPDTID FORMAT FOCUS INDEX CALDT
END
-RUN
MAINTAIN FILE TMPDTID
COMPUTE DAY_BEGIN/YYMD = '&DATE_BEGIN';
COMPUTE DAY_END/YYMD = '&DATE_END';
COMPUTE CNT/I8=1;
INFER CALDT INTO STK_CAL;
REPEAT UNTIL DAY_BEGIN GT DAY_END
STK_CAL(CNT).CALDT = DAY_BEGIN;
ADD(DAY_BEGIN, 1);
COMPUTE CNT = CNT + 1;
ENDREPEAT
FOR ALL INCLUDE CALDT FROM STK_CAL;
END
-*---------------------------------------------------------------------
-* Generationg the Calendar: ------------------------------------------
-* (It is of course possible to add more fields). ---------------------

DEFINE FILE TMPDTID
-*- Defining the helper fields for computations: ---------
TMPID/A8YYMD = CALDT;
TMPYY/A4 = EDIT(TMPID, '9999$$$$');
TMPMM/A2 = EDIT(TMPID, '$$$$99$$');
TMPDD/A2 = EDIT(TMPID, '$$$$$$99');
CALDQ/Q = CALDT;
TMPQ/A2 = 'Q' || EDIT(CALDQ);
TMPH/HYYMD = HDTTM(CALDT, 8, TMPH);
WPART/I2 = HPART(TMPH, 'week', WPART);
TMPWK/A2 = EDIT(WPART);
TMPDOW1/W = CALDT;
TMPDOW2/I2 = TMPDOW1;
-*---------------------------------------------------------------------
END

APP HOLD &HOLDAPP
DROP SYNONYM &CALNAME
-RUN
TABLE FILE TMPDTID
SUM COMPUTE
-*-------------- Calculations of the Time Dimension fields: ---------------
-*-------------- (just the small set of the possibly useful fields) -------
CALDTID/A8 = TMPID;
CALDATE/YYMD = CALDT;
CALYYMD/A10 = TMPYY || '&DTSEP' || TMPMM ||
'&DTSEP' || TMPDD;
CALYYQ/A7 = TMPYY || '&DTSEP' || TMPQ;
CALYYM/A7 = TMPYY || '&DTSEP' || TMPMM;
CALMM/A2 = TMPMM;
CALYYW/A8 = TMPYY || '&DTSEP|W' || TMPWK;
CALDOWNR/I2 = TMPDOW1;
CALDOWNM/A12 = DECODE CALDOWNR (
1 'Monday'
2 'Tuesday'
3 'Wednesday'
4 'Thursday'
5 'Friday'
6 'Saturday'
7 'Sunday'
ELSE '???'
);
-*----------------------------------------------------------------------------------
BY CALDT NOPRINT
ON TABLE HOLD AS &CALNAME FORMAT FOCUS INDEX CALDTID CALDATE
END
APP HOLD
-RUN
-*----- The final test of the created Table: --------------------------------
TABLE FILE &CALNAME
ON TABLE SET PAGE-NUM OFF
HEADING CENTER
" File &CALNAME created successfully."
" Sample data: "
PRINT *
BY CALDTID NOPRINT
WHERE RECORDLIMIT EQ 100
ON TABLE SET STYLE *
TYPE=REPORT, FONT=ARIAL, SIZE=10, $
TYPE=TITLE, STYLE=BOLD,$
ENDSTYLE
END

Piipster and Grzegorz, thank you for your input.

Piipster, the OFF option is not mentioned in the documentation, Developing Reporting Applications
Version 5 Release 3, and it really doesn't tell you how the DATETME setting affects CREATE SYNONYM.

Parameter: DATETIME

Description: Sets time and date in reports and controls the format in which CREATE SYNONYM creates date-time columns in a Master File.

Syntax: SET DATETIME = option

where option is one of the following:

STARTUP is the time and date when you began your session. STARTUP is the default value.

CURRENT|NOW changes each time it is interrogated. For example, if your batch job starts before midnight at 11:59 P.M., it won�t complete until the next day. If DATETIME is set to NOW|CURRENT, any reference to the variable gives the current
date, not the date when the job started.

RESET freezes the date and time of the current run for the rest of the session or
until another SET DATETIME command is issued.

Grzegorz,

You have a very interesting suggestion which I will look into.

Thank you,

Merry Christmas and a Happy New Year.

Pietro.

For Oracle

SQL SQLORA SET DATETIME OFF

or if you are using an ODBC connection to ORACLE

SQL SQLODBC SET DATETIME OFF


If you use this setting and Create a synonym it will give you YYMD in the master files instead of HYYMMDS.

Then you don't have to worry about the timestamp portion of the datetime field.

I'm not sure if anything has changed for this in R532, but this should hold true for R52x