Version: Microsoft SQL Server 2014 (SP3-CU4) (KB4500181)

What i'm trying to accomplish here is pull in all the rows where there is a break of less than lets say 40 days between the next row.

So in the first example there is an uninterrupted break every month (roughly). So I would like the query to pull in all records here.

Example 1

CREATE table #Temp1
id Varchar(50),
DT8 int,
series int


In example two there is a gap greater than 40 days between Sep 25 2019 and January 29 2020. So I would like the query to just pull in the most recent subsequent block. In this case it would just be the top record.

Example 2


I have started down this road, and was looking at using LEAD to calculate the number of days between the current and previous rows. I realize I probably need to break the years out to account for the case when moving to a new year or convert it to a real date so that I can use some sql functions to calculate the difference in days for me.

After that I wasn't sure how to go about only returning the most recent consecutive block. Thought I would ask here to see if anyone had any insight on how to accomplish this.

  • 2
    Using window functions is a good approach. Another method is here. What do you want the output to look like?
    – danblack
    Commented Feb 6, 2020 at 0:18
  • 3
    Please, please, always include your version of MySQL - it's very important as they've introduced many important features recently! Also, in future, could you provide your tables as DDL (CREATE TABLE...) in formatted text and your data in the form of DML (INSERT INTO...) - it makes life much easier for those of us trying to help. p.s. welcome to the forum! :-)
    – Vérace
    Commented Feb 6, 2020 at 5:50
  • @danblack, I am mainly concerned with the number of rows returned. Because I want to COUNT() the number of consecutive entries for the most recent block.
    – Trevor
    Commented Feb 6, 2020 at 15:14
  • @Vérace good point on the MySQL version, and thank you for the tip!
    – Trevor
    Commented Feb 6, 2020 at 15:17
  • Why would Date an INT??? So you can enter 20201335?
    – Eric
    Commented Feb 6, 2020 at 15:36

1 Answer 1

cte1 AS 
SELECT series, 
       DATEDIFF(LEAD(STR_TO_DATE(`date`, '%Y%m%d')) OVER (PARTITION BY series
                                                          ORDER BY `date`),
                STR_TO_DATE(`date`, '%Y%m%d')) diff
FROM test
cte2 AS 
SELECT series, 
       COALESCE(SUM(diff > 40) OVER (PARTITION BY series
                                     ORDER BY `date` DESC), 0) grp
FROM cte1
SELECT series, 
FROM cte2 
WHERE grp = 0


  • Thank you Akina, I can see that you have solved it! I don't have access to the STR_TO_DATE function i'm guessing due to my MySQL version. But this should do it for me. Appreciate it.
    – Trevor
    Commented Feb 6, 2020 at 15:49
  • 2
    my MySQL version...? But you've edited your question to show that your server is Version: Microsoft SQL Server 2014. I even edited the tags for the question (also very important for those searching...).
    – Vérace
    Commented Feb 6, 2020 at 16:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.