-
Apr 14th, 2019, 02:02 PM
#1
Thread Starter
Fanatic Member
-
Apr 15th, 2019, 01:47 AM
#2
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
-
Apr 15th, 2019, 01:57 AM
#3
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
-
Apr 15th, 2019, 06:17 AM
#4
Thread Starter
Fanatic Member
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:
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.
-
Apr 15th, 2019, 08:33 AM
#5
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
-
Apr 15th, 2019, 09:13 AM
#6
Thread Starter
Fanatic Member
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.
-
Apr 16th, 2019, 01:26 AM
#7
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
-
Apr 16th, 2019, 01:53 AM
#8
Re: SQL – Result table based on a pattern of activities in other table
This worked in SQLite
SQL 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 DV_RFT_sample AS T1 LEFT JOIN (SELECT DEVIATION_ID, MIN(RECORD_ORDER) AS AppOrder FROM DV_RFT_sample 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 DV_RFT_sample WHERE PHASE='New' GROUP BY DEVIATION_ID) AS T3 ON T1.DEVIATION_ID=T3.DEVIATION_ID GROUP BY 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
-
Apr 16th, 2019, 01:56 AM
#9
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
-
Apr 16th, 2019, 02:47 AM
#10
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:
with cteCasesWhichWentToApprovalThenStraightBackToNew as ( Select T1.CaseID From Table1 T1 Join Table1 T1_Previous on T1.CaseID = T1_Prev.CaseID And T1.PhaseOrder = T1_Prev.PhaseOrder + 1 Where T1.Phase = 'New' And T1_Prev.Phase = 'Approval' ) Select T2.CaseNumber, Case When C.CaseID is null then 'Yes' Else 'No' End as RFT From Table2 T2 Join cteCasesWhichWentToApprovalThenStraightBackToNew C 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:
with cteCasesWhichWentToApprovalThenSubsequentlyBackToNew as ( Select T1.CaseID From Table1 T1 Join Table1 T1_Previous on T1.CaseID = T1_Prev.CaseID And T1.PhaseOrder > T1_Prev.PhaseOrder Where T1.Phase = 'New' And T1_Prev.Phase = 'Approval' ) Select T2.CaseNumber, Case When C.CaseID is null then 'Yes' Else 'No' End as RFT From Table2 T2 Join cteCasesWhichWentToApprovalThenStraightBackToNew C 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:
Select T2.CaseNumber, Case When Exists (Select * From Table1 T1 Join Table1 T1_Previous on T1.CaseID = T1_Prev.CaseID And T1.PhaseOrder = T1_Prev.PhaseOrder + 1 Where T1.Phase = 'New' And T1_Prev.Phase = 'Approval' And T1.CaseID = T2.CaseID) then 'No' Else 'Yes' End as RFT 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
-
Apr 16th, 2019, 03:33 AM
#11
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
-
Apr 16th, 2019, 04:40 AM
#12
Thread Starter
Fanatic Member
Re: SQL – Result table based on a pattern of activities in other table
Originally Posted by Zvoni
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.
-
Apr 16th, 2019, 05:05 AM
#13
Thread Starter
Fanatic Member
Re: SQL – Result table based on a pattern of activities in other table
Originally Posted by FunkyDexter
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:
Select T2.CaseNumber, Case When Exists (Select * From Table1 T1 Join Table1 T1_Previous on T1.CaseID = T1_Prev.CaseID And T1.PhaseOrder = T1_Prev.PhaseOrder + 1 Where T1.Phase = 'New' And T1_Prev.Phase = 'Approval' And T1.CaseID = T2.CaseID) then 'No' Else 'Yes' End as RFT 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.
-
Apr 16th, 2019, 05:46 AM
#14
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
-
Apr 16th, 2019, 06:17 AM
#15
Thread Starter
Fanatic Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|