All Questions
Tagged with gaps-and-islands sql-server-2017
3 questions
3
votes
1
answer
2k
views
How to collapse rows with individual, contiguous dates into single rows with date range start and end
Given a data set like this:
Id
Date
Value
1
01/01/2021
100
1
01/02/2021
100
1
01/03/2021
100
...
... (rows omitted for brevity)
...
1
12/29/2021
100
1
12/30/2021
100
1
12/31/2021
100
2
01/01/...
- 31
2
votes
1
answer
114
views
Gaps and islands with GPS breadcrumb data
I've got a gaps and islands (GAI) problem that I can't seem to crack to save my life. Most of the GAI problems I've seen involve data in the form of:
[Id] [StartDateTime] [EndDateTime]
But mine in ...
- 165
8
votes
3
answers
10k
views
Can I calculate ROW_NUMBER() for only consecutive records?
I need to calculate sequence numbers for consecutive values. That sounds like a job for ROW_NUMBER()!
DECLARE @Data TABLE
(
Sequence TINYINT NOT NULL PRIMARY KEY,
Subset CHAR(1) NOT ...
- 5,957