Or maybe I run a query that tells me which MagazineMailings I still need to run the second query on?
How do you determine which records these are?
If all you want to know is how to fire off a script two weeks after it fires the first time, I would just save the date it was last run either in a single-record table (if you don't want / need a history), or create a record in a log table. If the latest date is more than two weeks prior to now, you run the query. You can fire the script either server-side (probably preferable) or when the file opens for a certain privilege set.
Let us know if this doesn't answer the question.
Wouldn't you just need to run the same original query? The result would be who ever is left with an expired account at that time.
If you do this on a regular schedule you could specify this as a server scheduled script for specified times. If you want to run the first script manually and then 2 weeks later for the follow up, Mike has good ideas for you.
Beats me. Forum support?
This may be caused by underscore in his name...
@mark_baum also not work, since it try to find mark AND baum.
There should be escaping syntax for underscore, but I didn't find.
Thanks Mike. Each magazine has four categories of subscribers - those on the first issue (1), those on the second to last issue of their subscription (2), those on the last issue of their subscription (3) and then everyone else (4).
Every time a magazine is sent out I run four queries, one for each category, which gives a list of Customers and this is used to send them a different covering letter with the magazine. The letters are worded according to their stage in the subscription.
What I call the 'second query' (and should probably be called query 5!) is a query which looks at the Subscriptions table and finds any new subscriptions by customers who were listed in the query number 3 (see above). If they are in this result set then it means they renewed their subscription so we don't bother them any more. The remaining customers are then passed to a call centre who will then speak to them to understand why they chose not to renew (and, I would assume, try to encourage them to renew!)
So... what I had in mind is to run the four 'mailing' queries and at that point make note of the fact that the fifth query would need to be performed in two weeks time. I think your idea of a separate table makes sense - I'm thinking I can save the date and also the list of Customer IDs that will need to be queried against. The IDs need to be stored because simply running the same query again will yield different results as customers move through their subscription period.
I think I'm going to give that a go and see how it goes.
And, yes, the query will be run server-side most likely in the early hours of the morning so that the data is ready for the mailing team first thing on a Monday morning.
Thanks to you (and bigtom) for your input