Solved

Oracle - Return Accounts not present for 10 days Continued

Posted on 2014-04-01
4
253 Views
Last Modified: 2014-07-15
Good evening, This is a follow up issue to my original question in regards to returning accounts not present for 10 days. http://www.experts-exchange.com/Database/Oracle/Q_28386607.html

Guy Hengel [angelIII / a3]

select a.*, t.transaction_date
  from account_table a
  join transaction_table t
    on t.accountnumber = a.accountnumber
   and t.transaction_date < trunc(sysdate -10 )
  where not exists ( select null from transaction_table n
                                  where n.accountnumber = a.accountnumber
                                     and n.transaction_date > t.transaction_date
                                     and n.transaction_date <= t.transaction_date + 10

I’ve had a new requirement added to where i must first get a list of accounts that are present in the account table for 10 consecutive days prior to joining to the transaction table to see if the account is present in the past 10 days.

Data is not loaded to the tables on sundays so all sundays have to be omitted from the 10 consecutive days logic from the accounts table.

Thanks for your assistance!
0
Comment
Question by:cottenmouth
  • 2
4 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 39971148
please provide sample data and expected results
0
 

Author Comment

by:cottenmouth
ID: 39972155
Oracle Ver: version 11.1.0.7.0


Account Table:
Column Name      Data Type
PROCESS_DT              DATE
ACCOUNT                   VARCHAR2 (10 Byte)
JOB                              VARCHAR2 (64 Byte)

Sample Data:
PROCESS_DT  ACCOUNT          JOB
4/1/2014                   0000000012     JOB_1
4/1/2014             0000000013     JOB_2

Transaction Table
Column Name              Data Type
PROCESS_DT                      DATE
ACCOUNT_NUMBER         VARCHAR2 (256 Byte)
JOB_NAME                         VARCHAR2 (255 Byte)

Sample Data:
PROCESS_DT      ACCOUNT_NUMBER   JOB_NAME
    4/1/2014             0000000012                JOB_1
    4/1/2014             0000000013                JOB_2

Expected results is to only show accounts that exist in the accounts table  for 10 consecutive days omitting sundays from the count of consecutive days that do not exist  in the transaction table for the previous 10 days.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39972167
Your sample data seems insufficient to demonstrate what you are looking for

And by expected results I meant for you to "show" rather than "describe"

Doing that removes ambiguity.

You provide data, I load it, write a query and if my results don't match what you posted, then my query is not correct and I can keep trying without needing a lot of back and forth
0
 
LVL 13

Accepted Solution

by:
Alexander Eßer [Alex140181] earned 500 total points
ID: 39973837
I "just" played around a bit and I'd come up with following. You might want to give it a shot ;-)

Table data/setup:
prompt PL/SQL Developer import file
prompt Created on Donnerstag, 3. April 2014 by Alex
set feedback off
set define off
prompt Dropping ACCOUNT...
drop table ACCOUNT cascade constraints;
prompt Dropping TRANSACTION...
drop table TRANSACTION cascade constraints;
prompt Creating ACCOUNT...
create table ACCOUNT
(
  process_dt DATE,
  account    VARCHAR2(10),
  job        VARCHAR2(64)
)
tablespace APP
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

prompt Creating TRANSACTION...
create table TRANSACTION
(
  process_dt     DATE,
  account_number VARCHAR2(256),
  job_name       VARCHAR2(255)
)
tablespace APP
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

prompt Disabling triggers for ACCOUNT...
alter table ACCOUNT disable all triggers;
prompt Disabling triggers for TRANSACTION...
alter table TRANSACTION disable all triggers;
prompt Loading ACCOUNT...
insert into ACCOUNT (process_dt, account, job)
values (to_date('21-04-2014', 'dd-mm-yyyy'), '0000000012', 'JOB_21');
insert into ACCOUNT (process_dt, account, job)
values (to_date('01-04-2014', 'dd-mm-yyyy'), '0000000012', 'JOB_1');
insert into ACCOUNT (process_dt, account, job)
values (to_date('01-04-2014', 'dd-mm-yyyy'), '0000000013', 'JOB_2');
insert into ACCOUNT (process_dt, account, job)
values (to_date('02-04-2014', 'dd-mm-yyyy'), '0000000012', 'JOB_2');
insert into ACCOUNT (process_dt, account, job)
values (to_date('03-04-2014', 'dd-mm-yyyy'), '0000000012', 'JOB_3');
insert into ACCOUNT (process_dt, account, job)
values (to_date('04-04-2014', 'dd-mm-yyyy'), '0000000012', 'JOB_4');
insert into ACCOUNT (process_dt, account, job)
values (to_date('05-04-2014', 'dd-mm-yyyy'), '0000000012', 'JOB_5');
insert into ACCOUNT (process_dt, account, job)
values (to_date('06-04-2014', 'dd-mm-yyyy'), '0000000012', 'JOB_6');
insert into ACCOUNT (process_dt, account, job)
values (to_date('07-04-2014', 'dd-mm-yyyy'), '0000000012', 'JOB_7');
insert into ACCOUNT (process_dt, account, job)
values (to_date('08-04-2014', 'dd-mm-yyyy'), '0000000012', 'JOB_8');
insert into ACCOUNT (process_dt, account, job)
values (to_date('09-04-2014', 'dd-mm-yyyy'), '0000000012', 'JOB_9');
insert into ACCOUNT (process_dt, account, job)
values (to_date('10-04-2014', 'dd-mm-yyyy'), '0000000012', 'JOB_10');
insert into ACCOUNT (process_dt, account, job)
values (to_date('11-04-2014', 'dd-mm-yyyy'), '0000000012', 'JOB_11');
insert into ACCOUNT (process_dt, account, job)
values (to_date('12-04-2014', 'dd-mm-yyyy'), '0000000012', 'JOB_12');
insert into ACCOUNT (process_dt, account, job)
values (to_date('13-04-2014', 'dd-mm-yyyy'), '0000000012', 'JOB_13');
insert into ACCOUNT (process_dt, account, job)
values (to_date('14-04-2014', 'dd-mm-yyyy'), '0000000012', 'JOB_14');
insert into ACCOUNT (process_dt, account, job)
values (to_date('15-04-2014', 'dd-mm-yyyy'), '0000000012', 'JOB_15');
insert into ACCOUNT (process_dt, account, job)
values (to_date('16-04-2014', 'dd-mm-yyyy'), '0000000012', 'JOB_16');
insert into ACCOUNT (process_dt, account, job)
values (to_date('17-04-2014', 'dd-mm-yyyy'), '0000000012', 'JOB_17');
insert into ACCOUNT (process_dt, account, job)
values (to_date('18-04-2014', 'dd-mm-yyyy'), '0000000012', 'JOB_18');
insert into ACCOUNT (process_dt, account, job)
values (to_date('19-04-2014', 'dd-mm-yyyy'), '0000000012', 'JOB_19');
insert into ACCOUNT (process_dt, account, job)
values (to_date('20-04-2014', 'dd-mm-yyyy'), '0000000012', 'JOB_20');
insert into ACCOUNT (process_dt, account, job)
values (to_date('01-04-2014', 'dd-mm-yyyy'), '0000000013', 'JOB_1');
insert into ACCOUNT (process_dt, account, job)
values (to_date('02-04-2014', 'dd-mm-yyyy'), '0000000013', 'JOB_2');
insert into ACCOUNT (process_dt, account, job)
values (to_date('03-04-2014', 'dd-mm-yyyy'), '0000000013', 'JOB_3');
insert into ACCOUNT (process_dt, account, job)
values (to_date('04-04-2014', 'dd-mm-yyyy'), '0000000013', 'JOB_4');
insert into ACCOUNT (process_dt, account, job)
values (to_date('05-04-2014', 'dd-mm-yyyy'), '0000000013', 'JOB_5');
insert into ACCOUNT (process_dt, account, job)
values (to_date('06-04-2014', 'dd-mm-yyyy'), '0000000013', 'JOB_6');
insert into ACCOUNT (process_dt, account, job)
values (to_date('07-04-2014', 'dd-mm-yyyy'), '0000000013', 'JOB_7');
insert into ACCOUNT (process_dt, account, job)
values (to_date('08-04-2014', 'dd-mm-yyyy'), '0000000013', 'JOB_8');
insert into ACCOUNT (process_dt, account, job)
values (to_date('09-04-2014', 'dd-mm-yyyy'), '0000000013', 'JOB_9');
insert into ACCOUNT (process_dt, account, job)
values (to_date('10-04-2014', 'dd-mm-yyyy'), '0000000013', 'JOB_10');
insert into ACCOUNT (process_dt, account, job)
values (to_date('11-04-2014', 'dd-mm-yyyy'), '0000000013', 'JOB_11');
insert into ACCOUNT (process_dt, account, job)
values (to_date('12-04-2014', 'dd-mm-yyyy'), '0000000013', 'JOB_12');
insert into ACCOUNT (process_dt, account, job)
values (to_date('13-04-2014', 'dd-mm-yyyy'), '0000000013', 'JOB_13');
insert into ACCOUNT (process_dt, account, job)
values (to_date('14-04-2014', 'dd-mm-yyyy'), '0000000013', 'JOB_14');
insert into ACCOUNT (process_dt, account, job)
values (to_date('15-04-2014', 'dd-mm-yyyy'), '0000000013', 'JOB_15');
insert into ACCOUNT (process_dt, account, job)
values (to_date('16-04-2014', 'dd-mm-yyyy'), '0000000013', 'JOB_16');
insert into ACCOUNT (process_dt, account, job)
values (to_date('17-04-2014', 'dd-mm-yyyy'), '0000000013', 'JOB_17');
insert into ACCOUNT (process_dt, account, job)
values (to_date('18-04-2014', 'dd-mm-yyyy'), '0000000013', 'JOB_18');
insert into ACCOUNT (process_dt, account, job)
values (to_date('19-04-2014', 'dd-mm-yyyy'), '0000000013', 'JOB_19');
insert into ACCOUNT (process_dt, account, job)
values (to_date('20-04-2014', 'dd-mm-yyyy'), '0000000013', 'JOB_20');
commit;
prompt 42 records loaded
prompt Loading TRANSACTION...
insert into TRANSACTION (process_dt, account_number, job_name)
values (to_date('31-03-2014', 'dd-mm-yyyy'), '0000000012', 'JOB_1');
insert into TRANSACTION (process_dt, account_number, job_name)
values (to_date('01-04-2014', 'dd-mm-yyyy'), '0000000012', 'JOB_1');
insert into TRANSACTION (process_dt, account_number, job_name)
values (to_date('01-04-2014', 'dd-mm-yyyy'), '0000000013', 'JOB_2');
insert into TRANSACTION (process_dt, account_number, job_name)
values (to_date('31-03-2014', 'dd-mm-yyyy'), '0000000013', 'JOB_3');
insert into TRANSACTION (process_dt, account_number, job_name)
values (to_date('30-03-2014', 'dd-mm-yyyy'), '0000000013', 'JOB_4');
insert into TRANSACTION (process_dt, account_number, job_name)
values (to_date('29-03-2014', 'dd-mm-yyyy'), '0000000013', 'JOB_5');
insert into TRANSACTION (process_dt, account_number, job_name)
values (to_date('28-03-2014', 'dd-mm-yyyy'), '0000000013', 'JOB_6');
insert into TRANSACTION (process_dt, account_number, job_name)
values (to_date('27-03-2014', 'dd-mm-yyyy'), '0000000013', 'JOB_7');
insert into TRANSACTION (process_dt, account_number, job_name)
values (to_date('26-03-2014', 'dd-mm-yyyy'), '0000000013', 'JOB_8');
insert into TRANSACTION (process_dt, account_number, job_name)
values (to_date('25-03-2014', 'dd-mm-yyyy'), '0000000013', 'JOB_9');
insert into TRANSACTION (process_dt, account_number, job_name)
values (to_date('24-03-2014', 'dd-mm-yyyy'), '0000000013', 'JOB_10');
insert into TRANSACTION (process_dt, account_number, job_name)
values (to_date('23-03-2014', 'dd-mm-yyyy'), '0000000013', 'JOB_11');
insert into TRANSACTION (process_dt, account_number, job_name)
values (to_date('22-03-2014', 'dd-mm-yyyy'), '0000000013', 'JOB_12');
commit;
prompt 13 records loaded
prompt Enabling triggers for ACCOUNT...
alter table ACCOUNT enable all triggers;
prompt Enabling triggers for TRANSACTION...
alter table TRANSACTION enable all triggers;
set feedback on
set define on
prompt Done.

Open in new window


for testing purposes:
-- for testing purposes

-- "remove" one Saturday from table account for account '0000000012'
update account a
   set a.process_dt = to_date(null)
 where a.process_dt = to_date('29.03.2014', 'dd.mm.yyyy')
   and a.account = '0000000012';

-- "remove" one Sunday from table transaction for account '0000000012'
update account a
   set a.process_dt = to_date(null)
 where a.process_dt = to_date('30.03.2014', 'dd.mm.yyyy')
   and a.account = '0000000012';

-- "remove" one Saturday from table transaction for account '0000000013'
update transaction a
   set a.process_dt = to_date(null)
 where a.process_dt = to_date('29.03.2014', 'dd.mm.yyyy')
   and a.account_number = '0000000013';

-- "remove" one Sunday from table transaction for account '0000000013'
update transaction a
   set a.process_dt = to_date(null)
 where a.process_dt = to_date('30.03.2014', 'dd.mm.yyyy')
   and a.account_number = '0000000013';

Open in new window


And here goes the "final" SQL statement:
with previous_days as
 (select account_number,
         count_all,
         sum(nvl(date_diff, 0))
    from (select trans.account_number,
                 lag(trans.process_dt) over(partition by trans.account_number order by trans.process_dt) prev_date_db,
                 case
                   when to_char(trans.process_dt, 'd') <> '1' then
                    case to_char(trans.process_dt - interval '1' day, 'd')
                      when '7' then
                       trans.process_dt - interval '2' day
                      else
                       trans.process_dt - interval '1' day
                    end
                   else
                    trans.process_dt - interval '1' day
                 end prev_date_calc,
                 case
                   when (lag(trans.process_dt) over(partition by trans.account_number order by trans.process_dt) - (case
                           when to_char(trans.process_dt, 'd') <> '1' then
                            case to_char(trans.process_dt - interval '1' day, 'd')
                              when '7' then
                               trans.process_dt - interval '2' day
                              else
                               trans.process_dt - interval '1' day
                            end
                           else
                            trans.process_dt - interval '1' day
                         end)) < 0 then
                    case
                      when to_char(trans.process_dt - interval '1' day, 'd') =
                           nvl(to_char(lead(trans.process_dt) over(partition by trans.account_number order by trans.process_dt), 'd'), to_char(trans.process_dt - interval '1' day, 'd')) then
                       0
                      else
                       (lag(trans.process_dt) over(partition by trans.account_number order by trans.process_dt) - (case
                          when to_char(trans.process_dt, 'd') <> '1' then
                           case to_char(trans.process_dt - interval '1' day, 'd')
                             when '7' then
                              trans.process_dt - interval '2' day
                             else
                              trans.process_dt - interval '1' day
                           end
                          else
                           trans.process_dt - interval '1' day
                        end))
                    end
                   else
                    (lag(trans.process_dt) over(partition by trans.account_number order by trans.process_dt) - (case
                       when to_char(trans.process_dt, 'd') <> '1' then
                        case to_char(trans.process_dt - interval '1' day, 'd')
                          when '7' then
                           trans.process_dt - interval '2' day
                          else
                           trans.process_dt - interval '1' day
                        end
                       else
                        trans.process_dt - interval '1' day
                     end))
                 end date_diff,
                 min(trans.process_dt) min_dt,
                 row_number() over(partition by account_number order by trans.process_dt desc) rn,
                 count(*) over(partition by account_number order by null) count_all
            from transaction trans
           group by trans.account_number,
                    trans.process_dt
          having max(trans.process_dt) < to_date('01.04.2014', 'dd.mm.yyyy') -- starting date
           order by trans.account_number,
                    trans.process_dt desc)
   where rn >= (10 - (abs((next_day(to_date('01.04.2014', 'dd.mm.yyyy') - interval '10' day, to_char(to_date(6, 'j'), 'day')) -
                          next_day(to_date('01.04.2014', 'dd.mm.yyyy'), to_char(to_date(6, 'j'), 'day'))) / 7))) -- 10 days (over)view
     and count_all >= 10
   group by account_number,
            count_all
  having sum(nvl(date_diff, 0)) = 0),
next_days as
 (select account,
         sum(nvl(date_diff, 0))
    from (select acc.account,
                 lead(acc.process_dt) over(partition by acc.account order by acc.process_dt) next_date_db,
                 case
                   when to_char(acc.process_dt, 'd') <> '7' then
                    case to_char(acc.process_dt + interval '1' day, 'd')
                      when '7' then
                       acc.process_dt + interval '2' day
                      else
                       acc.process_dt + interval '1' day
                    end
                   else
                    acc.process_dt + interval '1' day
                 end next_date_calc,
                 case
                   when (lead(acc.process_dt) over(partition by acc.account order by acc.process_dt) - (case
                           when to_char(acc.process_dt, 'd') <> '7' then
                            case to_char(acc.process_dt + interval '1' day, 'd')
                              when '7' then
                               acc.process_dt + interval '2' day
                              else
                               acc.process_dt + interval '1' day
                            end
                           else
                            acc.process_dt + interval '1' day
                         end)) < 0 then
                    case
                      when to_char(acc.process_dt + interval '1' day, 'd') =
                           nvl(to_char(lead(acc.process_dt) over(partition by acc.account order by acc.process_dt), 'd'), to_char(acc.process_dt + interval '1' day, 'd')) then
                       0
                      else
                       (lead(acc.process_dt) over(partition by acc.account order by acc.process_dt) - (case
                          when to_char(acc.process_dt, 'd') <> '7' then
                           case to_char(acc.process_dt + interval '1' day, 'd')
                             when '7' then
                              acc.process_dt + interval '2' day
                             else
                              acc.process_dt + interval '1' day
                           end
                          else
                           acc.process_dt + interval '1' day
                        end))
                    end
                   else
                    (lead(acc.process_dt) over(partition by acc.account order by acc.process_dt) - (case
                       when to_char(acc.process_dt, 'd') <> '7' then
                        case to_char(acc.process_dt + interval '1' day, 'd')
                          when '7' then
                           acc.process_dt + interval '2' day
                          else
                           acc.process_dt + interval '1' day
                        end
                       else
                        acc.process_dt + interval '1' day
                     end))
                 end date_diff,
                 max(acc.process_dt) max_dt,
                 row_number() over(partition by account order by acc.process_dt) rn,
                 count(*) over(partition by account order by null) count_all
            from account acc
           group by acc.account,
                    acc.process_dt
          having max(acc.process_dt) >= to_date('01.04.2014', 'dd.mm.yyyy') -- starting date
           order by acc.account,
                    acc.process_dt)
   where rn >= (10 - (abs((next_day(to_date('01.04.2014', 'dd.mm.yyyy') - interval '10' day, to_char(to_date(6, 'j'), 'day')) -
                          next_day(to_date('01.04.2014', 'dd.mm.yyyy'), to_char(to_date(6, 'j'), 'day'))) / 7))) -- 10 days (over)view
     and count_all >= 10
   group by account
  having sum(nvl(date_diff, 0)) = 0)
select *
  from next_days a
 where not exists (select 'X'
          from previous_days b
         where b.account_number = a.account);

Open in new window


If you have any questions, please do not hesitate to ask ;-)

This one's kind of a quick shot ;-) Any optimization(s) from any other EE and/or Oracle guru is/are welcome^^
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now