none
Need help for the query

    Question

  • Hi,

     

    I have the following tables in my data base with following columns:

     

    Table 1(Water_Usage_per_hour): user_id, water_used_per_hour, Update_time (when data updated)

    Table 2(User_info): user_id, locality, state

     

    There are two applications interacting with this database:

    Application 1: it updates the data of different users at different instant of time. We believe that different users started using the application at different times therefore their hourly updation will also differ

    Application 2: this does the following query->

     

    it gets the total water used every hour by the users belonging to a particular locality ( say 'a' ) for the last 6 hours ie

    total water used by all users of locality 'a' in last hour, total water used by all users of locality a in hour before last and so on.

     

    can any body help us with the possible query of the 2nd application ???

    Friday, February 03, 2012 3:29 PM

Answers

  • Hi King,

    To do the query on Application2, the latest data should be inserted instead of being updated when working with Application1 or the history data could be stored in other place before being updated ,otherwise, you could not get the data last 6 hours data.

    In addition, i'd suggest you consider Change Data Capture(CDC) used in your project. For more information about CDC, see http://msdn.microsoft.com/en-us/library/bb522489.aspx .

    Hope this helpfully,

    Regards,
    Jerry

    Thursday, February 09, 2012 2:14 AM