# 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);beginIF (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.

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);BEGINIF (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;ENDELSEBEGIN    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;ENDreturn @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)