There is a forum to see a post, " Consult the query out of the continuous date record method", screenshot as follows:
Insus.net tries to write a program and test it to get the expected results, which can be referenced and learned by SQL code.
Copy Code code as follows:
--Create a temporary table that will store records for consecutive dates
CREATE TABLE #temp (IDD VARCHAR (), Sdate DATETIME)
DECLARE @sD DATETIME--Start date
DECLARE @eD DATETIME--End date
--In the record, find the start and end dates
SELECT @sD = MIN ([sdate]), @eD = MAX ([sdate]) from [TT]
DECLARE @n INT = 0--Declaring a variable, storing the number of tired records
--Cycle Date
while (@sD <= @eD)
BEGIN
--If there is
IF EXISTS (SELECT top 1 1 from [TT] WHERE [sdate] = @sD)
BEGIN
SET @sD = DATEADD (day,1, @sD)--date plus 1 days
SET @n = @n + 1--record plus 1
End
ELSE--If it does not exist
BEGIN
if (@n >= 3)--to determine if it is greater than or equal to 3
INSERT into #temp SELECT [idd],[sdate] from [TT] WHERE [sdate] BETWEEN DATEADD (Day,-@n, @sD) and @sD
--date plus 1 days
SET @sD = DATEADD (day,1, @sD)
SET @n = 0--initialized to 0
End
End
--list records that meet the criteria
SELECT * from #temp
The above method, actually can change if (@n >= 3) This sentence 3 to a variable, this can facilitate later program expansion, one day need to change to 2 consecutive days, 4 days, 5 days, or any day.
