0

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
) 

INSERT INTO #Temp1
VALUES
('147501',20181022,1),
('147502',20181115,1),
('147503',20181211,1),
('147504',20190110,1),
('147506',20190306,1),
('147505',20190205,1),
('147507',20190402,1),
('147508',20190429,1),
('147509',20190529,1),
('147510',20190603,1),
('147511',20190703,1),
('147512',20190802,1),
('177901',20190905,1),
('177902',20191001,1),
('177903',20191108,1),
('177904',20191205,1),
('190401',20200103,1),
('190402',20200205,1)

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

INSERT INTO #Temp1
VALUES
('164701',20190605,2),
('164702',20190703,2),
('164703',20190802,2),
('164704',20190904,2),
('164705',20190925,2),
('189101',20200129,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 Feb 6 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 Feb 6 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 Feb 6 at 15:14
  • @Vérace good point on the MySQL version, and thank you for the tip! – Trevor Feb 6 at 15:17
  • Why would Date an INT??? So you can enter 20201335? – Eric Feb 6 at 15:36
1
WITH 
cte1 AS 
(
SELECT series, 
       id, 
       `date`, 
       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, 
       id, 
       `date`, 
       COALESCE(SUM(diff > 40) OVER (PARTITION BY series
                                     ORDER BY `date` DESC), 0) grp
FROM cte1
)
SELECT series, 
       id, 
       `date` 
FROM cte2 
WHERE grp = 0
ORDER BY 1,3

fiddle

| improve this answer | |
  • 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 Feb 6 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 Feb 6 at 16:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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