65c71872-51fa-46fa-9db7-78bcc95aeab9 wrote:

 

Hi together,

 

I need your help in a SQL query Problem.

My Problem is it, I want exclude all timelines which have in the last date the conditions JIRA_PROGRESS= closed and

JIRA_DECISION = false

 

The filter should only affect the last date.

 

Thanks for your help

Please click on URL below & respond accordingly

Re: 2. How do I ask a question on the forums?

Hi,

This does what you requested:

WITH    got_max_dt   AS

(

    SELECT  time_series_id, jira_ticket_no, time_series_dt

    ,       time_series_id, kpi_name, ts_status

    ,       jira_progress, jira_decision

    ,       MAX (time_series_dt) OVER ()  AS max_dt

    FROM    vw_bqi_tableau_data

)

SELECT  time_series_id, jira_ticket_no, time_series_dt

,       time_series_id, kpi_name, ts_status

,       jira_progress, jira_decision

FROM    got_max_dt

WHERE   jira_decision  = 'false'

AND     jira_progress  = 'closed'

AND     time_series_dt = max_dt

;

assuming "last date" means the latest time_series_dt.  If "last date" is based on some other column, then use that column for computing and comparing max_dt.

Analytic functions are computed after the WHERE clause has been applied.  To use the results of an analytic function (like MAX, above) in a WHERE clause, you can compute the analytic function in a sub-query (like got_max_dt above).

I hope this answers your question.

If not, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.

Explain, using specific examples, how you get those results from that data.

Always say which version of Oracle you're using (e.g., 12.1.0.1.0).

See the forum FAQ: Re: 2. How do I ask a question on the forums?

Now, I'm guessing that vw_bqi_tableau_data is a view. So, you should post the view's DDL and the DDL for the tables included in that view. Maybe it would be better not to use the view itself, but rather one or more of the tables included in that view.

About: "should affect last date" - am I to assume you mean what is found in time_series_dt?

If so, I thing something like below:

with tb(

select time_series_id, time_series_dt, kpi_name, ts_status, Jira_progress, Jira_decision

  ,max(time_series_dt) over (partition by 1)  max_dt --- should it be partition by some group? which?

from vw_bki_tableau_data

)

select *

from tb

where

  case

     when time_series_dt=max_dt and Jira_decision='false' and Jira_progress='closed' then 1

     when time_series_dt!=max_dt then 1

     else 0

  end=1

;

Although, I fear the max(time_series_dt) involving a window sort my cause a terrible performance if vw_bki_tableau_data has lot of rows...

Hello,

What is a "timeline" exactly? Is it every row that has the same jira_ticket_no?

The impression I get is :

1. a timeline is a group of rows
2. You want to check the last row (in date order) of each timeline to see if it contains 'false' and 'closed'
3. If the last row has those values, you want to exclude the entire timeline from the result
4. Otherwise you want to see the entire timeline.

Did I understand you correctly?

Best regards,

Stew Ashton

Assuming my understanding is correct:

CREATE TABLE T (
  TIME_SERIES_ID   NUMBER ,
  JIRA_TICKET_NO   NUMBER ,
  TIME_SERIES_DT   DATE ,
  JIRA_PROGRESS    VARCHAR2 ( 6 BYTE ),
  JIRA_DECISION    VARCHAR2 ( 5 BYTE )
);
Insert into T values (1,1,to_date('2018-12-08 00:00','yyyy-mm-dd hh24:mi'),null,'open');
Insert into T values (2,2,to_date('2018-12-08 00:00','yyyy-mm-dd hh24:mi'),null,'open');
Insert into T values (3,3,to_date('2018-12-08 00:00','yyyy-mm-dd hh24:mi'),null,'open');
Insert into T values (4,4,to_date('2018-12-08 00:00','yyyy-mm-dd hh24:mi'),null,'open');
Insert into T values (5,1,to_date('2018-12-09 00:00','yyyy-mm-dd hh24:mi'),null,'open');
Insert into T values (6,2,to_date('2018-12-09 00:00','yyyy-mm-dd hh24:mi'),null,'open');
Insert into T values (7,3,to_date('2018-12-09 00:00','yyyy-mm-dd hh24:mi'),null,'open');
Insert into T values (8,4,to_date('2018-12-09 00:00','yyyy-mm-dd hh24:mi'),null,'open');
Insert into T values (9,1,to_date('2018-12-10 00:00','yyyy-mm-dd hh24:mi'),'closed','false');
Insert into T values (10,2,to_date('2018-12-10 00:00','yyyy-mm-dd hh24:mi'),'closed','true');
Insert into T values (11,3,to_date('2018-12-10 00:00','yyyy-mm-dd hh24:mi'),'open','false');
Insert into T values (12,4,to_date('2018-12-10 00:00','yyyy-mm-dd hh24:mi'),'open','true');

select * from t where jira_ticket_no not in (
  select jira_ticket_no
  from t
  group by jira_ticket_no
  having
    max(jira_progress) keep (dense_rank last order by time_series_dt) = 'closed'
    and
    max(jira_decision) keep (dense_rank last order by time_series_dt) = 'false'
)
order by 2,1;

Regards,

Stew

Hi,

If Stew's assumptions in reply #4 are correct, and you're using Oracle 12.1 (or higher), you can also do it this way:

SELECT    time_series_id, jira_ticket_no, time_series_dt

,         jira_progress, jira_decision

FROM      t

MATCH_RECOGNIZE

          (

            PARTITION BY  jira_ticket_no

            ORDER BY      time_series_dt  DESC

            MEASURES      FIRST (jira_decision)  AS last_jira_decision

            ,              FIRST (jira_progress)  AS last_jira_progress

            ALL ROWS PER MATCH

            PATTERN      (any_row+)

            DEFINE        any_row  AS  1 = 1

          )

WHERE     LNNVL (last_jira_decision  = 'false')

OR        LNNVL (last_jira_progress  = 'closed')

ORDER BY  jira_ticket_no, time_series_dt  DESC

;

Stew Ashton, you're good at MATCH_RECOGNIZE.  Can you suggest improvements?  Would you use a NOT IN sub-query in version 12.1 or up?

Hi Frank,

My browser is having trouble with the ODC site. I was hoping the NOT IN would use an index if available, but my tests show that is not the case. I have a MATCH_RECOGNIZE solution but it is probably not quite as good as the equivalent analytic solution.

Regards, Stew

Hi, Lea,

3848360 wrote:

 

Hi Stew,

 

to 1. A timeline is group by time_series_id, the status refers to the timeline

...

Replies 4-8 assumed that jira_ticket_no identified the different timelines.  If timeline is based on time_series_id instead, then use time_series_id instead of jira_ticket_no in the solutions above.

Don't forget to mark the thread as "Answered".