All Forums Database
Abhi93 11 posts Joined 01/08
12 Mar 2015
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
 

yuvaevergreen 93 posts Joined 07/09
13 Mar 2015

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
 

adityapatel 2 posts Joined 02/15
13 Mar 2015

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

Abhi93 11 posts Joined 01/08
13 Mar 2015

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)   

yuvaevergreen 93 posts Joined 07/09
13 Mar 2015

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
 
 

yuvaevergreen 93 posts Joined 07/09
13 Mar 2015

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

Abhi93 11 posts Joined 01/08
15 Mar 2015

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

dnoeth 4628 posts Joined 11/04
16 Mar 2015

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

Abhi93 11 posts Joined 01/08
15 Sep 2015

Thanks dnoeth !!!

You must sign in to leave a comment.