6 Replies Latest reply on May 15, 2016 5:14 AM by jurgmay

    Strategy for running SQL query two weeks after an initial query?

    jurgmay

      Hi all,

       

      I'm looking for a bit of strategic input from the community please...

       

      I have a magazine subscription database and a key part of the system is going to revolve around contacting people who have let a subscription expire and getting them to renew their subscription.

       

      What I need to do is run a query at a given point in time that will give me all the Customer IDs for the customers who have let a subscription expire. The result set is saved in a MagazineMailing table as a list of IDs and is used to export the customer data to send a letter out to them.

       

      Two weeks later I need to run another query to tell me which of the original customers still haven't renewed their subscriptions.

       

      I don't have a problem with the queries but my question is this...

       

      What would be the best way to initiate the follow up query? I was thinking of creating and storing a list of MagazineMailing IDs which would be a list of all the MagazineMailings which haven't yet had that second query performed. Or maybe I run a query that tells me which MagazineMailings I still need to run the second query on?

       

      Any thoughts out there?

       

      Thanks,

       

      Juerg

        • 1. Re: Strategy for running SQL query two weeks after an initial query?
          Mike_Mitchell

          jurgmay wrote:

           

          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.

          • 2. Re: Strategy for running SQL query two weeks after an initial query?
            bigtom

            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.

            • 3. Re: Strategy for running SQL query two weeks after an initial query?
              bigtom

              @Mike_Mitchell I still cannot tag you and could not send an image in a message either.

              No Mike Tag.png

              • 5. Re: Strategy for running SQL query two weeks after an initial query?
                user19752

                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.

                • 6. Re: Strategy for running SQL query two weeks after an initial query?
                  jurgmay

                  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

                   

                  Juerg