Running accumulation for past 30 days

Question

Running accumulation for past 30 days

SSCarpal Tunnel

Points: 4476

June 30, 2020 at 4:28 pm

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

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

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

Zidar SSChasing Mays Points: 651 More actions · Answer 1

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: 24800 More actions · Answer 2

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.