14 Replies Latest reply: Feb 13, 2014 9:34 PM by c24f4c2e-540e-4982-b5e2-c4b8829e1636 RSS

Can someone help in SQL

c24f4c2e-540e-4982-b5e2-c4b8829e1636 Newbie
Currently Being Moderated

I have requirement to generate difference between two date of any 2 process states

If there are states called A and B I need difference between their last_update_date to see how much time it took between 2 process state

 

Can anyone please tell how can i write query

 

last_update_date is in timestamp

 

ID      Process_state          last_update_date

1

2

3

4

5

  • 1. Re: Can someone help in SQL
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    Maybe

    SELECT  MAX (CASE WHEN process_state = 'B' THEN last_update_date END)

          - MAX (CASE WHEN process_state = 'A' THEN last_update_date END)  AS diff_in_days

    FROM    table_x

    ;

    If the latest 'A' was after the latest 'B', then diff_in_days will be a negative number.

     

    I hope this answers your question.

    If not, 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.

    Point out where the query above is giving the wrong results, and explain, using specific examples, how you get the correct results from the given data in those places.  If you changed the query at all, post your code.

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

    See the forum FAQ: https://forums.oracle.com/message/9362002

  • 2. Re: Can someone help in SQL
    c24f4c2e-540e-4982-b5e2-c4b8829e1636 Newbie
    Currently Being Moderated

    Thanks Frank

     

    Version is 11.2.0.3

     

    Actually LAST_UPDATE_DATE is TIMESTAMP column.

     

    So may be can I use Extract function in CASE statement that will show difference in Day, Hour,Minute,Second

     

    Issue is I need to show PROCESS_STATE,ID,LAST_UPDATE_DATE and TIMESTAMP difference

     

    Will I able to show everything above using your logic?

  • 3. Re: Can someone help in SQL
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

     

    c24f4c2e-540e-4982-b5e2-c4b8829e1636 wrote:

     

    Thanks Frank

     

    Version is 11.2.0.3

     

    Actually LAST_UPDATE_DATE is TIMESTAMP column.

     

    So may be can I use Extract function in CASE statement that will show difference in Day, Hour,Minute,Second

     

    Issue is I need to show PROCESS_STATE,ID,LAST_UPDATE_DATE and TIMESTAMP difference

     

    Will I able to show everything above using your logic?

    It depends on your data, and what results you want from that data.

    You know what you need to post if you'd like help.

     

    The only thing I can tell for sure from this message is that you don't need EXTRACT and CASE jsut to get the difference in days, hours, minutes and seconds.  If you subtract one TIMESTAMP from another, the result is an INTERVAL DAY TO SECOND, the default output for which shows the days, hours, minutes and seconds very clearly.

  • 4. Re: Can someone help in SQL
    AnnPricks E Guru
    Currently Being Moderated

    c24f4c2e-540e-4982-b5e2-c4b8829e1636 wrote:

     

    Thanks Frank

    Here is my query

     

    What I need more in this query is display VALUE AHKHKO018295 to be part of outer select clause

     

    also query should handle multiple values using IN clause in subquery

     

     

     

    SELECT EXTRACT (DAY FROM MAX(CASE WHEN A.process_step_name='AB' THEN A.LAST_MOD_DT END)-MAX(CASE WHEN B.process_step_name='CD' THEN B.LAST_MOD_DT END)) AS Days,

    EXTRACT (HOUR FROM MAX(CASE WHEN A.process_step_name='AB' THEN A.LAST_MOD_DT END)-MAX(CASE WHEN B.process_step_name='CD' THEN B.LAST_MOD_DT END)) AS Hours,

    EXTRACT (MINUTE FROM MAX(CASE WHEN A.process_step_name='AB' THEN A.LAST_MOD_DT END)-MAX(CASE WHEN B.process_step_name='CD' THEN B.LAST_MOD_DT END)) AS Minutes,

    EXTRACT (SECOND FROM MAX(CASE WHEN A.process_step_name='AB' THEN A.LAST_MOD_DT END)-MAX(CASE WHEN B.process_step_name='CD' THEN B.LAST_MOD_DT END)) AS Seconds

    from

    (select wpsi.LAST_MOD_DT,wpsi.id_wf_process_step,wps.process_step_name from wf_process_step_instance wpsi, wf_process_step wps where

      wpsi.id_wf_process_step = wps.id_wf_process_step and

      wpsi.id_wf_process_step_instance in

      (select id_wf_process_step_instance from wf_proc_step_inst_bus_ref_id where

      id_wf_proc_step_inst_b_ref_id in (select id_wf_proc_step_inst_b_ref_id

      from WF_BUSINESS_REFERENCE_KEY where

      value = 'AHKHKO018295'))) A,

     

      (select wpsi.LAST_MOD_DT,wpsi.id_wf_process_step,wps.process_step_name from wf_process_step_instance wpsi, wf_process_step wps where

      wpsi.id_wf_process_step = wps.id_wf_process_step and

      wpsi.id_wf_process_step_instance in

      (select id_wf_process_step_instance from wf_proc_step_inst_bus_ref_id where

      id_wf_proc_step_inst_b_ref_id in (select id_wf_proc_step_inst_b_ref_id

      from WF_BUSINESS_REFERENCE_KEY where

      value = 'AHKHKO018295')))B

    What is the difference between qry A and qry B.. Why you are joining both queries? Can you please post some sample data(Create table and Insert script) and required output?

  • 5. Re: Can someone help in SQL
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

    c24f4c2e-540e-4982-b5e2-c4b8829e1636 wrote:

     

    Thanks Frank

    Here is my query ...

     

     

    Thanks, but where are the CREATE TABLE and INSERT statements, and the exact results you want from whatever sample data you post?  as long as I don't know where you're starting from, or where you want to go, I can't give you very good directions.

  • 6. Re: Can someone help in SQL
    c24f4c2e-540e-4982-b5e2-c4b8829e1636 Newbie
    Currently Being Moderated

    I am joining both queries because I want to get difference in LAST_UPDATE_DATE column of same table

     

    For one value in WF_BUSINESS_REFERENCE_KEY there will be multiple rows in that table.

     

    For 1 VALUE there will be multiple id_wf_process_step_instance and multiple process_step_name

     

    We need to display all VALUES which user will give by IN list along with difference in timestamp between each process_step_name

     

    I am able to display difference for 1 particular value and by hard coding 2 process state names

     

    Need to display timestamp difference between each process state for each VALUE user provide along with VALUE column

     

     

     

     

     

     

     

    Create table statements

     

     

    CREATE TABLE WF_PROCESS_INSTANCE

    (

      ID_WF_PROCESS_INSTANCE NUMBER(*, 0) NOT NULL

    , ID_WF_PROCESS NUMBER(*, 0) NOT NULL

    , ID_WF_ENGINE_PROCESS NUMBER(*, 0) NOT NULL

    , CREATED_DT TIMESTAMP(3)

    , LAST_MOD_DT TIMESTAMP(3)

    , LAST_MOD_BY VARCHAR2(10 CHAR)

    , CREATED_BY VARCHAR2(10 CHAR)

    , CONSTRAINT WF_PROCESS_INSTANCE_PK PRIMARY KEY

      (

        ID_WF_PROCESS_INSTANCE

      )

      ENABLE

    )

     

     

    CREATE TABLE WF_PROCESS_STEP

    (

      ID_WF_PROCESS_STEP NUMBER(*, 0) NOT NULL

    , PROCESS_STEP_NAME VARCHAR2(30 CHAR)

    , ID_WF_PROCESS_TYPE NUMBER(*, 0) NOT NULL

    , IS_AUTHORIZER_TYPE VARCHAR2(1 CHAR)

    , START_DT TIMESTAMP(3)

    , END_DT TIMESTAMP(3)

    , ACTIVITY_STATUS VARCHAR2(1 CHAR)

    , CREATED_DT TIMESTAMP(3)

    , LAST_MOD_DT TIMESTAMP(3)

    , LAST_MOD_BY VARCHAR2(10 CHAR)

    , ID_LIFECYCLE_STATUS NUMBER(*, 0)

    , ID_MASTER_RECORD NUMBER(*, 0)

    , CREATED_BY VARCHAR2(20 CHAR)

    , ID_LAST_MOD_BY_GTPP_USER NUMBER(*, 0)

    , LAST_MOD_GTPP_DT TIMESTAMP(3)

    , ID_WF_PROCESS_STEP_CATEGORY NUMBER(*, 0)

    , CONSTRAINT WF_PROCESS_STEP_PK PRIMARY KEY

      (

        ID_WF_PROCESS_STEP

      )

      ENABLE

    )

     

     

    CREATE TABLE WF_PROC_STEP_INST_BUS_REF_ID

    (

      ID_WF_PROC_STEP_INST_B_REF_ID NUMBER(*, 0) NOT NULL

    , ID_WF_PROCESS_STEP_INSTANCE NUMBER(*, 0) NOT NULL

    , ID_WF_BUSINESS_REF_IDNTFR_TYPE NUMBER(*, 0) NOT NULL

    , CREATED_DT TIMESTAMP(3)

    , LAST_MOD_DT TIMESTAMP(3)

    , CREATED_BY VARCHAR2(10 CHAR)

    , LAST_MOD_BY VARCHAR2(10 CHAR)

    , IS_SYNCED_IND VARCHAR2(1 CHAR) NOT NULL

    , CONSTRAINT WF_PROC_STEP_INST_BUS_REF_PK PRIMARY KEY

      (

        ID_WF_PROC_STEP_INST_B_REF_ID

      )

      ENABLE

    )

     

    CREATE TABLE WF_BUSINESS_REFERENCE_KEY

    (

      ID_WF_BUSINESS_REFERENCE_KEY NUMBER(*, 0) NOT NULL

    , KEY_NAME VARCHAR2(50 CHAR)

    , VALUE VARCHAR2(100 CHAR)

    , ID_WF_PROC_STEP_INST_B_REF_ID NUMBER(*, 0) NOT NULL

    , CREATED_DT TIMESTAMP(3)

    , LAST_MOD_DT TIMESTAMP(3)

    , CREATED_BY VARCHAR2(10 CHAR)

    , LAST_MOD_BY VARCHAR2(10 CHAR)

    , CONSTRAINT WF_BUS_REF_KEY_PK PRIMARY KEY

      (

        ID_WF_BUSINESS_REFERENCE_KEY

      )

      ENABLE

    )

  • 7. Re: Can someone help in SQL
    AnnPricks E Guru
    Currently Being Moderated

    Thanks for the create table script. Can you please post some sample data(INSERT script) and required output from that data?

  • 8. Re: Can someone help in SQL
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    Thanks; now all we need are some INSERT statements for a little sample data, the results you want from that data, and an explanation of how you get those results from that data.

     

    Are all those columns important in this problem?

    If not, only include the relevant columns in your INSERT statements.

  • 9. Re: Can someone help in SQL
    AnnPricks E Guru
    Currently Being Moderated

    Where is the create table script for WF_PROCESS_STEP_INSTANCE table. You are giving create table script for WF_PROCESS_INSTANCE and there is no insert script for that table. Can you please post correct insert script

  • 10. Re: Can someone help in SQL
    c24f4c2e-540e-4982-b5e2-c4b8829e1636 Newbie
    Currently Being Moderated

    CREATE TABLE WF_PROCESS_STEP_INSTANCE

    (

      ID_WF_PROCESS_STEP_INSTANCE NUMBER(*, 0) NOT NULL

    , ID_WF_PROCESS_STEP NUMBER(*, 0) NOT NULL

    , ID_WF_ENGINE_WORKITEM NUMBER(*, 0)

    , ID_PREV_PROC_STEP_INSTANCE NUMBER(*, 0)

    , CREATED_DT TIMESTAMP(3)

    , LAST_MOD_DT TIMESTAMP(3)

    , CREATED_BY VARCHAR2(10 BYTE)

    , LAST_MOD_BY VARCHAR2(10 BYTE)

    , ID_WF_PROCESS_INSTANCE NUMBER(*, 0) NOT NULL

    , ID_LOCKED_BY_GTPP_USER NUMBER(*, 0)

    , UUID VARCHAR2(36 CHAR)

    , WF_PROCESS_STEP_INST_STATUS VARCHAR2(40 CHAR)

    , CURRENT_WORKFLOW_PRIORITY NUMBER(*, 0)

    , CREATED_GTPP_DT TIMESTAMP(3)

    , ID_ASSIGN_TO_TEAM NUMBER(*, 0)

    , ID_ASSIGN_TO_USER NUMBER(*, 0)

    , CONSTRAINT WF_PROCESS_STEP_INSTANCE_PK PRIMARY KEY

      (

        ID_WF_PROCESS_STEP_INSTANCE

      )

      ENABLE

  • 11. Re: Can someone help in SQL
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

     

    c24f4c2e-540e-4982-b5e2-c4b8829e1636 wrote:

     

    Sample output

     

    Value                                  Process_State                 last_update_date                                 diff_day    diff_hour   diff_minute   diff_second

     

    AHSAMPLETXNID015               A                            03-JUL-13 09.42.52.936000000 AM          00               00          -3                                                     Timestamp Difference is between two process state A-B,B-C,C-D,D-A

    AINHKK0002671199                  B                           03-JUL-13 09.45.52.936000000 AM           00               00          -2                        

    AHSAMPLETXNID002               C                            03-JUL-13 09.47.52.937000000 AM          00               00         -2

    AHSAMPLETXNID004               D                            03-JUL-13 09.40.53.658000000 AM            00            00           -2

    ...

     

    I am so confused.

    When you say "Difference is between two process state A-B,B-C,C-D,D-A" do you mean that sometimes if the differenece between A nd B, other times it is the differecne between B and C, sometimes C and D, and other times D and A?  Why just those combinations?  Why not B and D, or C and A?  How do you decide which 2 states you're going to use in each case?  Which pair was used in each of the output rows above?

     

    Once again, do all those columns really play some role in htis problem?

    If you only need some of the columns to get the results you need, then only include those columns.  (This includes columns needed for joins, even if they are not displayed.)

    If you really need all of the columns, explain what role they play in this problem.  How important they are in your application doesn't matter; we need to know why they are important in getting the right results in this problem.

  • 12. Re: Can someone help in SQL
    c24f4c2e-540e-4982-b5e2-c4b8829e1636 Newbie
    Currently Being Moderated

    Lets say we are giving 2 process states input to queries

    Then I dont need to show process_state  in output because that would be valid for one Value

     

    But output should display same 2 process state difference for multiple value

     

     

     

    The output should be

     

    Value                                                           diff_daydiff_hour   diff_minute   diff_second

    AHSAMPLETXNID015                                       

    AINHKK0002671199

    AHSAMPLETXNID002

    AHSAMPLETXNID004

  • 13. Re: Can someone help in SQL
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    I think I understand that part now: You want to pass 2 parameters to the query, and have the query find the difference between those 2 given states.

    Which 2 states were used to produce the results you posted?

  • 14. Re: Can someone help in SQL
    c24f4c2e-540e-4982-b5e2-c4b8829e1636 Newbie
    Currently Being Moderated

    My result is just made and not coming from any input value

     

    It is an example

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points