Home » SQL & PL/SQL » SQL & PL/SQL » Last Value (merged)
Last Value (merged) [message #395456] Wed, 01 April 2009 08:53 Go to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
Hello,

I have a table TEST_DATA and want to calculate the Result-table TEST_RESULT (see example below).
The new field RESULT shall be 1 if a previous value of P1 has been 1 within a active group (ISACTIVE=1). Does anyone know how I can do this with SQL?

Thank you very much for your help,
Stefan


Data-Table:
( ID number,
ISACTIVE number,
P1 number,
)

insert into TEST_DATA values ( 1, null, null);
insert into TEST_DATA values ( 2, 1 , null);
insert into TEST_DATA values ( 3, 1 , null);
insert into TEST_DATA values ( 4, 1 , 1 );
insert into TEST_DATA values ( 5, 1 , 1 );
insert into TEST_DATA values ( 6, 1 , 1 );
insert into TEST_DATA values ( 7, 1 , null);
insert into TEST_DATA values ( 8, 1 , null);
insert into TEST_DATA values ( 9, 1 , 1 );
insert into TEST_DATA values (10, 1 , 1 );
insert into TEST_DATA values (11, null, null);
insert into TEST_DATA values (12, null, null);
insert into TEST_DATA values (13, null, null);
insert into TEST_DATA values (14, 1 , null);
insert into TEST_DATA values (15, 1 , 1 );
insert into TEST_DATA values (16, 1 , 1 );
insert into TEST_DATA values (17, null, null);

Result-Table
create table TEST_RESULT
( ID number,
ISACTIVE number,
P1 number,
)

insert into TEST_RESULT values ( 1, null, null,null);
insert into TEST_RESULT values ( 2, 1 , null,null);
insert into TEST_RESULT values ( 3, 1 , null,null);
insert into TEST_RESULT values ( 4, 1 , 1 ,1 );
insert into TEST_RESULT values ( 5, 1 , 1 ,1 );
insert into TEST_RESULT values ( 6, 1 , 1 ,1 );
insert into TEST_RESULT values ( 7, 1 , null,1 );
insert into TEST_RESULT values ( 8, 1 , null,1 );
insert into TEST_RESULT values ( 9, 1 , 1 ,1 );
insert into TEST_RESULT values (10, 1 , 1 ,1 );
insert into TEST_RESULT values (11, null, null,null);
insert into TEST_RESULT values (12, null, null,null);
insert into TEST_RESULT values (13, null, null,null);
insert into TEST_RESULT values (14, 1 , null,null);
insert into TEST_RESULT values (15, 1 , 1 ,1 );
insert into TEST_RESULT values (16, 1 , 1 ,1 );
insert into TEST_RESULT values (17, null, null,null);
Re: Last Value [message #395460 is a reply to message #395456] Wed, 01 April 2009 08:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at LAG function.

Regards
Michel

Re: Last Value [message #395463 is a reply to message #395456] Wed, 01 April 2009 09:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How many times will you repeat the same topic?
You should better rread the replies instead.

Regards
Michel

[Updated on: Wed, 01 April 2009 09:06]

Report message to a moderator

Re: Last Value (merged) [message #395469 is a reply to message #395456] Wed, 01 April 2009 09:45 Go to previous messageGo to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
Sorry for posting three times, I got an error when I wanted to send the post, that's why.
Re: Last Value [message #395472 is a reply to message #395456] Wed, 01 April 2009 10:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You're going to have to go into some more detail about how to calculate RESULT - I can't make head or tail of it.
Re: Last Value (merged) [message #395473 is a reply to message #395456] Wed, 01 April 2009 10:16 Go to previous messageGo to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
Ok, the field RESULT shall be 1 when ever a previous value of P1 has been 1 (and stays 1 until the end of the group ISACTIVE=1). This rule is valid within a group which means within preceding records with ISACTIVE = 1. When ISACTIVE is null, the rule described above will start again when ISACTIVE = 1 again. I hope this will clarify the calculation.
Thanks for your help, Stefan
Re: Last Value (merged) [message #395475 is a reply to message #395456] Wed, 01 April 2009 10:22 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>previous value of P1
Rows in a table are like balls in a basket & have no inherent order.

How to know the order balls/rows are presented?
Re: Last Value (merged) [message #395484 is a reply to message #395473] Wed, 01 April 2009 10:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In this case, use MAX in its analytical form.

Regards
Michel
Re: Last Value (merged) [message #395672 is a reply to message #395456] Thu, 02 April 2009 03:43 Go to previous messageGo to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
@BlackSwan: The order of the rows is given by the ID
@Michel Cadot: Sorry but I don't know how to solve the problem with the max function.
Re: Last Value (merged) [message #395675 is a reply to message #395473] Thu, 02 April 2009 03:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
In your results, why is the RESULT for id=4 1
The previous P1 (assuming an order by ID) is null, so this seems to go against the rule yuo described
Re: Last Value (merged) [message #395678 is a reply to message #395456] Thu, 02 April 2009 04:05 Go to previous messageGo to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
The RESULT is 1 as soon as the current or a previous value of P1 within the group (ISACTIVE=1) has been 1.
Re: Last Value (merged) [message #395679 is a reply to message #395675] Thu, 02 April 2009 04:13 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Assuming that the results you posted are right, and your explanation is where the problem is, here's a way of doing it.

I'm sure there's an easier way though.
select id,isactive,p1,result
      ,last_value(p1 ignore nulls) over (partition by isactive_grp order by id) calc_result
from (select id,isactive,p1,result
            ,case when nvl(last_value(start_pt ignore nulls) over (order by id),0) > nvl(last_value(end_pt ignore nulls) over (order by id),0) then last_value(start_pt ignore nulls) over (order by id)
                  else null end isactive_grp
      from (select id
                  ,isactive
                  ,p1
                  ,result
                  ,case when isactive is not null and lag(isactive) over (order by id) is null then id  
                        else null end start_pt
                  ,case when isactive is null and lag(isactive) over (order by id) is not null then id 
                        else null end end_pt            
            from   test_result
            )
     )
order by id;
Re: Last Value (merged) [message #395690 is a reply to message #395456] Thu, 02 April 2009 04:55 Go to previous messageGo to next message
laknar
Messages: 22
Registered: February 2009
Junior Member
INSERT INTO TEST_RESULT 
(select ID,ISACTIVE,P1,
(CASE WHEN P1=1 AND ISACTIVE=1 THEN P1 ELSE NULL END) AS RESULT  
FROM TEST_DATA 
order by ID)

[Updated on: Thu, 02 April 2009 05:37] by Moderator

Report message to a moderator

Re: Last Value (merged) [message #395743 is a reply to message #395690] Thu, 02 April 2009 09:00 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Sort of, but no. If you look at the expected result and your column calc_result, you might notice a few differences....
SQL> select ID,ISACTIVE,P1,result,
  2  (CASE WHEN P1=1 AND ISACTIVE=1 THEN P1 ELSE NULL END) calc_result
  3  FROM TEST_result
  4  order by ID;

        ID   ISACTIVE         P1     RESULT CALC_RESULT
---------- ---------- ---------- ---------- -----------
         1
         2          1
         3          1
         4          1          1          1           1
         5          1          1          1           1
         6          1          1          1           1
         7          1                     1
         8          1                     1
         9          1          1          1           1
        10          1          1          1           1
        11
        12
        13
        14          1
        15          1          1          1           1
        16          1          1          1           1
        17
Re: Last Value (merged) [message #395749 is a reply to message #395473] Thu, 02 April 2009 09:15 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can do it in 3 steps; the purpose of the first two ones is to group the rows.

1/ Number the first row of each group:
SQL>     select id, isactive, p1,
  2             case 
  3               when nvl(lag(isactive,1,-1) over (order by id),0) != nvl(isactive,0)
  4                 then row_number () over (order by id) 
  5             end rn
  6      from test_data
  7  /
        ID   ISACTIVE         P1         RN
---------- ---------- ---------- ----------
         1                                1
         2          1                     2
         3          1
         4          1          1
         5          1          1
         6          1          1
         7          1
         8          1
         9          1          1
        10          1          1
        11                               11
        12
        13
        14          1                    14
        15          1          1
        16          1          1
        17                               17

2/ Propagate this number to each row of the group, this is the group number:
SQL> with 
  2    data as (
  3      select id, isactive, p1,
  4             case 
  5               when nvl(lag(isactive,1,-1) over (order by id),0) != nvl(isactive,0)
  6                 then row_number () over (order by id) 
  7             end rn
  8      from test_data
  9    )
 10       select id, isactive, p1, max(rn) over (order by id) grp
 11       from data
 12  /
        ID   ISACTIVE         P1        GRP
---------- ---------- ---------- ----------
         1                                1
         2          1                     2
         3          1                     2
         4          1          1          2
         5          1          1          2
         6          1          1          2
         7          1                     2
         8          1                     2
         9          1          1          2
        10          1          1          2
        11                               11
        12                               11
        13                               11
        14          1                    14
        15          1          1         14
        16          1          1         14
        17                               17

3/ For each group set result to 1 as soon as you encounter a value in P1 and the group is active:
SQL> with 
  2    data as (
  3      select id, isactive, p1,
  4             case 
  5               when nvl(lag(isactive,1,-1) over (order by id),0) != nvl(isactive,0)
  6                 then row_number () over (order by id) 
  7             end rn
  8      from test_data
  9    ),
 10    grouped as (
 11       select id, isactive, p1, max(rn) over (order by id) grp
 12       from data
 13    )
 14  select id, isactive, p1,
 15         nvl2(max(decode(isactive,1,p1)) over (partition by grp order by id), 1, null) result
 16  from grouped
 17  order by id
 18  /
        ID   ISACTIVE         P1     RESULT
---------- ---------- ---------- ----------
         1
         2          1
         3          1
         4          1          1          1
         5          1          1          1
         6          1          1          1
         7          1                     1
         8          1                     1
         9          1          1          1
        10          1          1          1
        11
        12
        13
        14          1
        15          1          1          1
        16          1          1          1
        17

Regards
Michel

[Updated on: Thu, 02 April 2009 09:18]

Report message to a moderator

Previous Topic: Populating data into table type record and returning from procedure
Next Topic: issue with join condition while using in the corelated sub query
Goto Forum:
  


Current Time: Mon Dec 05 19:22:09 CST 2016

Total time taken to generate the page: 0.11692 seconds