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

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

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

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

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)

SEL NAME,SUM,(SEL SUM FROM (SEL SUM FROM P1 QUALIFY ROW_NUMBER() OVER( ORDER BY NAME ASC)=1) A) - SUMFROM P1This sould work. Thanks Yuva

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

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

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

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

Dieter

Thanks dnoeth !!!

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