Running accumulation for past 30 days

  • cbrammer1219

    SSCarpal Tunnel

    Points: 4459

    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);
  • Zidar

    SSChasing Mays

    Points: 651

    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.

  • Mr. Brian Gale

    SSC-Insane

    Points: 22932

    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.

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

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