Gap in Dates - Or Next Date Due

[MacCaspio]

Hello.  I have a scheduling application that ensures a task is manually added to the schedule every day.  We operate the manual scheduling process every few days.  I need to figure out a way to calculate the next opening in the calendar.  It needs to return the first "gap" it finds as it counts from today forward....not the date that is the furthest out.  

Example:  If we have a task scheduled for each of the next 3 days, I need to find the gap that exists starting on day 4 and return that date.  This will allow the scheduler to know how far out the schedule has been created. 

ChatGPT says I should run the following SQL queries in a calculated field, but I don't believe Caspio will allow these subqueries.  Does anyone have any ideas how to accomplish finding the next opening on the schedule?

 

SELECT 
    (SELECT MIN(DueDate + 1) 
     FROM Pin_Creation_Schedule 
     WHERE ServiceID = [@field:Pin_Creation_Details_ServiceID]
       AND DueDate >= CAST(GETDATE() AS DATE)
       AND NOT EXISTS (
           SELECT 1 
           FROM Pin_Creation_Schedule AS innerTable 
           WHERE innerTable.ServiceID = [@field:Pin_Creation_Details_ServiceID] 
             AND innerTable.DueDate = Pin_Creation_Schedule.DueDate + 1
       )
    ) AS NextGapDate
FROM Pin_Creation_Schedule
WHERE ServiceID = [@field:Pin_Creation_Details_ServiceID]
  AND DueDate >= CAST(GETDATE() AS DATE)

 

[MacCaspio]

Just replying in case it helps anyone else.  I was finally able to get this to work with the following SQL query in a calculated field.  There were several standard queries that weren't supported, but the following code works!

 

SELECT MIN(DATEADD(day, 1, DueDate)) 
FROM Pin_Creation_Schedule outerTable
WHERE ServiceID = [@field:Pin_Creation_Details_ServiceID]
  AND DueDate >= CAST(GETDATE() AS DATE)
  AND NOT EXISTS (
    SELECT 1 
    FROM Pin_Creation_Schedule innerTable 
    WHERE innerTable.ServiceID = outerTable.ServiceID 
      AND innerTable.DueDate = DATEADD(day, 1, outerTable.DueDate)
)

[Kurumi]

Hi @MacCaspio - As Caspio now offers integration with OpenAI, you can use extensions to leverage AI to update your data based on a prompt. You can try to create a request based on a specific date. To learn more about Extensions, you can check it here: https://howto.caspio.com/integration/extensions/