Silly question I suspose but when developing queries specific for reporting (i.e. read only, multiple table joins, etc.) what is the prefered way of writing the models and controllers?
Example (simple sql):
SELECT users.id, users.name, ISNULL(SUM(ticketIncidents.totaltime), 0) AS totalTime, COUNT(ticketIncidents.totaltime) AS totalIncidents FROM users LEFT OUTER JOIN ticketIncidents ON users.id = ticketIncidents.assignedby_id GROUP BY users.id, users.name ORDER BY users.name
The above sql is pretty simple but what happen when the sql gets complicated? Oracle has PL/SQL and MSSQL has Transact-SQL which add the ability to add procedural elements to the sql. Any thoughts?