T-SQL to find gaps in Date field of table

Question

I'm inexperienced in asking these questions in a forum. I'm sure there's a more elegant way to display this code, etc. I hope I can explain what I'm after here. I have a simple table with a record ID and a date column in it. You can run this simple code to create the table if you'd like.

IF OBJECT_ID('tempdb..#tmptbl') IS NOT NULL
BEGIN
DROP TABLE #tmptbl
END

create table #tmptbl (recid int, docdate date)
insert into #tmptbl
values
(1, '11/16/19'),(1, '11/15/19'),(1, '11/14/19'),(1, '11/13/19'),(1, '10/29/19'),(1, '10/27/19'),(1, '10/26/19'),(2, '10/31/19'),(2, '10/30/19'),(2, '10/29/19'),(2, '10/1/19'),(3, '11/16/19'),(3, '11/15/19'),(3, '11/13/19'),(3, '8/9/19'),(3, '8/8/19'),(3, '8/7/19')

--select * from #tmptbl order by 1, 2 desc

Here is a picture of a sample in Excel

Here is a picture of a sample in Excel. The highlighted rows are the rows I want to return in a query.

Logic for the select statement to return the rows needed: For each recid, determine if there is a record on 11/16/19 (this can be a passed parameter but it will always be just one particular date). If the recid does not have a record with 11/16/19 on it, return no rows for that recid. If it does, I need to return the consecutive dated rows up to that date. When there is a gap in the date for the recid, I can omit the rest of the rows for that recid. I've tried to explain the logic in comments in the picture.

Can you help give me some examples of how to accomplish this using T-SQL? ...Return only the consecutive dated rows for each recid up to the provided date (i.e. 11/16/19 in my example).

Thank you.

How far did you get with this, did you got the list of records that include the date 11/16/19? What is the use-case? From the question it sounds as if you might want to pass a date parameter that can be less than the maximum date? But then there is no example of a date after 2019-11-16 in the sample data so it's not totally clear what you are aiming for. — Mic, Nov 20 '19 at 19:21

score 0 · Answer 1 · answered Nov 20 '19 at 19:12

You can do this easily with a recursive CTE

declare @docDate datetime = '2019-11-16';
with cte as (select recid
                    , docdate
             from   #tmptbl
             where  docdate = @docDate
             union all
             select t.recid
                    , t.docdate
             from   #tmptbl as t
             join   cte on  t.recid = cte.recid
                        and t.docdate = dateadd(day, -1, cte.docdate))
select  *
from    cte

T-SQL to find gaps in Date field of table

1 Answers1