AZNewsh schrieb:
>
> On Jan 23, 3:44 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
>> 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"- Hide quoted text -- Show quoted text -
>
> Thanks for the fast response...
> Apologies for naming the column date, that is not the real name - I was
> trying to simplify, however can see that was a poor choice.
> I think the first suggested solution addresses my needs however with my
> limited SQL which does not include building a table in a query, I am a
> little unsure what part of the query to keep for my table, obviously i
> will need to correct column names. If I wanted to use the suggested
> query but not build a table on the fly, what would I be left with?
> Once again thank you for time and I am sorry i am not yet advanced
> enough to fully appreciate it without asking another question.
>
SQL> create table demo_table as
2 with t as(
3 select '1' id_col,'2007' year_col,'1234' code_col,'1/1/07'
date_col,'A' action_col from dual union all
4 select '1','2007','1234','1/2/07','B' from dual union all
5 select '1','2007','1234','1/3/07','C' from dual union all
6 select '1','2007','4321','1/6/07','A' from dual union all
7 select '1','2007','4321','1/7/07','C' from dual union all
8 select '1','2007','4321','1/8/07','B' from dual),
9 t1 as(
10 select
id_col,year_col,code_col,to_date(date_col,'MM/DD/YY') as
date_col,action_col from t
11 )
12 select * from t1;
Table created.
SQL>
SQL> select id_col,year_col,code_col,date_col,action_col
2 from (select t.*,
3 row_number() over(partition by
id_col,year_col,code_col order by date_col) order_by_date,
4 row_number() over(partition by
id_col,year_col,code_col order by action_col) order_by_action
5 from demo_table t)
6 where order_by_date!=order_by_action and action_col='C';
I YEAR CODE DATE_COL A
- ---- ---- ------------------ -
1 2007 4321 07-JAN-07 C
The demo_table should represent your data, the rest is your query of
interest.
You might find useful as well following links:
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10002.htm#i2077142
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm#i81407
Best regards
Maxim
Received on Tue Jan 23 2007 - 17:17:40 CST
