ExecuteSQL("SELECT dateField FROM dates WHERE DayOfWeek(dateField) = ? OR DayOfWeek(dateField) = ?" ; "" ; "" ; "1" ; "7" )
Where you have a table called dates with a date field called dateField
You could also constrain it to a specified range:
ExecuteSQL("SELECT dateField FROM dates WHERE dateField BETWEEN ? AND ? AND ( DayOfWeek(dateField) = ? OR DayOfWeek(dateField) = ? )" ; "" ; "" ; startDate ; endDate ; "1" ; "7" )
And if you wanted to return just the count of days:
ExecuteSQL("SELECT COUNT (DISTINCT dateField) FROM dates WHERE dateField BETWEEN ? AND ? AND ( DayOfWeek(dateField) = ? OR DayOfWeek(dateField) = ? )" ; "" ; "" ; startDate ; endDate ; "1" ; "7" )
Champion effort Mike. Thanks for your help.
I am very grateful. The new guy will be ecstatic and impressed.
Sure thing, FYI complex SQL queries can sometimes take longer to run than a recursive custom function counterpart. There are advantages and disadvantages to each to keep in mind. Good luck!
I'm so glad Mike got it right! I was trying to think of ways to supply a range and have ExecuteSQL determine your needs (without actually querying real data).
Even though that's not what you needed, I think there is a way do this and may ultimately use a CustomFunction to create the query.
This part was kind of arbitrary:
whether dates within a range
I was assuming you were trying to query existing filemaker data.
If you want to query a range with non-existent data I'm not really sure what FM supports inside of ExecuteSQL(). I'd imagine you would try and use either DATEDIFF(), or something looped together to check with WHILE.
Hopefully filemaker does expand on the functionality (and documentation!) of what's supported. There's a lot more that SELECT statements are capable of, even if they never give us INSERT/UPDATE/DROP/DELETE statements.
I was thinking more of using UNION and CASE (SQL functionality).
Given any value, yes you can "create" data, because the result is placed inside a TEXT field (or variable) and not an UPDATE or INSERT to the table(s).
But without a "loop" inside SQL statements (at least not within ExecuteSQL function), then the query may need to be constructed (with FM functions) and then run.
I've done this when given the start and end range of dates and "created" dates within the range. The resulting list can be used however needed. The trick for Michael (itraining) would be to check for those weekend dates. But ultimately, he wanted counts, so may even use nested SELECTS.
and in the end, I think I'd bounce back to native FM functions (and/or custom functions with recursion) and variables to do the same thing.