I'm unclear on exactly what you need. It sounds like you either
1) want a field with a list of the unique dates for a given set of (230) found or related records, or
2) a field in second database table, possibly with a record for each date, in which you want to have a calculation showing whether at least one record in the first table exists for that date (i.e., "if there is one or more of this date in that table, the value = 1, otherwise blank), or
3. Something entirely different
You might try briandunning.com for some marvelous custom functions that may be useful to you, once it becomes clear what you need.
Is further explanation possible, or does this answer start you on a decent path?
See if this helps:
In version 12, you can use the ExecuteSQL() function to get the same result.
Message was edited by: Michael Horak
Thank you for looking into my issue.
Will get back to you in detail.
Thanks for guiding me to the right direction.
Please see the follwing very detail info. Hope you dont mind reading all those details.
EXAMPLE DB ATTACHED
Database consists of multiple records those belongs to each day.
Accession # Date Serial# Room # Procedure Exam
19766710 10/01/2012 2395050 2 Venous 10:40:00 12:00:00 80 19767288 10/01/2012 2519103 3 Venous 11:20:00 11:45:00 25 19768638 10/01/2012 2488371 3 Venous 13:45:00 14:30:00 45 19768497 10/01/2012 2112901 2 Venous 14:35:00 14:40:00 5 19768824 10/01/2012 1130715 3 Drainage 15:10:00 15:45:00 35 19769066 10/01/2012 2589623 1 Venous 16:00:00 17:00:00 60 19771385 10/01/2012 573330 3 Venous 11:00:00 12:30:00 90 19771473 10/02/2012 2590017 2 Venous 10:00:00 11:00:00 60 19771385 10/02/2012 573330 3 Venous 11:00:00 13:20:00 140 19772181 10/02/2012 1906027 2 Biopsy 12:00:00 12:15:00 15 19773249 10/02/2012 2366818 2 Venous 15:15:00 16:00:00 45 19775472 10/03/2012 658618 3 Biopsy 10:10:00 10:55:00 45 19776460 10/03/2012 3386423 3 Drainage 12:15:00 12:30:00 15 19776075 10/03/2012 2590303 1 Venous 10:00:00 12:00:00 120 19777068 10/03/2012 2260250 3 Venous 14:00:00 14:30:00 30 19777045 10/04/2012 503979 1 Venous 13:10:00 14:10:00 60 19781604 10/04/2012 1680390 2 Hemodialysis 14:20:00 15:50:00 90 19789799 10/04/2012 2558798 CT Drainage 14:00:00 14:50:00 50 19784251 10/05/2012 2575840 3 Tube 10:15:00 10:50:00 35 19785644 10/05/2012 2534519 3 Venous 13:30:00 14:15:00 45 19785101 10/05/2012 2511328 3 Venous 12:10:00 12:40:00 30 19784489 10/05/2012 134826 2 Hemodialysis 10:55:00 12:05:00 70 19784472 10/05/2012 2590270 1 Venous 10:15:00 13:30:00 195 19785752 10/05/2012 1377815 2 Tube 13:30:00 14:45:00 75 19786095 10/05/2012 434684 CT Drainage 15:00:00 15:30:00 30 19785102 10/05/2012 2511328 3 Venous 12:10:00 12:40:00 30 19784231 10/05/2012 25758413 3 Tube 10:15:00 10:50:00 35 19789979 10/07/2012 2315560 2 Venous 12:00:00 13:30:00 90 19795352 10/09/2012 2590405 1 Venous 14:15:00 15:30:00 75 19793993 10/09/2012 2567951 3 Venous 10:50:00 12:50:00 120
Room Availability for each day is 480 minutes.(8 hours)
What I am trying to accomplish here is:
Get a monthly Utilization Summary % for each room.
Example: (Using the above referenced records)
Room # 3 had 14 procedures performed from Oct 1 thru Oct 9 (Key: WHICH IS Oct 1, 2, 3, 4, 5 7, 9 (7 Days)
Room # 3 was available 480 minutes each day.
So the Total Room available minutes for Room#3 should be 480*7=3360 minutes
Room# 3 Utilized minutes from Oct 1 thru Oct 9 is: TAT Minutes (25+45+35+90+140+45+15+30+35+45+30+30+35+120 = 720 minutes)
Ultimate goal is to calculate the Room Utilization % using the following simple formula:
(Total Room Utilized Minutes /Total Room Available Minutes )* 100
For the period of Oct 1 thru Oct 9 2012 the Room Utilization % of Room# 3 should be as below:
Room#3 = (720/3360)*100 = 21.4%
THE REAL QUESTION IS:
How do I get the count of 7 from the total of 14 records for Room#3 for the Period of Oct 1 thru Oct 9?
Looking forward for your kind HELP.
Wonderful explanation, above. Here's what I'm seeing (tell me if I'm missing the point): Room 3 is utilized at least once on seven days during the 9-day period, Oct. 1 through Oct. 9. I <think> what's missing in the example calculations, however, is an extra 960 minutes of potentially utilized time that Room 3 was available on Oct. 6 and Oct. 8.
We don't have an indication that there are any days that Room 3 is not available; dates and time that should be excluded from our calculatioons.
Thus, if I understand, the Total Room Available Minutes would be 9 x 480 = 4,320.
Should the database display the ultimate goal of utilizatioin percentage, based on a starting date, end date and all utilization data records AND take into account days during that period for which a room was not used, as in your example?
THANK YOU so much for immediate response.....
My example records only reflects 9 days and my explanation was based on those recods count.....But I am planning to produce the report monthly.
I need to produce two reports one for weekdays and the other for weekends.
Also, I have to exclude holidays - in my example records, Oct 8th was a holiday and that's why there wasn't any exam for Oct 8th - and Oct 7 is Sunday which should exclude from my regular report.
So, for my WEEKDAYS report, the Total Available minutes for Room#3 should be: 6 x 480=2880 (eventhough no exam done in Room# 3 on Oct 4) (Total days open 7 minus 1 (Oct 7 Sunday) = 6
Room#3 = (720/2880)*100 = 25%
"Should the database display the ultimate goal of utilizatioin percentage, based on a starting date, end date and all utilization data records AND take into account days during that period for which a room was not used, as in your example?"
The answer is YES plus a couple of aggregates (average exam per day, Highest exam(count) peformed in a day and the lowest exam (count) performed in a day.
Hope I didn't confuse you.......
Thank you soooooo much for your help
Obviously, you need to calculate the available time separately, because it cannot be computed from your data alone. Seems like you should have a pair of global fields to hold the reported period's start and end dates, and a table of holidays. That way you can calculate the number of work days in the reported period, multiply it by 480 and use that as the denominator in your % calculation.
Not sure what this has to do with your original question.
OK, I think that makes it clearer — that a simple start/end date range is not enough, since the range of dates being reported includes "black-out" dates (holidays; Sunays) during which a room is unavailble AND, depending on the report, may include only weekdays or only weekend days. Yes, Michael, it might be possible (or better) to try this with ExecuteSQL() in FM 12, but I'm going to think it through "old school," as just a possibility.
Let's start with global text fields, room_number_g and datefilter_g. Create a relationship from the "parent" table containing these global fields to the LOG table containing the appointments. For example, call the new table occurrence "REP_LOG," where the predicates are: room_number_g = REP_LOG::room_number and datefilter_g = REP_LOG::exam_date.
Yes, datefilter_g is a text global, and yes, that means the predicates are not of the same type (text vs. date). But this still works, and here it is necessary, because we're going to fill datefilter_g with a return-delimited list of dates (in text format), such as
This is a "multi-key" field, meaning that any of these values would satisfy a match to exam_date in the relationship. The field can contain hundreds of date entries (enough at least for an annual report, though there is a finite limit and from a performance standpoint, this method is probably better for weekly/monthly reports). You can use whatever process you need to fill this field with dates, whether that be a looping script or custom function of some kind.
room_number_g will have the value "3" for example.
The Total Room Available Minutes would be a numeric calculation in the parent table: Valuecount( datefilter_g ) * 480 (which = 7 * 480 = 3,360).
The Total Room Utilized Minutes would be a numeric calculation in the parent table: sum( REP_LOG::Exam_Minutes ) (which = 930, for example, for room 3).
So that gets you the numbers you need to do the further utilization percentage calculations.
Now the good question is what is this "parent" table? Well, at least so far in your database, you don't have a "Room" table, with a record representing each room. You could add such a table, and for the above calculations, add the REP_LOG relationship from this perspective (and instead of using a room_number_g, global field, use the room_number ID field as one of the predicates). That would allow you to produce a report from the Room table, showing each room in a row and any utilization calculations for each room in respective columns.
I hope this works and makes sense.
Yes, Michael, it might be possible (or better) to try this with ExecuteSQL() in FM 12, but I'm going to think it through "old school," as just a possibility.
I haven't suggested using ExecuteSQL().
we're going to fill datefilter_g with a return-delimited list of dates (in text format), such as
This is a "multi-key" field, meaning that any of these values would satisfy a match to exam_date in the relationship. The field can contain hundreds of date entries
That's a bit of "too old school", don't you think? Since version 7, you can define a range relationship, without enumerating all the interim dates.
In any case, I believe that the report should be based on summaries rather than on relationships. In your example, the report is limited to a single room selected in the global. Even if producing the report from a Rooms table, it still lacks the flexibility of reporting on any found set (for example, excluding certain dates and/or activities) as well as the ability to provide further breakdowns, e.g. utilization by type of activity.
Thank you so much for putting so much time to think for me.
Looks like there is no easy solution for my problem. I read your instruction and need more time to understant your solution method. I may ask your help again.
Thank you for your continuous input.
My orginal request is identical to this. When I said Group of records I meant "Group of dates". Looks like my original explanation was not clear...
Certainly, Michael. But in this situation, it isn't a completely inclusive range (10/1 through 10/9), as 10/6 and 10/8 are excluded, due to the room being closed on certain days. Also, the reports needed may be only weekdays or only weekends. Yes there are some advanced tricks you could employ to accomplish this via a more complex relationship and without the multi-key values. This is just the simplist solution I could come up with without going in to adding things like a holiday date table or a bunch more calculations and relationships.
When I said Group of records I meant "Group of dates". Looks like my original explanation was not clear...
My point is this: say you want to produce a report for the month of September 2012. In your example, you have 230 records for this month - but there's no guarantee that you have at least one record for each work day in September. IOW, the count of unique dates in your data is not necessarily the number of available days in the reported period.*
This is why I suggested calculating the available time separately from the time actually utilized.
(*) And even if it is, it's far from being the simplest way to arrive at this number.
Message was edited by: Michael Horak