Picking Addresses by Date

[Tom Mills]

In the industry I'm in, it's not unusual for the people in the database to have 2 addresses. One for the winter and one for the summer. Addresses are normally stored in their own table.

In the legacy app we use, they have a Y/N field for every month. (AddrJan, AffrFeb, etc.) When you retrieve an address to use for a mailing, you ensure it's valid by checking the appropriate field for the month you are in.

I have two issues with this system. One is that it offers no "fine tuning", if the person will move June 15th for example. The other is that it just offends my sensibilities!! I hate having 12 fields for that. It just seems like their should be a smarter way. But I can't fuigure it out.

The problem is that a particular address will be valid (for example) from 1 Jan to 15 Jun and then 15 Sep to 31 Dec. How can you store this and retrieve it by checking it against the current date?

[whanigsberg]

RE: Picking Addresses by Date

Here's one idea.

Each address is a separate record. If a person has two addresses he gets two records. Add two fields: start_date, and end_date.

To know where to send you need the person's id and the shipping date.

So you then write a function called shipto which takes two arguments: person_id, shipping_date. Using these two values it can look up the correct address.

Use the function in the filter expression of your mailing label definition.

No?

Bill

I bet others will have interesting ideas about how to do this efficiently.

[Tom Mills]

RE: Picking Addresses by Date

Bill, the process you described is essentially what we have. Each address ia separate record. The problem comes about because one address can have 2 effective periods depending on how you look at it.

For example an address that is valid from 1 Jan - 15 Jun and 15 Sep - 31Dec, can also be thought of to be valid from 15 Sep-15 Jun.

So if you have one start field and one stop field on the record, you'd have to employ the 15 Sep-15 Jun method. Problem is, 15 Sep as the starting point of the "range" is bizarre because it occurs after the ending point of the range.

One vendor I saw took care of this by making the start and stop dates complete including the year so that you'd have: 09/15/2001 - 06/15/2002. That gave you a normal range of low to high. It also meant they had to run utilities against the data constantly to update the records for the next year. I thought that was a bad solution.

Now, just the way you phrased your answer made me think of something I hadn't thought of before. I could have three records.

123 Smith Lane start 1 Jan end 14 Jun
234 Elm Street start 14 Jun end 14 Sep
123 Smith Lane start 15 Sep end 31 Dec

That is only one additional record than I would have before, I eliminate the 12 fields and add 2. I get the accuracy of the dates and ranges which go low to high. The cost is one additional record.

So I might do that unless someone has a brilliant theory.

[whanigsberg]

RE: Picking Addresses by Date

This is interesting.

The real structure of this is one to many: one address to many date ranges. (That's why you get so many fields using a single table.) What would happen if you set it up as follows.
Set one= addresses to ranges (1:N)
Set two= ranges to address (1:1)

Print labels from set2 with a filter on senddate is contained within the start and end dates.

Your turn.

Bill

[Tom Mills]

RE: Picking Addresses by Date

That's what I'm talking about!! A fresh perspective!! I had not considered that possibility. Let me ponder that.

Thanks