5 Replies Latest reply on Sep 19, 2012 10:33 AM by HelderSantos_1

    making count by clock time ??

    HelderSantos_1

      Title

      making count by clock time ??

      Post

      I have a database that records the day and time that the employee has a meal.

      how can count how many breakfast, lunch and Dinners each employee has taken per month?
           breakfast from 7:00 to 9:00, Lunch 12:00 to 15:00, Dinner 19:00 to 21:00

           do i have to user (am) and (pm) for time format?

      the table has a field for day and one for the time

        • 1. Re: making count by clock time ??
          philmodjunk

               Do you have individual records for each time an employee has a meal?

               Time is recorded as an integer with a count of the number of seconds since midnight and you can add and subtract values from time fields just like it is a number as long as you understand that the number represents seconds. The format you see on the screen is just special formatting and you can enter time in either 12 or 24 hour formats.

               You can add a calculation field such as:

               Case ( TimeField > GetAsTime ( "7:00" ) and TimeField < GetAsTime ( "9:00" ) ; "Breakfast" ;
                            TimeField > GetAsTime ( "12:00" ) and TimeField < GetAsTime ( "15:00" ) ; "Lunch" ;
                            TimeField > GetAsTime ( "19:00" ) and TimeField < GetAsTime ( "21:00" ) ; "Dinner"
                           )

               If you add a summary field that is defined as a "count of" some other field in your table that is never empty. (This time field will work for that.) If you place this field inside a sub summary layout part "when sorted by" the above calculation field, you can sort your records by this calculation field and get a count of how many records fall in each category. You can list the individual records in each group along with the sub totals or you can remove the body layout part and just have a layout that lists the three sub totals.

               Note: an alternative approach to this calcualiton field is to add a field with a value list that requires that each record be labeled as "breakfast" "lunch" or "dinner" at the time the record is created.

          • 2. Re: making count by clock time ??
            HelderSantos_1

                 how many option on Case can i use?

                 I did add a 4 rolls and is not working:

                 Case ( RA_Hora  ≥  GetAsTime ( "07:00" )  and  RA_Hora  ≤  GetAsTime ( "9:00" ) ; "Pequeno Almoço" ;
                              RA_Hora ≥ GetAsTime ( "12:00" )  and   RA_Hora ≤ GetAsTime ( "15:00" ) ; "Almoço" ;
                              RA_Hora ≥ GetAsTime ( "19:00" )  and   RA_Hora ≤ GetAsTime ( "21:00" ) ; "Jantar" ;
                              RA_Hora ≥ GetAsTime ( "23:00" )  and   RA_Hora ≤ GetAsTime ( "01:00" ) ; "Ceia"
                             )

                  

            • 3. Re: making count by clock time ??
              philmodjunk

                   It's basically unlimited, you can add as many as you need.

                   Make sure that RA_Hora is a field of type Time--not Text.

              • 4. Re: making count by clock time ??
                philmodjunk

                     Just spotted the issue with the last pair of expressions.

                     No time value can be larger than 23:00 and also smaller than 01:00. Remember that time values are really the number of seconds from midnight of the day before.

                     Use this expression:

                     Case ( RA_Hora ≤ GetAsTime ( "01:00" ) ; "Ceia"
                                  RA_Hora  ≥  GetAsTime ( "07:00" )  and  RA_Hora  ≤  GetAsTime ( "9:00" ) ; "Pequeno Almoço" ;
                                  RA_Hora ≥ GetAsTime ( "12:00" )  and   RA_Hora ≤ GetAsTime ( "15:00" ) ; "Almoço" ;
                                  RA_Hora ≥ GetAsTime ( "19:00" )  and   RA_Hora ≤ GetAsTime ( "21:00" ) ; "Jantar" ;
                                  RA_Hora ≥ GetAsTime ( "23:00" )  ; "Ceia"
                                 )

                • 5. Re: making count by clock time ??
                  HelderSantos_1

                       All i have to say is many thanks...

                       now i will try to count all them using filtertext then count each one.