AnsweredAssumed Answered

ExecuteSQL problem

Question asked by ChrisPye on Apr 6, 2013
Latest reply on Apr 7, 2013 by jbante

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

 

Thanks,

Chris.

Outcomes