I'm having a weird problem. I'm trying to develop a report that contains several measures that come from different date ranges. In my WHERE clause, I'm encompassing the entire date range needed for my report. Most of the fields I'm asking to print require them to be COMPUTEs and then have subsequent IF statements to limit the date's selected.

Here's my code:

-SET &ECHO=ALL;
-SET &END_DAY = &YYMD;
-SET &YESTERDAY = AYMD(&YYMD,-1,'I8');
-SET &TODAY_MON = EDIT(&YYMD,'999999');
-SET &START_MONTH = AYM(&TODAY_MON,-1,'I6');
-SET &PM_START_DAY = EDIT(&START_MONTH,'999999')||'01';
-SET &TM_START_DAY = &TODAY_MON||'01';
-SET &LDLM = AYMD(&TM_START_DAY,-1,'I8');
-SET &TODAYS_DAY = EDIT(&YYMD,'$$$$$$99');
-SET &LM_END_DAY = &START_MONTH||&TODAYS_DAY;

TABLE FILE DW_PLANT_PERF_DAILY
SUM
     COMPUTE DB_PCT/D20.2 = IF EFF_DATE EQ DT(20080215) THEN ( STD_HOURS / EFFICIENCY_HOURS ) * 100; AS 'Day Before Yesterday'
     COMPUTE D_PCT/D20.2 = IF EFF_DATE EQ DT(20080216) THEN ( STD_HOURS / EFFICIENCY_HOURS ) * 100 ELSE 0; AS 'Yesterday'
     COMPUTE D_TRGT/D20.2 = IF EFF_DATE EQ DT(20080216) THEN AVE.TARGET_EFFICIENCY ELSE 0; AS 'Target'
     COMPUTE D_TREND/D20.2 = IF EFF_DATE EQ DT(20080216) THEN D_PCT - D_TRGT ELSE 0; AS 'Trend'
     COMPUTE PM_PCT/D20.2 = IF (EFF_DATE GE DT(&PM_START_DAY) AND EFF_DATE LE DT(&LM_END_DAY)) THEN (STD_HOURS / EFFICIENCY_HOURS) * 100; AS 'Prior Month'
     COMPUTE M_PCT/D20.2 = IF (EFF_DATE GE DT(&TM_START_DAY) AND EFF_DATE LE DT(&END_DAY)) THEN (STD_HOURS / EFFICIENCY_HOURS) * 100 ELSE 0; AS 'This Month'
     COMPUTE M_TRGT/D20.2 = IF (EFF_DATE GE DT(&TM_START_DAY) AND EFF_DATE LE DT(&END_DAY)) THEN AVE.TARGET_EFFICIENCY ELSE 0; AS 'Target'
     COMPUTE M_TREND/D20.2 = IF (EFF_DATE GE DT(&TM_START_DAY) AND EFF_DATE LE DT(&END_DAY)) THEN M_PCT - M_TRGT ELSE 0; AS 'Trend'
BY PLANT_NAME AS 'Plant'
BY DATE_GROUP NOPRINT
WHERE EFF_DATE GE DT(&PM_START_DAY) AND EFF_DATE LE DT(&END_DAY);
WHERE PLANT_NAME NE 'N/A';
WHERE DEPT_NO NE 'N/A';
WHERE WORKCENTER NE 'N/A';
WHERE PART_NUMBER NE 'N/A';
WHERE PLANT_NAME NE 'SERVICE';
WHERE TOTAL D_PCT NE 0;
WHERE TOTAL M_PCT NE 0;

When I run this, I get the following results:

Plant   Day Before Yesterday Yesterday Target Trend Prior Month This Month Target Trend 
------- -------------------- --------- ------ ----- ----------- ---------- ------ -----
ALABAMA 85.89                68.50     96.30  -27.80   1,662.17      68.50  96.30 -27.80 
FLORA   96.79                95.20     98.80  -3.60         .00      95.20  98.80  -3.60 
PARIS   87.98                81.71     97.60  -15.89        .00      81.71  97.60 -15.89 
SALEM   89.04                90.65     97.00  -6.35       97.61      90.65  97.00  -6.35 

I know from querying directly against the data that the Prior Month & This Month columns are wrong.

What am I doing wrong? Can WebFOCUS handle this kind of filtering?

Shane

Shane,

What kind of file/table is DW_PLANT_PERF_DAILY?

It appears that your date fields are in Date-Time format. In the file/table, do the values contain only dates or do they contain date-time? If they contain date-time, your equality tests (IF EFF_DATE EQ DT(20080215)) may not give you the expected results.

I have found that, for SQL Server tables, for example, if a date-time field is defined as a date field in a master, it will display as a date, but where statements need to take into account the time portion of the field, i.e. WHERE date-time-field GE '2007-01-01' will give expected results, WHERE date-time-field EQ '2007-01-01' will not.

Some suggestions, I know that mostly we know these things but sometimes we do not do them because we are so consumed by the error we miss doing what we normally do instinctively :-

Try changing your SUM to PRINT and remove your NOPRINT on the second BY field, so that you can see the data that is being used to compile the summary. If you put the output into EXL2K format you can easily add summations and other calculations quickly to help analyse the extracted data.

Reduce the output that you need to look through by selecting individual Plants where the value is known to be wrong. Identify what data is missing / extraneous and modify your selction criteria once you understand why the data is incorrect.

T

All:

The data is coming from an Oracle table in our Data Warehouse. The EFF_DATE field is an HYYMDS field. There's another date field in there with the same data that's an A10 field with a date format of YYYY-MM-DD. I'm going to try to use that field for my filtering.

Also, I tried TonyA's suggestion to do a PRINT instead of a SUM and for some reason, the only date that I'm pulling is 2008-02-16. Why would that be if in my WHERE clause I'm asking for 2008-01-01 to 2008-02-21?

Shane

If you have more than one record per plant and date group you could be summing the components of your calculations (STD_HOURS and EFFICIENCY_HOURS) before it is doing the calculation. Tony's suggestion to do a print will make that obvious. Also you do not have an ELSE condition on all of your computations. If EFF_DATE is a date-time stamp or not in yymd format you will need to convert it in a define first.

Shane,

Now you know why it's best to check the data extracted for the final report.

As for the "date" selection, is it the only selection you are making? If it is the only where clause, do you definitely have data for other dates?

You just have to go into your data and determine why the dearth of records being returned

T