I saw a post on the Forum ,《How can I query records of consecutive dates?, As follows:
Insus. NET tries to write a program and tests it to get the expected results. The SQL code can be used for reference and learning.
Copy codeThe Code is as follows:
-- Create a temporary table to store records of consecutive dates
Create table # temp (idd varchar (10), sdate datetime)
DECLARE @ sD DATETIME -- start date
DECLARE @ eD DATETIME -- end date
-- In the record, locate the start and end dates
SELECT @ sD = MIN ([SDATE]), @ eD = MAX ([SDATE]) FROM [TT]
DECLARE @ n int = 0 -- DECLARE a variable and store the number of tired records
-- Cycle date
WHILE (@ sD <= @ eD)
BEGIN
-- If yes
If exists (select top 1 FROM [TT] WHERE [SDATE] = @ sD)
BEGIN
SET @ sD = DATEADD (day, 1, @ sD) -- date plus 1 day
SET @ N = @ N + 1 -- add 1 to record
END
ELSE -- if it does not exist
BEGIN
IF (@ N> = 3) -- judge whether the value 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 day
SET @ sD = DATEADD (day, 1, @ sD)
SET @ N = 0 -- Initialization is 0
END
END
-- List qualified records
SELECT * FROM # temp
In the above method, you can change 3 in the sentence "IF (@ N> = 3)" to a variable to facilitate program expansion in the future. One day needs to be changed to 2 consecutive days, 4 days, 5 days, or any day.