|
On 2/11/2016 2:15 AM, bigkev wrote:
If I've read your original query correctly.. Based on this ... I think you were just off a little bit on your idea.... This stores the start_time, which is the date and time a series of calls begins. The duration defines how long this event(call) will go for, and the end_time defines when the schedule will stop. I need to generate a calendar eg: 1 year with intervals of 1 day, and include fortnightly calls at the appropriate dates. the call_frequency_id would be 5 for a fortnightly call. I an post schema if you need. Consider moving the fortnight generation into a sub-query join with the call_schedule... e.g. SELECT g.*, c.meeting, a.name AS account_name, u.name AS user_name, c.start_time, c.start_time::timestamp+c.duration * '1s'::interval AS end_time, ct.name, extract(dow from c.start_time) AS start_day FROM generate_series('2016-01-22', '2017-12-31', '1 day'::interval) g(day) LEFT JOIN ( select *, generate_series(c.start_time, c.end_time, '2 week'::interval) meeting from call_schedule c where call_frequency_id = 5 ) c on ((g.day, '1 day'::interval) OVERLAPS (meeting, c.duration * '1s'::interval)) LEFT JOIN users u ON c.user_id=u.id LEFT JOIN accounts a ON c.account_id=a.id LEFT JOIN call_types ct ON c.call_type_id=ct.id ORDER BY g.day The generate_series in the sub-query could be genericized to deal with any frequency through the use of a user defined function or a join on your call_frequency table if it stores "interval" data. Tuning wise, you may need to move the 3 dependent joins into the sub-query for better performance. I'm not exactly sure what the sub-query does to the planner for index usage for joins... I am also not sure what the performance difference might be between using OVERLAPS and BETWEEN. Roxanne -- [At other schools] I think the most common fault in general is to teach students how to pass exams instead of teaching them the science. Donald Knuth |
