On 2/10/2016 1:33 AM, bigkev wrote:
> Yes, c.start_time is a timestamp.
It is really late where I am, so I apologize if I'm being dense...
> My goal is to list events (calls) which are scheduled on fortnightly basis.
> The query works with hard coded values, but I need to generate results for
> days beginning at the start_time and ending with the end_time.: eg:
>
> 2016-02-06
> 2016-02-07
> 2016-02-08 | Some account name | other info
> 2016-02-09
> 2016-02-10
> ....
> 2016-02-22 | Some account name | other info
> 2016-02-23
> 2016-02-24
> etc etc
Is this exactly the format of the data you are looking for?
If there was an event on 02/10, would it show or not? [because it
doesn't fit in a fortnight schedule from 02/08, but could still be
scheduled to run fortnightly [e.g. 02/10, 02/24...]
> All dates within the first generate_series are listed, 1 row for each day.
> The second generate_series is to show events(calls) which begin on
> c.start_time and end at c.start_time.
> Records would be displayed, at each 14 days from the start_time.
I am confused about "which" start time. Your description implies there
is just one start time that bounds your entire query. You have a column
"start_time" in the Call Event table which is presumably the start of an
event. However, each row in the table potentially has a totally
different value. So what do you mean by "the start time"? There isn't
a 14 days from "a" start time if you are using c.start_time and
calculating 14 days into the future for each row in the Call Event
table.. Each event would then have a fortnight period and your query
results are not bounded by time at all.
> In the above example, this would be 2016-02-08 and end_time would be a date
> in the future.
So for "the start time" do you mean the first (minimum) start time in
the Call Event table?
Or are you trying to gather all the call events that are scheduled 14
days apart, listed in order by day?
Is there anything that ties the call events together as being in a set
other than they take place exactly 14 days apart?
Roxanne
--
[At other schools] I think the most common fault in general is to teach students how to pass exams instead of teaching
themthe science.
Donald Knuth