On 2/11/2016 2:15 AM, bigkev wrote:
...
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.
If I've read your original query correctly.. Based on this ... I think you were just off a little bit on your idea.
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
