Google Sheet Query output for difference in succesive values in a date column

Question

I have a google sheet holding dates(mm/dd/yyyy) in a column. I want to calculate the difference between two successive values(input sheet) in the date column and return the output to a seperate sheet (summary sheet) when there is a difference of more than 1. Trying to use google sheet query since it has the property of executing a query result and automatically add rows based on the retrieval. The first sheet(input) is mostly a dataentry sheet where dates are being entered as an when a process is progressed. So whenever two consequtive date entries differ by more than 1, I want to report the start_date(i.e date value in ith row) and the end_date(i.e., date value i+1th) row. For each i and i+1 th row to be a new row in the summary sheet agaist column start_date(ith value) and end_date(i+1th value). Is there any way we can achieve this is google sheets?

Updated with some reference data:

https://docs.google.com/spreadsheets/d/1-PAwtw07LxH8OYJ9wvPdivcoLHjz1IRHBrqOieqixY8/edit?usp=sharing

Answer 1

I think it's easier to do the subtraction outside the query (because Query doesn't allow arithmetic on dates) like this:

=ArrayFormula(query({{A2:A},{A3:A;""},{A3:A;""}-{A2:A}},"select Col1,Col2 where Col3>1 label Col1 'Start Date',Col2 'End Date'"))

EDIT

You probably ought to check for any empty cells between dates, so you could add another condition to the Where clause:

=ArrayFormula(query({{A2:A},{A3:A;""},{A3:A;""}-{A2:A}},"select Col1,Col2 where Col3>1 and Col1 is not null label Col1 'Start Date',Col2 'End Date'"))