SQL query needed---Urgent

Database
Enthusiast

SQL query needed---Urgent

Hi All,

I have source data as

Month         x                   y

Aug 14     3000            2000

Sep 14      4000           3000

Oct 14      5000            44444

I am looking to create a calculated field that will return a percentage change in volume when compared to the previous month (for each column). 

3 REPLIES
New Member

Re: SQL query needed---Urgent

Hi, there are many ways to get yout answer.

There is one:

/*given:

MONTH         x                   y

Aug 14     3000            2000

Sep 14      4000           3000

Oct 14      5000            44444*/

CREATE VOLATILE MULTISET TABLE Test1

(

Dat DATE FORMAT 'MMM-YYYY'

,x INTEGER

, y INTEGER

)

PRIMARY INDEX (dat)

ON COMMIT PRESERVE ROWS;

INSERT INTO test1

(Dat,x,y)

SELECT

    DAT, X, Y

FROM

    (

        SELECT '2014-08-01'(DATE) AS Dat, 3000 AS x, 2000 AS y FROM (SELECT NULL AS a) B

        UNION ALL

        SELECT '2014-09-01'(DATE) AS Dat, 4000 AS x, 3000 AS y FROM (SELECT NULL AS a) B

        UNION ALL

        SELECT'2014-10-01'(DATE) AS Dat, 5000 AS x, 4444 AS y FROM (SELECT NULL AS a) B

    ) C

;

SELECT

    CAST(DAT AS CHAR(8)) AS Dat -- Cast to Char needed becaus my TD-Driver in windows change to YYYY-MM-DD

    , X

    , Y

    , SUM(x) OVER (ORDER BY DAT ASC) AS Column_sum_X

    , SUM(Y) OVER (ORDER BY DAT ASC) AS Column_sum_Y

    , SUM(x) OVER (ORDER BY DAT ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS moving_sum_x

    , SUM(y) OVER (ORDER BY DAT ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS moving_sum_y

    , COALESCE(SUM(x) OVER (ORDER BY DAT ASC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING), x) AS x_before -- coalesce is neede to prevent null in first row

    , COALESCE(SUM(y) OVER (ORDER BY DAT ASC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING),y) AS y_before -- coalesce is neede to prevent null in first row

    , x - x_before AS delta_x

    , y - y_before AS delta_y

    , CAST(CAST(delta_x AS DECIMAL(18,2)) / x * 100.000 AS DECIMAL(7,3)) AS change_pct_x

    , CAST(CAST(delta_y  AS DECIMAL(18,2))/ y * 100.000 AS DECIMAL(7,3)) AS change_pct_y

FROM Test1

ORDER BY DAT ASC;

Enthusiast

Re: SQL query needed---Urgent

Hi Hope this helps.

create table comparedata (
mnth date format 'mmmyy',
x DECIMAL(18,2),
y DECIMAL(18,2)
) primary index (mnth);

INSERT INTO comparedata values ('Aug14', 3000, 2000);
INSERT INTO comparedata values ('Sep14', 4000, 3000);
INSERT INTO comparedata values ('Oct14', 5000, 44444);

select
mnth,
x,
y,
(x - prev_val_x)/prev_val_x * 100 as percent_chng_x,
(y - prev_val_y)/prev_val_y * 100 as percent_chng_y
from (
select
mnth,
x,
y,
coalesce(min(x) over(order by mnth rows between 1 preceding and 1 preceding),x) as prev_val_x,
coalesce(min(y) over(order by mnth rows between 1 preceding and 1 preceding),y) as prev_val_y
from comparedata ) T1
;
Enthusiast

Re: SQL query needed---Urgent

Thanks all :) I wil try both codes...Nice to have SQL gurus....:) world lack them