Recurring bookings

Started by Kevin Watersonabout 10 years ago3 messagesgeneral
Jump to latest
#1Kevin Waterson
kevin.waterson@gmail.com

So far this is what I have.. (see below).
How can I have recurring bookings for a call?
Eg: if the call_frequency is weekly, how can I see a list of dates which
this account will be called upon?

Kind regards
Kevin

CREATE TABLE call_frequency (
id SERIAL PRIMARY KEY NOT NULL,
name varchar(25) NOT NULL,
description VARCHAR(200) NOT NULL
);
COMMENT ON TABLE call_frequency IS 'How often a call is scheduled';
INSERT INTO call_frequency( name, description ) VALUES ('Once Only',
'Single non-recurring call' );
INSERT INTO call_frequency( name, description ) VALUES ('Hourly', 'Every
hour' );
INSERT INTO call_frequency( name, description ) VALUES ('Daily', 'Every
day' );
INSERT INTO call_frequency( name, description ) VALUES ('Weekly', 'Every
week' );
INSERT INTO call_frequency( name, description ) VALUES ('Bi-Weekly', 'Every
second week' );
INSERT INTO call_frequency( name, description ) VALUES ('Monthly', 'Every
Month' );
INSERT INTO call_frequency( name, description ) VALUES ('Yearly', 'Every
Year' );

CREATE TABLE callcard (
id SERIAL PRIMARY KEY NOT NULL,
account_id INT NOT NULL REFERENCES accounts(id),
user_id INT NOT NULL REFERENCES users(id),
call_type_id INT NOT NULL REFERENCES call_types(id),
call_frequency_id INT NOT NULL REFERENCES call_frequency(id),
duration TSRANGE NOT NULL
);
COMMENT ON TABLE callcard IS 'Table of scheduled calls';

#2John R Pierce
pierce@hogranch.com
In reply to: Kevin Waterson (#1)
Re: Recurring bookings

On 1/21/2016 2:05 AM, Kevin Waterson wrote:

So far this is what I have.. (see below).
How can I have recurring bookings for a call?
Eg: if the call_frequency is weekly, how can I see a list of dates
which this account will be called upon?

your call frequency table probably should have a field interval_length
of type INTERVAL with values like INTERVAL '1 week'

then you could do something like select start_date +
interval_length*int_num from call_frequency join generate_series(1,n) as
int_num where call_frequency.id = ?;

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

#3Noname
cpolish@surewest.net
In reply to: John R Pierce (#2)
Re: Recurring bookings

On 2016-01-21 02:15, John R Pierce wrote:

How can I have recurring bookings for a call?
Eg: if the call_frequency is weekly, how can I see a list of dates which
this account will be called upon?

I recommend "Developing time-oriented database applications in SQL",
Richard T. Snodgrass, ISBN 1-55860-426-7, might save you a lot of
time and trouble.

Best regards,
--
Charles Polisher

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general