On Nov 25, 9:17 pm, Harish <[EMAIL PROTECTED]> wrote:
> There is a table which tracks changes made to an account
>
> Account
> Number Action Date
> -----------------------------------------------------------
> 1234 Open 11/01
> 1234 Upgrade 11/02
> 6666 Open 11/04
> 1234 Close 11/05
> 6666 Upgrade 11/05
>
> Whats the query to get all accounts not closed(still open) on a given
> month?
> In this case for month 11 it should return 6666
>
> this table can have millions of entry on a given month.
>
> help!!!
On the face of it this appears to be a simple NOT IN/NOT EXISTS
query, but the reopened accounts makes that not possible. I'm hoping
you're running at least 9.2.0.8:
SQL> --
SQL> -- Create table
SQL> --
SQL> create table acct_action(
2 account_nbr number,
3 action varchar2(20),
4 act_date date
5 );
Table created.
SQL>
SQL> --
SQL> -- Your sample data
SQL> --
SQL> insert all
2 into acct_action
3 values (1234,'Open',to_date('11/01', 'MM/DD'))
4 into acct_action
5 values (1234,'Upgrade',to_date('11/02','MM/DD'))
6 into acct_action
7 values (6666,'Open',to_date('11/04','MM/DD'))
8 into acct_action
9 values (1234,'Close',to_date('11/05','MM/DD'))
10 into acct_action
11 values (6666,'Upgrade',to_date('11/05','MM/DD'))
12 select * From dual
13 /
5 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> --
SQL> -- Your desired result set
SQL> --
SQL> with a as(
2 select account_nbr, action, act_date, row_number() over
(partition by account_nbr order by account_nbr, act_date, action desc)
r_num
3 from acct_action)
4 select a.account_nbr
5 from a
6 where (a.account_nbr, a.r_num) in (select account_nbr, max(r_num)
from a group by account_nbr minus select account_nbr, r_num from a
where action = 'Close')
7 /
ACCOUNT_NBR
-----------
6666
SQL>
SQL> truncate table acct_action;
Table truncated.
SQL>
SQL> --
SQL> -- Modified data, with a reopened account
SQL> --
SQL> insert all
2 into acct_action
3 values (1234,'Open',to_date('11/01', 'MM/DD'))
4 into acct_action
5 values (1234,'Upgrade',to_date('11/02','MM/DD'))
6 into acct_action
7 values (6666,'Open',to_date('11/04','MM/DD'))
8 into acct_action
9 values (1234,'Close',to_date('11/05','MM/DD'))
10 into acct_action
11 values (6666,'Upgrade',to_date('11/05','MM/DD'))
12 into acct_action
13 values (7734,'Open',to_date('11/01', 'MM/DD'))
14 into acct_action
15 values (7734,'Upgrade',to_date('11/07', 'MM/DD'))
16 into acct_action
17 values (7836,'Open',to_date('11/11', 'MM/DD'))
18 into acct_action
19 values (7734,'Close',to_date('11/09', 'MM/DD'))
20 into acct_action
21 values (7734,'Open',to_date('11/11', 'MM/DD'))
22 into acct_action
23 values (7836,'Upgrade',to_date('11/11', 'MM/DD'))
24 select * From dual
25 /
11 rows created.
SQL>
SQL> --
SQL> -- Results, which are correct
SQL> --
SQL> with a as(
2 select account_nbr, action, act_date, row_number() over
(partition by account_nbr order by account_nbr, act_date, action desc)
r_num
3 from acct_action)
4 select a.account_nbr
5 from a
6 where (a.account_nbr, a.r_num) in (select account_nbr, max(r_num)
from a group by account_nbr minus select account_nbr, r_num from a
where action = 'Close')
7 /
ACCOUNT_NBR
-----------
6666
7734
7836
SQL>
David Fitzjarrell
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
[EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
