SQL Server Performance

Accumulating data and Transfer data

Discussion in 'Getting Started' started by shan1430, Apr 3, 2008.

  1. shan1430 New Member

    I have a table called Daily.
    It has 5 columns, "Testers ","Activity ", "GivenHours ","UsedHours " and " Delta ".
    This table will be regularly updated every 12 hours.
    Now I need to Accumulate the data from GivenHours and UsedHours with respect to the testers and insert the accumulated data Into a new table called "Weekly".
    The accumulation should last only for 7 days and then it should be reset again to accumulate new data for the new week.
    Weekly table should have 5 columns "Testers ","Activity ", "TotalGivenHours ", "TotalUsedHours " and " Percentage ".
    Percentage is ( TotalUsedHours / TotalGivenHours ) *100.

    Can anyone tell me how to do this?
  2. Adriaan New Member

    Usually the only reason to store aggregate data is for reporting purposes. Use a query or a view instead.
    Now go and do your homework without calling in the experts - become an expert yourself!
  3. SQL2000DBA New Member

    Best way to deal with aggregated data is write one stored procedure with following logic
    1). Delete all data from Weekly table
    2). Using T-SQL queries aggregate the data
    3). Insert it into your Weekly table
    Once you are done with stored procedure creation, Use SQL Server Agent to schedule your stored procedure with frequency occurrence as weekly. To get better performance, run your weekly job in offpeak time.

Share This Page