Re: Help with query

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Tue, 23 Jan 2007 23:44:14 +0100
Message-ID: <45B68FBE.3080003@gmail.com>

AZNewsh schrieb:
> I am going to attempt to simplify what is required which might even hel
> me get my head around this:
>
> Simplified table giving only columns that matter:
>
> ID (Varchar2)
> Year (Varchar2)
> Code (varchar2)
> Date (Date)
> Action (Varchar2)
>
> OK, here is what I need:
>
> I need to
> SELECT id FROM TABLE
> WHERE year = '2007'
> AND
>
> This is where sql ends and elongated description begins:
>
> Each id can have several rows, each with eiher different 'code' or
> 'action' or 'date' as in example below:
>
> ID-------Year--------Code------Date-------Action
> 1 2007 1234 1/1/07 A
> 1 2007 1234 1/2/07 B
> 1 2007 1234 1/3/07 C
> 1 2007 4321 1/6/07 A
> 1 2007 4321 1/7/07 C
> 1 2007 4321 1/8/07 B
>
>
> What I am looking for is:
>
> I want to identify where ACTION C occurs before Action B where the code
> is the same, in the example above only line 5 would satisfy that
> criteria since ID is the same, year is 2007 code was the same and the
> action C occured on a date before action B
>
> Thanks in advance...
>

It is not quite clear, what are your conditions. Well, i'll consider two possibilities,
first one - actions are naturally ordered and C before B means this order is violated:

SQL> with t as(

   2 select '1' id_col,'2007' year_col,'1234' code_col,'1/1/07' date_col,'A' action_col from dual union all

   3          select '1','2007','1234','1/2/07','B' from dual union all
   4          select '1','2007','1234','1/3/07','C' from dual union all
   5          select '1','2007','4321','1/6/07','A' from dual union all
   6          select '1','2007','4321','1/7/07','C' from dual union all
   7          select '1','2007','4321','1/8/07','B' from dual),
   8  t1 as(
   9          select 

id_col,year_col,code_col,to_date(date_col,'MM/DD/YY') as date_col,action_col from t),
  10 t2 as (

  11          select t1.*,
  12          row_number() over(partition by id_col,year_col,code_col 
order by date_col) order_by_date,
  13          row_number() over(partition by id_col,year_col,code_col 
order by action_col) order_by_action
  14          from t1

  15 )
  16 select id_col,year_col,code_col,date_col,action_col   17 from t2
  18 where order_by_date!=order_by_action and action_col='C';

I YEAR CODE DATE_COL A

- ---- ---- ------------------ -
1 2007 4321 07-JAN-07          C

Second one - we take it literally, the only rows of interest are those, there C occurs immediately before B:

SQL> with t as(

   2 select '1' id_col,'2007' year_col,'1234' code_col,'1/1/07' date_col,'A' action_col from dual union all

   3          select '1','2007','1234','1/2/07','B' from dual union all
   4          select '1','2007','1234','1/3/07','C' from dual union all
   5          select '1','2007','4321','1/6/07','A' from dual union all
   6          select '1','2007','4321','1/7/07','C' from dual union all
   7          select '1','2007','4321','1/8/07','B' from dual),
   8  t1 as(
   9          select 

id_col,year_col,code_col,to_date(date_col,'MM/DD/YY') as date_col,action_col from t),
  10 t2 as (

  11          select t1.*,
  12          lead(action_col) over(partition by 
id_col,year_col,code_col order by date_col) following_action
  13          from t1

  14 )
  15 select id_col,year_col,code_col,date_col,action_col   16 from t2
  17 where following_action='B' and action_col='C';

I YEAR CODE DATE_COL A

- ---- ---- ------------------ -
1 2007 4321 07-JAN-07          C

Best regards

Maxim
PS feel free to name your columns somewhat not so confusing as "DATE" Received on Tue Jan 23 2007 - 16:44:14 CST