How to calculate difference between 2 columns excluding nulls

[lenygold via DBMonster.com]

Here is my input table:

TUE MON
----------- -----------
2 -
- 25
27 -
- 48
50 -
- 78
3 -
- 40
42 -
- 62
65 -
- 85
4 -
- 40
42 -
- 62
68 -
- 92
What is the best way to calculate difference between not NULL MON and
previous not NULL TUE

25 - 2
48 - 27
........
62 - 40
92 - 68

I did it by creating separated tables for MON and TUE AND THEN JOIN BASED ON
ROW NUMBER
Any more creative solutions.
Thank's in advance
Leny G.

--
Message posted via http://www.dbmonster.com

[lenygold via DBMonster.com]

Re: How to calculate difference between 2 columns excluding nulls

UPS IT SHOULD BE 62 - 42 NOT 40

lenygold wrote:

>Here is my input table:
>
TUE MON
>----------- -----------
2 -
- 25
27 -
- 48
50 -
- 78
3 -
- 40
42 -
- 62
65 -
- 85
4 -
- 40
42 -
- 62
68 -
- 92
>What is the best way to calculate difference between not NULL MON and
>previous not NULL TUE
>
>25 - 2
>48 - 27
>........
>62 - 42
>92 - 68
>
>I did it by creating separated tables for MON and TUE AND THEN JOIN BASED ON
>ROW NUMBER
>Any more creative solutions.
>Thank's in advance
>Leny G.

--
Message posted via http://www.dbmonster.com

[The Boss]

Re: How to calculate difference between 2 columns excluding nulls

lenygold via DBMonster.com wrote:

Here is my input table:
>
TUE MON
----------- -----------
2 -
- 25
27 -
- 48
50 -
- 78
3 -
- 40
42 -
- 62
65 -
- 85
4 -
- 40
42 -
- 62
68 -
- 92
What is the best way to calculate difference between not NULL MON and
previous not NULL TUE

Define "previous".
Remember a table is an _unordered_ set of tuples.

--
Jeroen

[lenygold via DBMonster.com]

Re: How to calculate difference between 2 columns excluding nulls

if MON is not Null in 2nd row ten Tue Is not null In 1st - previous
MON is not Null is 4th position, Tue is not null in 3rd -previous

The Boss wrote:

>Here is my input table:
>>

>[quoted text clipped - 20 lines]

>What is the best way to calculate difference between not NULL MON and
>previous not NULL TUE

>
>Define "previous".
>Remember a table is an _unordered_ set of tuples.
>

--
Message posted via DBMonster.com

http://www.dbmonster.com/Uwe/Forums.aspx/ibm-db2/200805/1

[Serge Rielau]

Re: How to calculate difference between 2 columns excluding nulls

lenygold via DBMonster.com wrote:

if MON is not Null in 2nd row ten Tue Is not null In 1st - previous
MON is not Null is 4th position, Tue is not null in 3rd -previous
>
The Boss wrote:

>>Here is my input table:
>>>

>[quoted text clipped - 20 lines]

>>What is the best way to calculate difference between not NULL MON and
>>previous not NULL TUE

>Define "previous".
>Remember a table is an _unordered_ set of tuples.
>>

>

OK, first I would merge the two columns into one with COALESCE().
Then use two OLAP functions (e.g. MAX) where one only looks at the
current row and one looks at the preceding row and subtracts them.

Easy as PI ;-)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Tonkuma]

Re: How to calculate difference between 2 columns excluding nulls

I forgot to copy the result:
WITH
Data_sample(n, tue, mon) AS (
VALUES
( 1, 2, CAST(NULL AS INTEGER) )
,( 2, CAST(NULL AS INTEGER), 25 )
,( 3, 27, CAST(NULL AS INTEGER) )
,( 4, CAST(NULL AS INTEGER), 48 )
,( 5, 50, CAST(NULL AS INTEGER) )
,( 6, CAST(NULL AS INTEGER), 78 )
,( 7, 3, CAST(NULL AS INTEGER) )
,( 8, CAST(NULL AS INTEGER), 40 )
,( 9, 42, CAST(NULL AS INTEGER) )
,(10, CAST(NULL AS INTEGER), 62 )
,(11, 65, CAST(NULL AS INTEGER) )
,(12, CAST(NULL AS INTEGER), 85 )
,(13, 4, CAST(NULL AS INTEGER) )
,(14, CAST(NULL AS INTEGER), 40 )
,(15, 42, CAST(NULL AS INTEGER) )
,(16, CAST(NULL AS INTEGER), 62 )
,(17, 68, CAST(NULL AS INTEGER) )
,(18, CAST(NULL AS INTEGER), 92 )
) -- End of sample data
SELECT M.n
, M.mon - T.tue AS difference
, RTRIM(CHAR(M.mo n)) || ' - ' || RTRIM(CHAR(T.tu e)) AS expression
FROM Data_sample M
INNER JOIN
Data_sample T
ON M.mon IS NOT NULL
AND T.n = M.n - 1 -- T is previous of M
AND T.tue IS NOT NULL -- Redundant predicate
;
------------------------------------------------------------------------------

N DIFFERENCE EXPRESSION
----------- ----------- -------------------------
2 23 25 - 2
4 21 48 - 27
6 28 78 - 50
8 37 40 - 3
10 20 62 - 42
12 20 85 - 65
14 36 40 - 4
16 20 62 - 42
18 24 92 - 68

9 record(s) selected.

[lenygold via DBMonster.com]

Re: How to calculate difference between 2 columns excluding nulls

Thank You very much Tonkuma

Tonkuma wrote:

>I forgot to copy the result:
>WITH
Data_sample(n, tue, mon) AS (
>VALUES
( 1, 2, CAST(NULL AS INTEGER) )
>,( 2, CAST(NULL AS INTEGER), 25 )
>,( 3, 27, CAST(NULL AS INTEGER) )
>,( 4, CAST(NULL AS INTEGER), 48 )
>,( 5, 50, CAST(NULL AS INTEGER) )
>,( 6, CAST(NULL AS INTEGER), 78 )
>,( 7, 3, CAST(NULL AS INTEGER) )
>,( 8, CAST(NULL AS INTEGER), 40 )
>,( 9, 42, CAST(NULL AS INTEGER) )
>,(10, CAST(NULL AS INTEGER), 62 )
>,(11, 65, CAST(NULL AS INTEGER) )
>,(12, CAST(NULL AS INTEGER), 85 )
>,(13, 4, CAST(NULL AS INTEGER) )
>,(14, CAST(NULL AS INTEGER), 40 )
>,(15, 42, CAST(NULL AS INTEGER) )
>,(16, CAST(NULL AS INTEGER), 62 )
>,(17, 68, CAST(NULL AS INTEGER) )
>,(18, CAST(NULL AS INTEGER), 92 )
>) -- End of sample data
>SELECT M.n
, M.mon - T.tue AS difference
, RTRIM(CHAR(M.mo n)) || ' - ' || RTRIM(CHAR(T.tu e)) AS expression
FROM Data_sample M
INNER JOIN
Data_sample T
ON M.mon IS NOT NULL
AND T.n = M.n - 1 -- T is previous of M
AND T.tue IS NOT NULL -- Redundant predicate
>;
>------------------------------------------------------------------------------
>
>N DIFFERENCE EXPRESSION
>----------- ----------- -------------------------
2 23 25 - 2
4 21 48 - 27
6 28 78 - 50
8 37 40 - 3
10 20 62 - 42
12 20 85 - 65
14 36 40 - 4
16 20 62 - 42
18 24 92 - 68
>
9 record(s) selected.

--
Message posted via DBMonster.com

http://www.dbmonster.com/Uwe/Forums.aspx/ibm-db2/200805/1