I have a table with rows of data relating to tasks. Using the following example below I have a few outcomes that I would like but I am unsure of the best way to do that, so open to suggestions
I want to create three tables effectively.
One where RO:Plan Review is present, and with 14 days, there is also an RT:Pre Tmt Chk present. if those conditions are met then it takes the entire row.
the 2nd table would be the same except it looks for the RT:Pre Tmt Check Replan, that is within 2 weeks of the RO:Plan Review.
The final table would be when there is an RO:Plan Review present, but no RT:Pre Tmt Chk, or RT:Pre Tmt Check Replan within 2 weeks.
sorry if that is super confusing. Hope you can help and thanks in advance
To add, it looks like you have more rows than the first three only. What are those like? Are the RO and two RT tows always on top of the table? It matters quite a lot for the logic to use here because you need to lead/lag with an offset higher than 1.
I’m honestly a bit to new to all of this to know what is the best way or the different ways to achieve it. Ultimately my end goal would be able to export to csv with the three tables each as their own sheet in a single workbook. Though I am just trying to work through it one step at a time.
I will add my code below as it may provide a bit more for you to work with, or if you have suggestions on how to improve the query to better filter the data in later nodes.
I’m afraid we still need more clarification on your desired output because the data that you show leaves some open questions
I assume your db contains information of multiple patients meaning that you would have to evaluate for each patient individually if RO and the mentioned RT lines are present?
I think your table is supposed to be still sorted on Due_date?
If you could just share the columns Task_Name and Due_date in a workable format then I’m sure someone will be able to jump. It’s a bit long stretch to type all of this over in KNIME ourselves
SELECT
Ident.IDA AS MRN,
Patient.Last_Name AS Patient_Last_Name,
Patient.First_Name AS Patient_First_Name,
QCLTASK.Description AS Task_Name,
CAST(Chklist.Due_DtTm As DATE) AS Due_Date,
Req_Staff.Last_Name AS Required,
Rsp_Staff.Last_Name AS Responsible,
CAST(Chklist.Act_DtTm AS DATE) AS Completed_Date,
FORMAT(Chklist.Act_DtTm, ‘HH:mm:ss’) AS Completed_Time,
Com_Staff.Last_Name AS Completedby,
QCLGroup.Description AS TaskSetGroup,
Chklist.Notes AS Comment,
Notes.notes AS Note_Message
FROM
Chklist
JOIN Staff AS Req_Staff ON Req_Staff.Staff_ID = Chklist.Req_Staff_ID
JOIN Staff AS Rsp_Staff ON Rsp_Staff.Staff_ID = Chklist.Rsp_Staff_ID
JOIN Staff AS Com_Staff ON Com_Staff.Staff_ID = Chklist.Com_Staff_ID
JOIN Ident ON Chklist.Pat_ID1 = Ident.Pat_ID1
JOIN Patient ON Patient.Pat_ID1 = Ident.Pat_ID1
LEFT JOIN QCLTASK ON Chklist.TSK_ID = QCLTASK.TSK_ID
LEFT JOIN QCLGroup On QCLGroup.QGP_ID = Chklist.Chklist_ID
INNER JOIN Notes ON Notes.Note_ID = Chklist.Note_ID
WHERE Chklist.Complete = 1
AND (QCLTASK.Description LIKE ‘RT:%’ OR QCLTASK.Description Like ‘ROMP%’ OR QCLTASK.Description LIKE ‘RO:%’)
AND Chklist.Due_DtTm BETWEEN ‘2021-12-12 00:00:00.000’ AND ‘2023-12-14 00:00:00.000’
@RGray
Typically you want to upload sample data with your question so forum members can work with the data in KNIME instead of creating data from scratch.
I assume @ArjenEX was referring to that and not the SQL Script.
br
