Home » SQL & PL/SQL » SQL & PL/SQL » SQL for getting list of active accounts (10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production)
SQL for getting list of active accounts [message #361454] Wed, 26 November 2008 08:49 Go to next message
harishmk
Messages: 7
Registered: November 2008
Junior Member
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 
1234     ReOpen  12/02

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
And for month 12, it should return 1234 and 6666
(Closed accounts can be opened again)
this table can have millions of entry on a given month.

help!!!
Re: SQL for getting list of active accounts [message #361456 is a reply to message #361454] Wed, 26 November 2008 09:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a test case: create table and insert statements along with the result you want with these data.

Also post what you already tried.

Regards
Michel
Re: SQL for getting list of active accounts [message #361472 is a reply to message #361454] Wed, 26 November 2008 09:45 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd try something along the lines of
SELECT id, case max_status when 1 then 'Open
                           when 2 then 'Closed'
                           end status
FROM (SELECT id, max(case status when 'Open' then 1
                                 when 'Closed then 2
                                 else 0
                                 end max_status
      FROM   table
      GROUP BY id)

[/CODE]
Re: SQL for getting list of active accounts [message #361487 is a reply to message #361472] Wed, 26 November 2008 11:13 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

JRowbottom wrote on Wed, 26 November 2008 21:15
I'd try something along the lines of
SELECT id, case max_status when 1 then 'Open
                           when 2 then 'Closed'
                           end status
FROM (SELECT id, max(case status when 'Open' then 1
                                 when 'Closed then 2
                                 else 0
                                 end max_status
      FROM   table
      GROUP BY id)

[/CODE]


This may notbe enough as OP says

Quote:
(Closed accounts can be opened again)


Smile
Rajuvan.
Re: SQL for getting list of active accounts [message #361489 is a reply to message #361487] Wed, 26 November 2008 11:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Rajuvan, you didn't understand, this is not a solution, this is a clue.

Regards
Michel
Re: SQL for getting list of active accounts [message #361491 is a reply to message #361454] Wed, 26 November 2008 11:26 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Ok .. if it is so ,

My apologies again .

Sad
Rajuvan
Re: SQL for getting list of active accounts [message #361494 is a reply to message #361454] Wed, 26 November 2008 11:52 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Well, I will be tempted to use the max keep dense_rank logic. It will be something like
select account_number
from
table
where date <= to_date('input_date','date_format')
group by account_number
having max(status) keep(dense_rank first order by date desc) != 'Close'

Regards

Raj
Re: SQL for getting list of active accounts [message #361544 is a reply to message #361454] Wed, 26 November 2008 21:53 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Combination of hints provided by JRowBottom and Rajaram will be better option.

ie Case + Max_keep_dense_rank combination.

Smile
Rajuvan.
Re: SQL for getting list of active accounts [message #361658 is a reply to message #361491] Thu, 27 November 2008 05:01 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Michel is too kind to me I'm afraid - I didn't see the part of the question about re-opening accounts.
Previous Topic: TO_NUMBER
Next Topic: How to store the zip file in oracle table ?
Goto Forum:
  


Current Time: Sat Dec 03 12:13:36 CST 2016

Total time taken to generate the page: 0.10899 seconds