Hi,

I've seen this topic dense data or nodata and I get it, but what i'm planning to do is a bit differente.

I want to report all information between 2 dates, but the database doesn't have information for all days within those dates. So I would like to show days with no data as well on my graph. From what I understand from the topic above I would need to create a table with all dates needed, how can I do that?


Thanks in advance,
Pedro

WF 7702, WFDevTools 7702, Unix

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

Pedro, its called the McGyver technique, and its a Focus classic.
you make a file of all dates that you want
then join your data to it.
read up on mcGyver, there are articles on the TIps & Techniques pages.

As for creating the file on the fly to match the dates you want, just search (above) on "tony daterng" and look at the first link you get listed.

T

This might be simpler for you if you don't mind using ggsales sample table.

-DEFAULT &start_date  = '20120401';
-DEFAULT &num_of_days = 30;
 APP PATH IBISAMP
 TABLE FILE GGSALES
 SUM COMPUTE DATEYYMD/YYMD = IF LAST DATEYYMD EQ 0 
                             THEN '&start_date' 
                             ELSE LAST DATEYYMD + 1;
    BY SEQ_NO NOPRINT
 WHERE SEQ_NO LE &num_of_days
 ON TABLE HOLD AS DATELIST FORMAT FOCUS
 END

Pedro,
I have the original McGuyver document files:
McGuyver Techniques PDF
McGuyver still lives PPT

I can send them to you.

Danny, can you post links to these?

quote:

I have the original McGuyver document files

Shalom...

Doug,

I have them on my machine.
Give me an e-mail address and I'll send them to you.
Ah! I found it on your profile. On its way...
Shabbat Shalom,

Hi,

Thank you all for your replies and please forgive me for my late reply. I manage to create something similar to what Tony said, I think, as for the MacGyver technique I've seen it mentioned a lot of times but I haven't had the privilege to find it. So, @Danny if you can send me the files directly I would appreciate, see my profile for email address.

This is how I managed to solve my question:

-SET &begin_interval = '2012-03-01';
-SET &end_interval = '2012-03-31';
-SET &column_name = 'DATE_VALUE';

-*get all data available on my table and hold it

TABLE FILE TABLE_NAME
SUM *
WHERE &column_name FROM &begin_interval TO &end_interval
ON TABLE HOLD FORMAT FOCUS AS DATA_DAILY
END

-* I went to my databse to get all dates within my interval

ENGINE SQLORA SET DEFAULT_CONNECTION &defaultconn
SQL SQLORA PREPARE SQLALLDATE FOR
SELECT TO_CHAR(TO_DATE('&begin_interval.EVAL', 'YYYY-MM-DD') - 1 + rownum,'YYYYMMDD') AS &column_name
FROM all_objects
WHERE TO_DATE('&begin_interval.EVAL', 'YYYY-MM-DD') - 1 + rownum <= TO_DATE['&end_interval.EVAL', 'YYYY-MM-DD')
END

-*then printed everything as my dates table
TABLE FILE SQLALLDATE
PRINT &column_name
ON TABLE HOLD AS ALLDATE
END

-*then merge everything

MATCH FILE ALLDATE
PRINT &column_name
BY &column_name
RUN
FILE DATA_DAILY
PRINT *
BY &column_name
AFTER MATCH HOLD AS NEW_DATA_DAILY OLD
END

And that's it!

Once again, thank you.
Pedro

PS: Achtung!! DATE_VALUE from DATA_DAILY and from SQLALLDATE have to be exactly of the same type and format so that the merge can understand that are the same.