![]() These are the specific points that exactly identify when a previous time range ends, and the next time range begins (and cleanly handles successive duplicate entry/exit reads). Next I put these together to generate a list of all exit/entry ranges, which are the points where the value of Reader changes from 2 to 1. Then I grab the first and last index value per user which will be used for the outermost boundaries of each entry/exit range:, MinMax as ( Row_number() over (partition by order by date, time, reader) as ix, I've broken the problem down into a series of steps made of successive cte's that build on one another and lead up to a final working solution:įirst, I add a row index to the data to provide a simple PK for identifying a unique row: with NumberedAtt as ( ![]() This is a fairly difficult problem to solve with SQL due to the need to find transitions and ranges in the data, which is not trivial.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |