Post reply

Help needed to calculate last n months moving average from given date for a each and every process name.

  • March 25, 2014 at 2:48 am

    #283760

    Hi,

    I have a requirement to calculate the moving average for last 2 months from given date in the table. The no of historical months average should be configurable. e.g. i have a table which specifies the run time taken for each and every process in the SQL. i.e.

    PROCESS Name, StartDate and EndDate.

    ABC,12-01-2013 00:52:564 , 12-01-2013 00:56:435

    XYZ,12-01-2013 10:00:564 , 12-01-2013 10:12:435

    XYZ, 11-12-2012 10:00:564 , 11-12-2012 10:12:435

    ......

    I need to calculate the process time for each row i.e. DateDiff(ss,StartDate ,EndDate) . and casting it to Float to get the process time and compute the past 2 months moving average.

    I have tried implementing this using a while loop to get the previous 2 months records for each process , but its taking a long time to process 🙁 .

    Tried even implementing using cursors but the performance still seems to take a long time.

    Any help on this would be much appreciated.

    Thanks

    Cathy

  • March 25, 2014 at 5:33 am

    #1699803

    Can you post up some sample code for creating the table and data and any solution you have attempted so far. It will make helping you much easier.

    MCITP SQL 2005, MCSA SQL 2012

  • March 26, 2014 at 4:51 am

    #1700220

    Thanks but it seems like i was looping it many times.... found the solution, and it seems to retrieve the results in ms now 🙂 . I just had to make the subset distinct result set and apply the moving average .

    Thanks for the help anyways!.

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

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