12 Replies Latest reply on Dec 12, 2018 1:55 PM by Frank Kulash

    Exclude the row depending on the last date

    3848360

      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

       

      Unbenannt.PNG

      The filter should only affect the last date.

       

      Thanks for your help

        • 1. Re: Exclude the row depending on the last date
          John Thorton

          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

           

          Unbenannt.PNG

          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?

          • 2. Re: Exclude the row depending on the last date
            Frank Kulash

            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?

            • 3. Re: Exclude the row depending on the last date
              BEDE

              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...

               

              • 4. Re: Exclude the row depending on the last date
                Stew Ashton

                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

                • 5. Re: Exclude the row depending on the last date
                  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;
                  

                   

                  TIME_SERIES_IDJIRA_TICKET_NOTIME_SERIES_DTJIRA_PROGRESSJIRA_DECISION
                  222018-12-08 00:00 open
                  622018-12-09 00:00 open
                  1022018-12-10 00:00closedtrue
                  332018-12-08 00:00 open
                  732018-12-09 00:00 open
                  1132018-12-10 00:00openfalse
                  442018-12-08 00:00 open
                  842018-12-09 00:00 open
                  1242018-12-10 00:00opentrue

                   

                  Regards,

                  Stew

                  • 6. Re: Exclude the row depending on the last date
                    Frank Kulash

                    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?

                    • 7. Re: Exclude the row depending on the last date
                      Stew Ashton

                      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

                      • 8. Re: Exclude the row depending on the last date
                        Stew Ashton

                        Hi Frank,

                         

                        Using MATCH_RECOGNIZE, I would say "order by date descending and match if the most recent row does not have 'false' and 'closed'". That way the question "is there a match?" is answered immediately.

                         

                        select * from t
                        match_recognize(
                          partition by jira_ticket_no order by time_series_dt desc
                          all rows per match
                          pattern (^a b*)
                          define a as 1 = any (decode(jira_progress,'closed',0,1), decode(JIRA_DECISION,'false',0,1))
                        );
                        

                         

                        Something similar could be done using analytic functions:

                         

                        select TIME_SERIES_ID, JIRA_TICKET_NO, TIME_SERIES_DT, JIRA_PROGRESS, JIRA_DECISION
                        from (
                          select t.*,
                          first_value(jira_progress) over(partition by jira_ticket_no order by time_series_dt desc) jp,
                          first_value(JIRA_DECISION) over(partition by jira_ticket_no order by time_series_dt desc) jd
                          from t
                        )
                        where 1 = any (decode(jp,'closed',0,1), decode(jd,'false',0,1));
                        

                         

                        I don't find any significant performance difference between these two solutions, though with larger volumes MATCH_RECOGNIZE starts to run a bit faster.

                         

                        Regards,

                        Stew

                        1 person found this helpful
                        • 9. Re: Exclude the row depending on the last date
                          3848360

                          Hi Stew,

                           

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

                                   2. I want check the last date from every timeline (time_series_id) that have a status "false" and "closed"

                                   3 and 4 did you descripe perfectly

                           

                          Best regards

                          Lea

                          • 10. Re: Exclude the row depending on the last date
                            Frank Kulash

                            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.

                            • 11. Re: Exclude the row depending on the last date
                              3848360

                              Thanks for your help, I could solved the problem

                              • 12. Re: Exclude the row depending on the last date
                                Frank Kulash

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