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

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

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

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....m-db2/200805/1

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

>> if MON is not Null in 2nd row ten Tue Is not null In 1st - previous <<
You still don't understand the meaning of "a table is an _unordered_
set of tuples".
You can't say ist, 2nd, 3rd and so on (and previous, following),
unless you specify of which order.
And you can't say order of rows inserted(or sequence of loading data,
etc.).
Anyhow, I added column n to specify sequence in the following example.
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.mon)) || ' - ' || RTRIM(CHAR(T.tue)) 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
;

Usually, the function "ROWNUMBER() OVER()" seems giving the sequental
row number for rows retrieved with the order of SELECTed.
But the order is not _guaranteed_ without specifying OVER(... ORDER
BY ...).
------------------------------ Commands Entered
------------------------------
WITH
Data_sample(tue, mon) AS (
VALUES
( 2, CAST(NULL AS INTEGER) )
,( CAST(NULL AS INTEGER), 25 )
,( 27, CAST(NULL AS INTEGER) )
,( CAST(NULL AS INTEGER), 48 )
,( 50, CAST(NULL AS INTEGER) )
,( CAST(NULL AS INTEGER), 78 )
,( 3, CAST(NULL AS INTEGER) )
,( CAST(NULL AS INTEGER), 40 )
,( 42, CAST(NULL AS INTEGER) )
,( CAST(NULL AS INTEGER), 62 )
,( 65, CAST(NULL AS INTEGER) )
,( CAST(NULL AS INTEGER), 85 )
,( 4, CAST(NULL AS INTEGER) )
,( CAST(NULL AS INTEGER), 40 )
,( 42, CAST(NULL AS INTEGER) )
,( CAST(NULL AS INTEGER), 62 )
,( 68, CAST(NULL AS INTEGER) )
,( CAST(NULL AS INTEGER), 92 )
) -- End of sample data
SELECT ROWNUMBER() OVER() AS sequence
, tue, mon
FROM Data_sample;
------------------------------------------------------------------------------

SEQUENCE TUE MON
-------------------- ----------- -----------
1 2 -
2 - 25
3 27 -
4 - 48
5 50 -
6 - 78
7 3 -
8 - 40
9 42 -
10 - 62
11 65 -
12 - 85
13 4 -
14 - 40
15 42 -
16 - 62
17 68 -
18 - 92

18 record(s) selected.

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.mon)) || ' - ' || RTRIM(CHAR(T.tue)) 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.

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.mon)) || ' - ' || RTRIM(CHAR(T.tue)) 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....m-db2/200805/1