Calculating Issue dates for publication

[pcourterelle]

I am working on a db for a small publishing firm. The company produces two biweekly magazines (that is each magazine publishes on alternating two week cycles.) Publishing day is always Thursday. For most months the magazine is published twice but there are months during the year where they will publish have to publish three. (ie there are two months every year that have five thursdays, three of which could be publishing dates).

The first publication came out Jan 1, 2004. Thus Issue one was in the second week of 2004, Issue 2 was in week 5, issue 3 in week 6 ect.

Each magazine has an Issue tag (issue 1, issue 2, issue 3...issue N) I need to be able to calculate the issue from the current date. If the magazine published every two weeks like clock work this would be simpler but there are two notable exceptions. The company only publishes the magazines once during July and December, which throws off the issue count. Also the Issue numbers runs consecutively. Thus the last issue of 2004 will be 24 and the first issue of 2005 will be 26 and so on.

I cannot wrap my head around the exceptions and how to account for only one issue in July and Dec and how this affects the running issue count. A good chunk of the database depends on this calculation being 100% correct. After banging my head for two weeks I've come for help. Anything you could provide would be greatly appreciated.

thanks

phil courterelle

calgary

[The Shadow]

We can help, but for me, at least, you need to be more clear.

May I suggest posting a sample table for 2004, with the issue number, corresponding date, and explanation for weeks that were skipped.

[pcourterelle]

No problem...Sorry if the post is confusing....I've listed the dates for every issue in 2004 and the beginning of 2005. Note that the issue dates are a running total. The magazine publishes every two weeks EXCEPT in July and December when it publishes only once in each month.

From 1/8/2004 to 7/8/2004 the pattern is an issue every two weeks. The Issue for 7/22/2004 is skipped as they only publish once in July. Same for December. The specific dates where they skip varies depending on the year (obviously) as does the number of issues skipped.

The formula Round((((Today's Date - OriginalIssueDate)/14 )+1),0) calculates the total number of bi-weekly (14 day) periods between the original issue date (1/8/2004) and the current date. But this does not account for the exceptions in July and December to the biweekly rule.

Date -- Issue

1/8/04 -- 1

1/22/04 -- 2

2/5/04 -- 3

2/19/04 -- 4

3/4/04 5 -- 5

3/18/04 -- 6

4/1/04 -- 7

4/15/04 -- 8

4/29/04 -- 9

5/13/04 -- 10

5/27/04 -- 11

6/10/04 -- 12

6/24/04 -- 13

7/8/04 -- 14

------------- only one issue in July

8/5/04 -- 15

8/19/04 -- 16

9/2/04 -- 17

9/16/04 -- 18

9/30/04 -- 19

10/14/04 -- 20

10/28/04 -- 21

11/11/04 -- 22

11/25/04 -- 23

12/9/04 -- 24

---- 12/23/2004 is skipped

1/6/05 -- 25

1/20/05 -- 26

2/3/05 -- 27

[Vaughan]

I'd approach this by turining it around... enter a start date, then assume that the next publication is 2 weeks away. Create another table that contains the dates that you DO NOT want to publish on, an exclusion list.

The devil is in the detail of course... it might be easier to make the exclusion table a list of months that are to only have one edition, for instance. A lot depends on how much flexibility you want to build into the system, which depends on how likely it is that the boss will change their mind about how often it will be published.

[pcourterelle]

Vaughn: I had thought of this approach but the number of possible publishing dates per month changes every year. For example: In 2004, July has only two possible publication dates while in 2008 it has three. This means I would need to hard code into the db every exclusion for every year for every product. No??

But I agree with you that I need to isolate the exceptions. I'm considering the following:

Sum of

if start date is less than July, then take ((June 30 - start date)/14) +1 to calculate all the biweekly periods between the start date and June 30)

Plus 1 for July

If end date is greater than or equal to August, than ((end date - August)/14)+1) for all biweekly periods from Aug to the end date.

End Sum

The problem with this is that the start date, July, August and end date may be in different years. Each contract is only one year long but can start at any point. For example: start date 10/10/2004. end date 9/30/2005.

As for flexibility...they want to be able to bend over backwards, tie it in a bow, tie the bow into a pretzle and tie the preztle into a Gordian knot.

[Vaughan]

"I would need to hard code into the db every exclusion for every year for every product"

Not hard code a calculation. Add a record into the exclusions database, yes. This is the whole point of having an exclusions database. Nothing is hard coded.

[The Shadow]

Maybe this can help get a little closer to a solution.

If I understand correctly, it looks like the years follow an 11-year cycle, as long as neither is a leap-year, they are identical. Even when they are a leap year, corresponding years tend to exclude the same number of days, but 2008 (a leap year) has an extra thursday in July.

# of bi-weeks in July for a year y =

[Fitch]

Have you considered just setting up a separate table with one record for each issue? Create 10 years worth of issues, adjust the dates and numbers, and there you have it.

Having a separate table also may give you other ideas about tracking the issue status etc.

[pcourterelle]

Tom/Vaughn: Thanks for all your input...sorry for taking so long to get back to this but was on a mini-holiday for a few days...The exceptions table would look something like this:

Record Month Year Subtraction

1 July 2004 -1

2 Dec 2004 -1

3 July 2005 -1

4 Dec 2005 -2

But what relates the exceptions table to the AdOrdering db? Year? Month & Year? I just don't see it. A contract may or may not include July or Dec therefore I need a way of identifiying when these months come into play. In addition a contract may start in one year and finish in another. For example, Nov 2004 to August 2005, including Dec 2004 and July 2005. This is what I mean by hard coding a solution...a caluclation that takes

these factors into consideration...

cheers

pc

[Fitch]

What I meant was not an "exceptions" table, but a table with one record for each issue. So counting the number of issues between two dates is just a matter of counting found (via script) or related (by date range) records in the table.