>> I have a set of agreements that have a date the agreement was started. From those agreements I track activities that occur during 3 month periods starting from the start date. I calculate those periods on the fly to report the number of activities that occurred during the current period and the previous period. <<

This is not how we write SQL; this is basically 1970's COBOL written in T-SQL dialect along with some really weird data modeling.

Do you understand that SQL is a database language, we do not like doing calculations. We look things up with tables. we do not calculate things "on the fly" because we have a firm data model that we know about in advance. We set our little make-believe universe up one time and go with it.

Since you did not bother to post any DDL (please read the forum rules), we have to make some guesses about keys and everything else. But you failed to use the ANSI ISO standard format for dates, use the old Sybase proprietary convert () function, and have an awful lot of dialect in your code. Let us start off of the idea of a table of report periods.

https://www.simple-talk.com/sql/t-sql-programming/temporal-data-techniques-in-sql-/

Build a look up table of reporting periods

CREATE TABLE Report_Periods

(report_name VARCHAR(30) NOT NULL PRIMARY KEY,

report_start_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,

report_end_date DATE NOT NULL,

CONSTRAINT date_ordering

CHECK (report_start_date <= report_end_date),

etc);

These reports can overlap; a fiscal quarter will be contained in the range of its fiscal year. There can be gaps between them;etc.

I like the MySQL convention of using zeroes in the ISO-8601 display format because it sorts correctly and is language independent. This uses "yyyy-mm-00" for months within a year and "yyyy-00-00" for entire years.

The basic skeleton for use with these tables is

SELECT R.report_name, << summary computations >>

FROM ReportRanges AS R, Events AS E

WHERE E.event_date BETWEEN R.report_start_date AND report_end_date

AND R.report_name IN (<<report name list>>)

GROUP BY R.report_name;

The next concept you seem to have missed is that of "<something>_status" during those time slots. The ISO model of time is based on half open intervals. We know the initial or start time and the terminal or endding time can be a precise point in time (which is never actually reached, but forms a limit) or is a null, if the status is still current. download the Rick Snodgrass book on temporal queries in SQL; it is a free PDF from the University of Arizona.

>> The tricky part is that the when this happens, the duration of the periods needs to be extended so the person has the full 3 months to collect their activities and be rewarded. <<

No, it is not tricky at all. this is a standard SQL idiom. But you need to use some constraints to guarantee that the durations do not overlap and are contiguous. Google around you will find constraints for guaranteeing this. the Google search for this will be "https://www.simple-talk.com/author/alex-kuznetsov"

>> SELECT 555 AS party_id, 777 AS role_id, 12345 memberagreement_id, CAST('2016-01-22' AS DATE) AS editable_start_date

INTO member_agreement –-fake scratch tape!

.. <<

This piece of code has all kinds of problems. Identifiers (I assume that is what you meant by "_id") are never numerics; you do not do math with them! This looks like Kabbalah magic; this is an old Hebrew superstition that God assigns a number to every entity in creation, and if you know it is special Kabbalah number, you have all kinds of magical powers over it. You can animate a golem, etc. this is absolutely the antithesis of RDBMS and logic.

We do not use the old Sybase convert () string function today. T-SQL now has the ANSI/ISO standard cast () function. You also used the old Sybase insertion syntax that has been overridden for many many years.

Back when we had magnetic tapes, we had to materialize the data on either punchcards or tapes to use it. This is where Sybase got the "SELECT ..INTO.." proprietary feature. Besides having names that violate ISO 11179 rules, what you are really doing is faking scratch tapes. if you do not know the correct syntax. Here is a skeleton:

INSERT INTO Foobar

VALUE

(2, 12345, '2016-10-05', '2016-05-30'),

(3, 12345, '2016-10-07', '2016-07-20'),

(4, 12345, '2016-10-08', '2016-09-30'),

(5, 12345, '2016-11-10', '2016-11-30');

>> the proposed solution is to put the baseline period dates in a temp table [scratch tapes, complete with a row_number () to fake sequential record numbers of a tape] and run a cursor or loop over the suspension table to update the dates based on the number of days the suspension. It would irk me to do that but a set based solution has evaded me thus far. <<

I assume you are aware that the word "cursor" is derived from the root word "curse" to SQL programmers? 😀 Using the cursor says that your design is wrong. Yes, it is possible that a cursor can be a solution. In 30+ years with SQL, I have written five myself; I know I could have gotten rid of three of them if we had the current features of ANSI/ISO standard SQL.