Running accumulation for past 30 days

  • 30 day accumulation Mean and Avg Mean are 30 days running, except for April 1 and Sept 1 where they go back 30 days otherwise 29 days, How would I write this?I don't know why I am drawing a blank. This is Oracle and needs to be SQL

    v_accum       NUMBER(22,9);
    begin
    IF (TO_CHAR(:p_start_date,'MM') = '04' OR
    TO_CHAR(:p_start_date,'MM') = '09') THEN
    SELECT SUM(ROUND(value)), AVG(ROUND(value))
    into v_accum, :CP_avg
    FROM num_daily
    WHERE site_code = :p_site_code
    AND shef_code='MWC'
    AND observ_date BETWEEN :p_start_date AND :observ_date;

    ELSE
    SELECT SUM(ROUND(value)), AVG(ROUND(value))
    into v_accum, :CP_avg
    FROM num_daily
    WHERE site_code = :p_site_code
    AND shef_code='MWC'
    AND observ_date BETWEEN :observ_date-:p_num_days AND :observ_date;
    END IF;
    return(v_accum);
  • Transact SQL has a set of functions called Window functions meant to deal with problems of the kind you deal with.

    Here is a good link to read: https://www.red-gate.com/simple-talk/sql/t-sql-programming/introduction-to-t-sql-window-functions/

    If you need a quick solution, please provide CREATE TABLE with some sample data, explain the problem, provide how output should look, and many people will be able to help you. Chances are not too many people on this site are capable of direct translation from ORACLE to MS SQL.

  • So converting this I am a bit rusty on Oracle, but I think this is going to be similar (although not 100% certain as I have no sample data or expected output) but this looks mostly trivial to convert to something like this:

    DECLARE @v_accum       NUMERIC(22,9);
    BEGIN
    IF (DATEPART(Month,@p_start_date) = '04' OR
    DATEPART(Month,@P_start_date) = '09')
    BEGIN
    SELECT @v_accum = SUM(ROUND(value)), @CP_avg=AVG(ROUND(value))

    FROM num_daily
    WHERE site_code = @p_site_code
    AND shef_code='MWC'
    AND observ_date BETWEEN @p_start_date AND @observ_date;
    END
    ELSE
    BEGIN
    SELECT @v_accum = SUM(ROUND(value)), @CP_avg = AVG(ROUND(value))

    FROM num_daily
    WHERE site_code = @p_site_code
    AND shef_code='MWC'
    AND observ_date BETWEEN @observ_date-@p_num_days AND @observ_date;
    END
    return @v_accum;

    You will need to define the parameters that are in there though such as @p_start_date, @CP_avg, @p_site_code, and @observ_date.  But that should be the SQL Server equivalent to what you posted from Oracle.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

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

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