Query

Database
Enthusiast

Query

Hi,

I have below data,

Name    Sum

A           10

B           8

C           6

D           4

From above i need to derive following:

Name    Sum      Diff

A           10         0

B           8           10-8

C           6           10-6

D           4           10-4

Thanks,

A

8 REPLIES
Enthusiast

Re: Query

If your TD version is 14, then first value can be used.

SEL NAME,DIFF,FIRST_VALUE(DIFF) OVER(ORDER BY NAME ASC ROWS UNBOUNDED PRECEDING)-DIFF FROM T1

Re: Query

I cant get this. Can you please expalin in detail.

Enthusiast

Re: Query

TD version is 12 

I need to calculate 'Diff' column using column 'Sum' for example - row 2 should B  8  2 (10-8) and row 3 should be C   6   4 (10-6)   

Enthusiast

Re: Query

SEL NAME,SUM,(SEL SUM FROM (SEL SUM FROM P1 QUALIFY ROW_NUMBER() OVER( ORDER BY NAME ASC)=1) A) - SUM

FROM P1

This sould work.

Thanks

Yuva

Enthusiast

Re: Query

SEL NAME,SUM,FIRST_VALUE(SUM) OVER(ORDER BY NAME ASC ROWS UNBOUNDED PRECEDING)-SUM FROM T1 ..This is the correct one..

@adityapatel

 

Enthusiast

Re: Query

Thanks Yuva !!!

If TD 14 then it would be easier to use First Value......

Senior Apprentice

Re: Query

FIRST_VALUE cam be replaced by nested OLAP-functions:

SELECT name, sum,
MIN(min_sum) OVER () - sum
FROM
(
SELECT name, sum,
CASE WHEN ROW_NUMBER() OVER (ORDER BY name) = 1 THEN sum END AS min_sum
FROM vt
) AS dt
Enthusiast

Re: Query

Thanks dnoeth !!!