Post reply

Schedule a job to get last year data from different servers

  • May 20, 2014 at 7:08 pm

    #285844

    Hi I need some help in solving this issue.

    Whenever a customer enrolls in a program I need to check his past 1 year data to get the total fare he made based on his mobile number.For this I have to schedule a job to run every day to get the fare of new customers.

    Now the problem is his last 3 months data will be in production database and 9 months data will be in archive database.Here data retrieval should happen dynamically from the time customer enrolls.

    I would like to know what is the best approach to solve this .

    Select sum(totalfare) from tblTrips where mobileno='123456889' and tripendtime between @startdate and @ enddate

    Union all

    Select sum(totalfare) from linkedserver.tblTrips where mobileno='123456889' and tripendtime between @startdate and @ enddate

    Thanks

  • May 21, 2014 at 9:11 am

    #1715412

    But what is your problem? You posted a solution, just wrap it into a stored procedure and place it in your prod server. And call it whenever you have a new customer.

  • May 21, 2014 at 12:00 pm

    #1715455

    You could use a linked server, but I prefer to stay away from them. I'd probably use SSIS to read new customers and then get the archived data for the new customers and put it in a "permanent, temporary" table for the processing I need.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply