14 Replies Latest reply on Nov 14, 2012 2:22 PM by comment

    How can I get a unique count for a "group of records"?

    jayankurian

      REQUESTING HELP!!!!!

       

       

      I have a database which contains 230 records for the Month of September 2012.

       

      Data Example:

      Sept 1, I have 8 records

      Sep 2, I have 18 records

      Sept 3, I have 12 etc.

       

      How do I get the count of days(unique count of day, in other words if Sept 1 have 8 records I want a field which should hold the value of 1 for Sep 1, and value 1 for Sep 2, value 1 for Sept 3 and so on... later I want to get a total of this value for some other calculations.

       

      Any easy way other than writing complex script?

       

      Thank you

        • 1. Re: How can I get a unique count for a "group of records"?
          ErikWegweiser

          Hello, jayankurian:

           

          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?

          • 2. Re: How can I get a unique count for a "group of records"?
            comment

            See if this helps:

            http://fmforums.com/forum/topic/61158-number-of-employees-from-payroll-report/page__view__findpost__p__289204

             

             

            ---

            In version 12, you can use the ExecuteSQL() function to get the same result.

             

            Message was edited by: Michael Horak

            • 3. Re: How can I get a unique count for a "group of records"?
              jayankurian

              Thank you for looking into my issue.

               

              Will get back to you in detail.

               

              Thanks again

              • 4. Re: How can I get a unique count for a "group of records"?
                jayankurian

                Thanks for guiding me to the right direction.

                • 5. Re: How can I get a unique count for a "group of records"?
                  jayankurian

                  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.

                  Example Records

                  Accession #DateSerial#Room #ProcedureExam
                  Start Time
                  Exam
                  End Time
                  TAT
                  In minutes
                  1976671010/01/201223950502Venous10:40:0012:00:0080
                  1976728810/01/201225191033Venous11:20:0011:45:0025
                  1976863810/01/201224883713Venous13:45:0014:30:0045
                  1976849710/01/201221129012Venous14:35:0014:40:005
                  1976882410/01/201211307153Drainage15:10:0015:45:0035
                  1976906610/01/201225896231Venous16:00:0017:00:0060
                  1977138510/01/20125733303Venous11:00:0012:30:0090
                  1977147310/02/201225900172Venous10:00:0011:00:0060
                  1977138510/02/20125733303Venous11:00:0013:20:00140
                  1977218110/02/201219060272Biopsy12:00:0012:15:0015
                  1977324910/02/201223668182Venous15:15:0016:00:0045
                  1977547210/03/20126586183Biopsy10:10:0010:55:0045
                  1977646010/03/201233864233Drainage12:15:0012:30:0015
                  1977607510/03/201225903031Venous10:00:0012:00:00120
                  1977706810/03/201222602503Venous14:00:0014:30:0030
                  1977704510/04/20125039791Venous13:10:0014:10:0060
                  1978160410/04/201216803902Hemodialysis14:20:0015:50:0090
                  1978979910/04/20122558798CTDrainage14:00:0014:50:0050
                  1978425110/05/201225758403Tube10:15:0010:50:0035
                  1978564410/05/201225345193Venous13:30:0014:15:0045
                  1978510110/05/201225113283Venous12:10:0012:40:0030
                  1978448910/05/20121348262Hemodialysis10:55:0012:05:0070
                  1978447210/05/201225902701Venous10:15:0013:30:00195
                  1978575210/05/201213778152Tube13:30:0014:45:0075
                  1978609510/05/2012434684CTDrainage15:00:0015:30:0030
                  1978510210/05/201225113283Venous12:10:0012:40:0030
                  1978423110/05/2012257584133Tube10:15:0010:50:0035
                  1978997910/07/201223155602Venous12:00:0013:30:0090
                  1979535210/09/201225904051Venous14:15:0015:30:0075
                  1979399310/09/201225679513Venous10:50:0012:50:00120

                  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.

                  • 6. Re: How can I get a unique count for a "group of records"?
                    ErikWegweiser

                    jayankurian,

                     

                    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?

                    • 7. Re: How can I get a unique count for a "group of records"?
                      jayankurian

                      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

                      • 8. Re: How can I get a unique count for a "group of records"?
                        comment

                        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.

                        • 9. Re: How can I get a unique count for a "group of records"?
                          ErikWegweiser

                          jayankurian,

                           

                          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

                           

                          10/1/12

                          10/2/12

                          10/3/12

                          10/4/12

                          10/5/12

                          10/7/12

                          10/9/12

                           

                          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.

                          • 10. Re: How can I get a unique count for a "group of records"?
                            comment

                            ErikWegweiser wrote:

                             

                            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().

                             

                             

                             

                            ErikWegweiser wrote:

                             

                            we're going to fill datefilter_g with a return-delimited list of dates (in text format), such as

                             

                            10/1/12

                            10/2/12

                            10/3/12

                            10/4/12

                            10/5/12

                            10/7/12

                            10/9/12

                             

                            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.

                            • 11. Re: How can I get a unique count for a "group of records"?
                              jayankurian

                              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.

                              • 12. Re: How can I get a unique count for a "group of records"?
                                jayankurian

                                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...

                                • 13. Re: How can I get a unique count for a "group of records"?
                                  ErikWegweiser

                                  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.

                                  • 14. Re: How can I get a unique count for a "group of records"?
                                    comment

                                    jayankurian wrote:

                                     

                                    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