Results 1 to 15 of 15

Thread: [RESOLVED] SQL – Result table based on a pattern of activities in other table

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2018
    Posts
    514

    Resolved [RESOLVED] SQL – Result table based on a pattern of activities in other table

    I have started here:http://www.vbforums.com/showthread.p...this-challenge but when looking at it I found out that my question was not actually what I needed, so here we are.

    I need a result table that contains two columns 1-Case number, 2-RFT:
    Name:  RFT3.jpg
Views: 257
Size:  8.7 KB

    Which should come from the following tables:
    Name:  RFT1.jpg
Views: 286
Size:  32.2 KB


    Name:  2019-04-14_16-53-30.jpg
Views: 211
Size:  10.7 KB

    The phase order is used to see if a case has been for “Approval” and then returned to “New”. If that is the case then RFT should be “No”.

    I find the RFT part challenging and I have trouble figuring out how to make that happen.
    OptionBase1 in the other thread had provided some direction and the translation to SQL is highly appreciated.
    I have started some non-sense below but can’t think how to move further:

    Code:
    DECLARE @MaxNewPhase int
    DECLARE @MinAppPhase int
    DECLARE @DV_ID int
    DECLARE @Counter int = 0
    DECLARE @Totalrows int = (Select count(Distinct DEVIATION_NUMBER) From DMS_Deviation_Data)
    DECLARE @TableA TABLE (RowID int not null primary key identity(1,1), DV_ID nvarchar(20))
    INSERT INTO @TableA (DV_ID) Select Distinct DEVIATION_ID From DMS_Deviation_Data Order By DEVIATION_ID ASC;
    
    While @Counter < @Totalrows
    Begin   
     SET @DV_ID = (Select DEVIATION_ID From DMS_Deviation_Data Where )
     SET @MaxNewPhase = (Select MAX(RECORD_ORDER) from DMS_Deviation_phaseTrack Where Phase = 'New' And DEVIATION_ID = @DV_ID)
     SET @MinAppPhase = (Select MIN(RECORD_ORDER) from DMS_Deviation_phaseTrack Where Phase = 'Approval' And DEVIATION_ID = @DV_ID)
     If @MaxNewPhase > @MinAppPhase 
      begin
        
      End
      Set @Counter = @Counter + 1
    End
    Thanks for help and sorry for that SQL-blasphemy; just a newbee. I was also thinking to do the stuff on client side on datatables but will give the SQL a shot first.

    Edit: Tried with datatable and took soem 35 seconds; so that was nto a good option.
    Attached Images Attached Images  
    Last edited by Grand; Apr 15th, 2019 at 07:54 AM.

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: SQL – Result table based on a pattern of activities in other table

    Well, Obiwan's Idea is sound (OptionBase1 --> "Ohh""Beee""One" )
    I'd probably try to go like this:
    Get MAX and COUNT of Phase_order WHERE Phase='New'

    MAX(PHase_order) should be the same as Count, then RFT = 'Yes' Else 'no'
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  3. #3
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: SQL – Result table based on a pattern of activities in other table

    Yep, works in SQLite.

    Code:
    SELECT 
    CaseID, 
    (CASE WHEN MAX(Phase_Order)=COUNT(Phase_order) THEN 'Yes' ELSE 'No' END) AS RFT 
    FROM 
    MyTable 
    WHERE 
    Phase='New' 
    GROUP BY 
    CaseID
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  4. #4

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2018
    Posts
    514

    Re: SQL – Result table based on a pattern of activities in other table

    Thank you Zvoni. It seemed to work but then I tried to sample-check it and found out that it gives an incorrect verdict for cases such as the ones below:

    Name:  2019-04-15_13-12-41.jpg
Views: 233
Size:  17.4 KB

    Name:  2019-04-15_13-13-46.jpg
Views: 209
Size:  17.2 KB

    I forgot to mention that some cases go back and forth many times.

    Here the sample if you woudl be interested to test:

    DV_RFT_sample.zip
    Last edited by Grand; Apr 15th, 2019 at 06:27 AM.

  5. #5
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: SQL – Result table based on a pattern of activities in other table

    Could you give me an overview about the expected results?
    Please refer your answer(s) to the sample-data you provided.
    Just pick some representative CaseID's

    Because as far as i have understood you:
    If there is any "New" After an "Approval" it should return "No"
    So, for RFT to be Yes, it must start with a "New" (Order=0), and must be uninterrupted until an "Approval".
    To my understanding: If a case goes back and forth between "new" and "Approval" then RFT is definitely a no, since it wasn't RIGHT FIRST TIME
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  6. #6

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2018
    Posts
    514

    Re: SQL – Result table based on a pattern of activities in other table

    You can try case ID 993 (which is not RFT) for example and compare it with case 722 (which is RFT).

    And you are correct; if a case has been back from approval to new, even once, it has not been RFT.

    BTW, I think Yes and No must be switched?

    Thanks for the help.
    Last edited by Grand; Apr 15th, 2019 at 09:26 AM.

  7. #7
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: SQL – Result table based on a pattern of activities in other table

    *groan*
    Just had a look at your sample-file.
    Of course it doesn't work with my query.
    Column Record_order starts with zero for each CaseID
    *sigh*
    As a general advice: Always provide Sample Data in your Questions as it really is
    in your first post the Order-Column started with "1", now they start with "0"
    But i have an idea for a general solution.
    I didn't like my first one, since it worked out of coincidence.
    I'll be back at you

    Question for clarification:
    Each Case always starts with a "New", and must be followed by an "Approval" somewhere later on?
    There is no case, where you have a "New", but skip "Approval" to "Completed" or whatever else?
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  8. #8
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: SQL – Result table based on a pattern of activities in other table

    This worked in SQLite
    SQL Code:
    1. SELECT
    2. T1.DEVIATION_ID,
    3. T2.AppOrder,
    4. T3.NewOrder,
    5. (CASE WHEN T2.AppOrder IS NULL OR T3.NewOrder IS NULL THEN 'Undefined' ELSE (CASE WHEN T2.AppOrder>T3.NewOrder THEN 'Yes' ELSE 'No' END) END) AS RFT
    6.  
    7. FROM
    8. DV_RFT_sample AS T1
    9.  
    10. LEFT JOIN
    11. (SELECT
    12. DEVIATION_ID,
    13. MIN(RECORD_ORDER) AS AppOrder
    14. FROM
    15. DV_RFT_sample
    16. WHERE
    17. PHASE='Approval'
    18. GROUP BY
    19. DEVIATION_ID) AS T2
    20. ON
    21. T1.DEVIATION_ID=T2.DEVIATION_ID
    22.  
    23. LEFT JOIN
    24. (SELECT
    25. DEVIATION_ID,
    26. MAX(RECORD_ORDER) AS NewOrder
    27. FROM
    28. DV_RFT_sample
    29. WHERE
    30. PHASE='New'
    31. GROUP BY
    32. DEVIATION_ID) AS T3
    33. ON
    34. T1.DEVIATION_ID=T3.DEVIATION_ID
    35.  
    36. GROUP BY
    37. T1.DEVIATION_ID
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  9. #9
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: SQL – Result table based on a pattern of activities in other table

    The two Columns AppOrder (Line 3) and NewOrder (Line 4) can be left out above.
    I just had them for testing purposes
    Last edited by Zvoni; Apr 16th, 2019 at 02:04 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  10. #10
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: SQL – Result table based on a pattern of activities in other table

    So you're check is that a case has an Approval immediately followed by New? Or has an Approval and then any subsequent New?

    You're requirement isn't quite clear but either way the solution is to join the table back to itself. Lets look at the first option first:-
    sql Code:
    1. with cteCasesWhichWentToApprovalThenStraightBackToNew as
    2. (
    3.     Select T1.CaseID
    4.     From Table1 T1
    5.     Join Table1 T1_Previous
    6.         on T1.CaseID = T1_Prev.CaseID
    7.         And T1.PhaseOrder = T1_Prev.PhaseOrder + 1
    8.     Where T1.Phase = 'New'
    9.     And T1_Prev.Phase = 'Approval'
    10. )
    11. Select T2.CaseNumber, Case When C.CaseID is null then 'Yes' Else 'No' End as RFT
    12. From Table2 T2
    13. Join cteCasesWhichWentToApprovalThenStraightBackToNew C
    14.     on T2.CaseID = C.CaseID

    Note that in the cte I'm joining the T1 table back to itself where the caseid matches and the phase order is the previous phase order to the one we're looking at. In other words, is there a New phase immediately preceded by an Approval phase.

    We can structure a similar cte to check if there's a New phase with any preceding Approval phase:-
    sql Code:
    1. with cteCasesWhichWentToApprovalThenSubsequentlyBackToNew as
    2. (
    3.     Select T1.CaseID
    4.     From Table1 T1
    5.     Join Table1 T1_Previous
    6.         on T1.CaseID = T1_Prev.CaseID
    7.         And T1.PhaseOrder > T1_Prev.PhaseOrder
    8.     Where T1.Phase = 'New'
    9.     And T1_Prev.Phase = 'Approval'
    10. )
    11. Select T2.CaseNumber, Case When C.CaseID is null then 'Yes' Else 'No' End as RFT
    12. From Table2 T2
    13. Join cteCasesWhichWentToApprovalThenStraightBackToNew C
    14.     on T2.CaseID = C.CaseID

    Note that there is a triangular join in there. That's when you join a table back to itself based on an inequality. Those are bad for performance but in this case I imagine the number of record for an individual case is very small (10 or less) so it shouldn't be too bad. If you have a lot of records for a given case then we'll need a better solution by pre-preparing the contents of the cte using a loop but lets cross that bridge is and when we need to.

    Finally, I've expressed the solutions here using ctes but it would actually probably be quicker to use an Exists sub clause in this case. So this woud be an alternative syntax that might perform better:-
    sql Code:
    1. Select T2.CaseNumber, Case When Exists (Select *
    2.                                         From Table1 T1
    3.                                         Join Table1 T1_Previous
    4.                                             on T1.CaseID = T1_Prev.CaseID
    5.                                             And T1.PhaseOrder = T1_Prev.PhaseOrder + 1
    6.                                         Where T1.Phase = 'New'
    7.                                         And T1_Prev.Phase = 'Approval'
    8.                                         And T1.CaseID = T2.CaseID)
    9.                                     then 'No'
    10.                                     Else 'Yes'
    11.                         End as RFT
    12. From Table2 T2
    Last edited by FunkyDexter; Apr 16th, 2019 at 02:51 AM.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  11. #11
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: SQL – Result table based on a pattern of activities in other table

    FD,
    haven't exactly looked at your solution, but the OP confirmed for me:

    A case always starts with a "New".
    It can have mutiple consecutive "New"'s, followed by an "Approval" (or multiple consecutive "Approval"s) somewhere along the line.
    But, a case with status "Approval" can be given back to "New", indicated by a "New" after an "Approval".

    He keeps track of this with his Record-Order-Column.
    If there is any "New" after an "Approval" his RFT (Right First Time) is "No" Else "Yes".

    I solved it the way, that the lowest Record_Order-Value for an "Approval" must be higher then the highest Record_Order-Value for a "New"
    (Implying there is no "New" after an "Approval") to return "Yes" Else "No"

    If you can improve my solution be my guest (could always learn something new i've overlooked)
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  12. #12

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2018
    Posts
    514

    Re: SQL – Result table based on a pattern of activities in other table

    Quote Originally Posted by Zvoni View Post
    The two Columns AppOrder (Line 3) and NewOrder (Line 4) can be left out above.
    I just had them for testing purposes
    Thank you very much for following up. Your advice is also noted.
    When I tried your query with the real table:
    Code:
    SELECT T1.DEVIATION_ID, T2.AppOrder, T3.NewOrder,
    (CASE WHEN T2.AppOrder IS NULL OR T3.NewOrder IS NULL THEN 'Undefined' ELSE (CASE WHEN T2.AppOrder>T3.NewOrder THEN 'Yes' ELSE 'No' END) END) AS RFT
    FROM 
    DMS_Deviation_phaseTrack AS T1
    LEFT JOIN
    (SELECT DEVIATION_ID, MIN(RECORD_ORDER) AS AppOrder
    FROM
    DMS_Deviation_phaseTrack
    WHERE 
    PHASE='Approval'
    GROUP BY
    DEVIATION_ID) AS T2
    ON
    T1.DEVIATION_ID=T2.DEVIATION_ID
    LEFT JOIN (SELECT DEVIATION_ID, MAX(RECORD_ORDER) AS NewOrder
    FROM
    DMS_Deviation_phaseTrack
    WHERE 
    PHASE='New'
    GROUP BY
    DEVIATION_ID) AS T3
    ON
    T1.DEVIATION_ID=T3.DEVIATION_ID
    GROUP BY 
    T1.DEVIATION_ID
    I get this error:
    Code:
    Column 'T2.AppOrder' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
    and when I took AppOrder and New Order out of from line 3 and 4, I get the following:
    Code:
    Column 'T2.AppOrder' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
    Column 'T3.NewOrder' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
    Waht other modification is needed?

    BTW, was it possible for case numbers (from table 2 inmy post 1) to be also present in the result. So case number, case ID and RFT status in the result?
    Thanks in advance.

  13. #13

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2018
    Posts
    514

    Re: SQL – Result table based on a pattern of activities in other table

    Quote Originally Posted by FunkyDexter View Post
    So you're check is that a case has an Approval immediately followed by New? Or has an Approval and then any subsequent New?

    You're requirement isn't quite clear but either way the solution is to join the table back to itself. Finally, I've expressed the solutions here using ctes but it would actually probably be quicker to use an Exists sub clause in this case. So this woud be an alternative syntax that might perform better:-
    sql Code:
    1. Select T2.CaseNumber, Case When Exists (Select *
    2.                                         From Table1 T1
    3.                                         Join Table1 T1_Previous
    4.                                             on T1.CaseID = T1_Prev.CaseID
    5.                                             And T1.PhaseOrder = T1_Prev.PhaseOrder + 1
    6.                                         Where T1.Phase = 'New'
    7.                                         And T1_Prev.Phase = 'Approval'
    8.                                         And T1.CaseID = T2.CaseID)
    9.                                     then 'No'
    10.                                     Else 'Yes'
    11.                         End as RFT
    12. From Table2 T2
    Thank you for taking time to address my inquiry.
    My requirement is, as Zvoni also mentioned, that a case is RFT, or "Yes", when a case has been in "Approval" and the Approval never followed by "New" phase. Else the case is NRFT or "No". by else I mean if a case has been in "Approval" and is back to "New". The number of Approval and New might reoccur multiple of time and even there are duplicates meaning that Approval follows by another approval or may be a third one. That also goes for New. The important thing is not how many time the same phase (approval or new) is repeated but if a case that has been in Approval is back to New.

    I have tried your third query; it runs fast and the result is correct

    A final wish; how can I get the distinct case numbers which are RFT (Yes) or not RFT (No)? Thanks again.
    Last edited by Grand; Apr 16th, 2019 at 05:47 AM.

  14. #14
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: SQL – Result table based on a pattern of activities in other table

    Remove the very last "GROUP BY T1.DeviationID" and make a SELECT DISTINCT in the first SELECT
    I always forget that SQLite is more lenient with the GROUP-By-clauses

    As for your case numbers:
    Just join them to the query using the caseID as connecting field
    Aircode!

    SELECT
    T4.CaseNumber,
    (CASE WHEN blablablablabla
    FROM
    CaseTable AS T4
    INNER JOIN
    DMS_Table AS T1
    On
    T1.CaseID=T4.CaseID
    LEFT JOIN
    (SELECT blablabla and so on
    Last edited by Zvoni; Apr 16th, 2019 at 05:50 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  15. #15

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2018
    Posts
    514

    Re: SQL – Result table based on a pattern of activities in other table

    Thank you Zvoni.
    It works and you also accounted for those cases where they never got to Approval in the first palce.

    Thank both of you for helping out. Greatly appriciated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width