Re: Help with query

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. Received on Tue Jan 23 2007 - 17:04:23 CST