I have recently started using the ExecuteSQL command in FM 12, and I'm really starting to see the benefits of learning/using this.. I have created a few simple queries that have saved me unnecessary table occurrences, bit I'm a bit stuck on a more complicated one.
The two tables in question are "Company" and "Notes", joined by the CompanyID field in both.
I need to get a count of how many related Notes for a Company were created between the current date and a pre-determined period before the current date (say 30 days). There is a date field in the Notes table named "Created". I'm aware of the SQL COUNT() and CURRENT_DATE function, but i'm not sure how to pull it all together.
I know how to do this without ExecuteSQL but I would prefer use it if possible (and learn something new).