Focal Point
Query date field for non-existant date values

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/3181077432

June 29, 2007, 10:26 AM
mbedford
Query date field for non-existant date values
I have a client with a report that is suppose to indicate which training rooms are not booked on specific dates. How would you query for a date value that does not exist in a field?

Thank you,
Morgan
June 29, 2007, 10:36 AM
Prarie
WHERE DATEFIELD EQ ' ';


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
June 29, 2007, 10:52 AM
mbedford
I don't think that will work because I want to know what dates the room is not booked. These values don't exist in the field.
June 29, 2007, 11:09 AM
Leah
What is your data base? DB2 sets a default on date fields. Our Student system has a default date of 01/01/1001, fills the DB2 dates with that, I believe DB2 has a default date of 01/01/0001. So your data base might also, then you look for rooms with the 'default date in them'. Of course that only finds not booked at all.


Leah
June 29, 2007, 11:13 AM
ET
One approach to this problem is to create a file of the universe of the dates possible (business days maybe?). Join the date in this file to all the dates that currently have meetings and then test for your missing dates on the cross referenced (child) file.

Good luck.

et


FOCUS 7.6 MVS PDF,HTML,EXCEL
June 29, 2007, 11:14 AM
mbedford
Leah, it is an Oracle database.

Thanks,
Morgan
June 29, 2007, 11:22 AM
Darin Lee
Hard to give a specific answer without some sort of data layout, but here's something that may work. If you have a file with all rooms and join it to the file with room schedules using SET ALL=ON and then do a where test on the schedule file like WHERE SCHEDULE.ROOMNBR EQ MISSING and WHERE SCHEDULE.DATE EQ mm/dd/yyyy you should get a list of rooms that are NOT scheduled for the given data. I believe I recall seeing an example of this in the manual where it was showing all employees that had not attended a training class. It was talking about MISSING field values/segments.


Regards,

Darin


In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
June 29, 2007, 01:12 PM
Leah
For your oracle data base, does the software that sits on top of it have some default date used?


Leah
June 29, 2007, 01:31 PM
mbedford
Leah, I don't think there is a default date, but there has got to be a way to do this within WebFocus. I say that because you can do lots of date math without field values.

Thanks,
Morgan
June 29, 2007, 01:50 PM
Prarie
Would this work.

WHERE DATEFIELD IS-MISSING


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
June 29, 2007, 02:07 PM
Leah
Without knowing how the data is being stored, missing may or may not be what you need, however have you considered doing a quick check to get the minimum value in the date field in question? Might give you the default. What other parameters if any are you using?


Leah
July 02, 2007, 09:18 AM
jgelona
I agree with ET's approach.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
July 02, 2007, 03:50 PM
Chad
ET - I'm new at WebFocus - how would you create the file in WebFocus. Or, would I need to create the file on the server to read it??
July 02, 2007, 03:59 PM
Prarie
At the end of your Fex with the data you want you put:

ON TABLE HOLD AS WHATEVER FORMAT FOCUS

TABLE FILE CAR
PRINT CAR
BY COUNTRY
ON TABLE HOLD AS WHATEVER FORMAT FOCUS
END


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
July 02, 2007, 05:14 PM
FrankDutch
create a small text file with let's say the numbers 1 till 90 and save that file in your baseapp directory

now create a master to read that file (PERIOD.MAS)
The number field is "NUMBER"

DEFINE FILE PERIOD
DATEFIELD/YYMD=TODAY+NUMBER;
END

Now you can table that file and hold the data in a focus file and it has the sequence of the coming 3 months.
With some smart extra defines you can list only the working days.
Join the focus file to your table and do what Darin suggested.




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

July 03, 2007, 08:47 AM
ET
Here is an example to create a file of dates using the car file. You can use any file as long as it has more than the number of days you want to check and use a recordlimit to get the number of dates you need and hold this file.

If I understand your problem correctly you would then need to build a file that has each meeting room and all the possible dates.

room1 date1
room1 date2
...
room9 date1
etc.

This can be done by adding a dummy blank field to the hold file generated with all the possible dates. Then create a file containing each distinct meeting room and another dummy blank field. Join the blank in the meeting room file to all blank in the date file. Now create the above described hold file sorted in meeting room and date order.

Extract a file from your data source that has meeting room and dates used. Join the first file which has every meeting room and date combination possible to the second file that only has the rooms with meeting dates. Print the room/date from the first file where the room/date from the second file is missing should give you what you need.

Good luck

et



DEFINE FILE CAR
CNTR/I9 WITH CAR= LAST CNTR + 1;
STARTDT/MDY WITH CAR='070307';
ALLDATE/MDY=IF CNTR EQ 1 THEN STARTDT ELSE LAST ALLDATE + 1;
END
TABLEF FILE CAR
PRINT CAR ALLDATE
END
CAR ALLDATE
--- -------
JAGUAR 07/03/07
JENSEN 07/04/07
TRIUMPH 07/05/07
PEUGEOT 07/06/07
ALFA ROMEO 07/07/07
MASERATI 07/08/07
DATSUN 07/09/07
TOYOTA 07/10/07
AUDI 07/11/07
BMW 07/12/07


FOCUS 7.6 MVS PDF,HTML,EXCEL