From: | Roxanne Reid-Bennett <rox(at)tara-lu(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: ERROR: missing FROM-clause entry for table |
Date: | 2016-02-11 20:39:44 |
Message-ID: | 56BCF190.2030509@tara-lu.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Yury Zhuravlev | 2016-02-11 21:56:56 | Re: Test CMake build |
Previous Message | Tom Lane | 2016-02-11 19:37:12 | Re: Test CMake build |