CODEWITHSUNDEEP
sqloracle-database
Dennis
Dennis

Reputation: 23

how can I select only rows that are not within a week of another type of row?

I have a data table that essentially looks like this:

Employee ID   ACTION    Action DATE
1             SIC       12/10/15 
1             CSL       11/10/15
2             SIC       12/10/15 
2             CSL       11/10/15
1             CSL       10/22/15
2             CSL       10/22/15
1             SAC       10/21/15
2             SAC       10/21/15

I am trying to select every row with a CSL action that is not within 7 days of an SIC or SAC action row. The output I desire is this:

Employee ID   ACTION    Action DATE
    1             CSL       11/10/15 
    2             CSL       11/10/15

What I have tried is joining the table to itself like this:

Select *
From 
TableName A join TableName B on Keyfield A = Keyfield B
Where 
A.Action = 'CSL' and
B.Action in ('SIC','SAC') and 
A.Action_DT not between B.Action_DT-7 and B.Action_DT+7

But, that returns the undesirable 10/22/15 row, I am assuming because it is more than 7 days from the 12/10/15 SIC row.

Any help would be greatly appreciated.

Upvotes: 2

Views: 50

Answers (4)

peter.hrasko.sk
peter.hrasko.sk

Reputation: 4141

You may rather need an self-antijoin instead of self-innerjoin. Try it like this:

select *
from TableName A
where A.action = 'CSL'
    and not exists (
        select 1
        from TableName B
        where B.action in ('SIC','SAC')
            and B.key_field = A.key_field
            and A.action_dt between B.action_dt-7 and B.action_dt+7
    );

Note: I did not put any effort into replicating your input data, so this query is only hypothetical and may not even work. You can try it anyway.

Upvotes: 2

MT0
MT0

Reputation: 168771

Using only a single table scan (no self join):

SELECT Employee_ID, Action, Action_Date
FROM   (
  SELECT t.*,
         COUNT( CASE WHEN action IN ( 'SIC', 'SAC' ) THEN 1 END )
           OVER ( ORDER BY Action_Date RANGE BETWEEN 7 PRECEDING AND 7 FOLLOWING )
           AS cnt
  FROM   Table_name t
)
WHERE  Action = 'CSL'
AND    cnt    = 0;

Upvotes: 2

Boneist
Boneist

Reputation: 23588

An alternative to doing a join is to use an analytic function with a windowing clause - here, I have used the last_value analytic function:

with sample_data as (select 1 employee_id, 'SIC' action, to_date('12/10/2015', 'mm/dd/yyyy') action_date from dual union all
                     select 1 employee_id, 'CSL' action, to_date('11/10/2015', 'mm/dd/yyyy') action_date from dual union all
                     select 2 employee_id, 'SIC' action, to_date('12/10/2015', 'mm/dd/yyyy') action_date from dual union all
                     select 2 employee_id, 'CSL' action, to_date('11/10/2015', 'mm/dd/yyyy') action_date from dual union all
                     select 1 employee_id, 'CSL' action, to_date('10/22/2015', 'mm/dd/yyyy') action_date from dual union all
                     select 2 employee_id, 'CSL' action, to_date('10/22/2015', 'mm/dd/yyyy') action_date from dual union all
                     select 1 employee_id, 'SAC' action, to_date('10/21/2015', 'mm/dd/yyyy') action_date from dual union all
                     select 2 employee_id, 'SAC' action, to_date('10/21/2015', 'mm/dd/yyyy') action_date from dual)
select employee_id,
       action,
       action_date
from   (select employee_id,
               action,
               action_date,
               case when action = 'CSL'
                         then last_value(case when action in ('SIC', 'SAC') then 1 end) ignore nulls over (order by action_date
                                                                                                           range between 7 preceding and 7 following)
               end last_val
        from   sample_data)
where  action = 'CSL'
and    last_val is null;

EMPLOYEE_ID ACTION ACTION_DATE
----------- ------ -----------
          1 CSL    11/10/2015 
          2 CSL    11/10/2015

Upvotes: 1

Bohemian
Bohemian

Reputation: 425448

Outer join the table to itself matching unwanted results, but filter out hits:

select A.*
from TableName A
left join TableName B on A.Action != B.Action
  and A.Action_DT between B.Action_DT-7 and B.Action_DT+7
where A.Action = 'CSL'
and B.Action is null

A successful join means we don't want the row - we want the missed joins, which have all nulls for the joined columns, which is why a test for null for any joined column in the where clause returns what we want.

Upvotes: 2

Related Questions