4 Replies Latest reply on Aug 14, 2014 1:24 PM by philmodjunk

    ExicuteSQL problem

    RossEwing

      Title

      ExicuteSQL problem

      Post


           Hello all, I am new to FileMaker and Even Newer to SQL so any help is appreciated.

           I am working on a Staff scheduling solution and one calculation is killing me. I have the following tables: Employee_Info, Stagehand_Shifts, Interface. My layout uses filtered portals to show a calendar for employees and I want to show a weekly total of the amount of scheduled hours for each week for each employee. I will use 5 repetitions of this calculation for each of the displayed weeks. I am able to use ExicuteSQL to return Total Hours per Employee, but when I try to use WHERE to restrict records to a date range i get no results.

           How do I need to structure the Query to filter out unwanted records. Here is what I have so far.

           ExecuteSQL (
           "SELECT Employee_ID,
           Sum (Total_Shift_Hours) 
           FROM Stagehand_Shifts
           GROUP BY Employee_ID"
           ; "," ; "" ; "")

            

           Thank you

            

            

        • 1. Re: ExicuteSQL problem
          philmodjunk

               How is the date range specified? To dates in date fields? A calculated range of dates based on the value of Get ( CurrentDate) ? Or ???

               Is there a field of type date defined in Stagehand_Shifts to use for limiting the records to a date range?

               And example of your query showing one of your attempts at a WHERE clause might be helpful in helping us to help you.

          • 2. Re: ExicuteSQL problem
            RossEwing

                 For the date range I am planning on using a calculation field from the Interface table cDateoffirstportal, that calculates the date for the first day in the calendar, then I just need that same day + 7 to get my date range, I believe I need to say "BETWEEN cDateoffirstportal AND cDateoffirstportal +7"

                  

                 cDateoffirstportal is as follows

                 Let ( [

                 FirstDayOfTheMonth = DayName ( Date ( gSelectedMonth ; 1 ; gSelectedYear ) ) ] ;

                 Date ( gSelectedMonth ; 2 ; gSelectedYear ) -

                 Case (
                 FirstDayOfTheMonth = "Monday" ; 3 ;
                 FirstDayOfTheMonth = "Tuesday" ; 4 ;
                 FirstDayOfTheMonth = "Wednesday" ; 5 ;
                 FirstDayOfTheMonth = "Thursday" ; 6 ;
                 FirstDayOfTheMonth = "Friday" ; 7 ;
                 FirstDayOfTheMonth = "Saturday" ; 1 ;
                 FirstDayOfTheMonth = "Sunday" ; 2 ) )

                  

                  

            • 3. Re: ExicuteSQL problem
              RossEwing

                   Yes each of the Stagehand_Shifts has a Shift_Day_Start and Shift_Day_End Field

              • 4. Re: ExicuteSQL problem
                philmodjunk

                     Assuming that the shift_day fields defined in stagehand_shifts are defined as date fields and not text, try this query:

                     ExecuteSQL (
                     "SELECT Employee_ID,
                     Sum (Total_Shift_Hours) 
                     FROM Stagehand_Shifts
                     GROUP BY Employee_ID
                     WHERE ? >= Stagehand_Shifts.Shift_Day_Start and ? <= Stagehand_Shifts.Shift_Day_End"
                     ; "," ; "" ; cDateoffirstportal ; cDateoffirstportal )