All Forums Database
krishna1985 13 posts Joined 08/16
15 Aug 2016
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). 

Regards, Hari Krishna
michael.hoppi 1 post Joined 03/10
16 Aug 2016

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;
 

sk8s3i 35 posts Joined 06/13
16 Aug 2016

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
;

 

-Thanks Shardul

krishna1985 13 posts Joined 08/16
16 Aug 2016

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

Regards,
Hari Krishna

You must sign in to leave a comment.