0

The table looks like this:

User Date
A 2022-08-05
A 2021-08-05
A 2020-08-06
B 2022-08-05
B 2020-07-07

What logic can show me that user A has 3 dates (counted) that are sequential with no gaps in the year, and User B has 2 dates (counted) but does have a gap in the year?

Want the result could look like this:

User Count Sequential
A 3 Y
B 2 N

I simply have no idea how to make this work. I have some working knowledge of SQL Query, but don't know where to start on this one. Thank you.

GeekGirl02134
  • 3
  • 3
  • What is your DBMS? Please edit the question and tag that DBMS. Also, "user" is not an ideal column name as that may interfere with some dbms types. user_id or user_name would be better. Same could probably be said about "date". – Isolated Jul 21 '23 at 21:07
  • MS-SQL, SQL Management Studio, creating reports using SSRS. The column names are irrelevant at this point. I'm just using them as examples. – GeekGirl02134 Jul 21 '23 at 21:16
  • Try, for each user, get the difference in years between the min date and the max date (as years), add 1 and if the result is the same as the count then they are sequential – NickW Jul 21 '23 at 21:18
  • @NickW that sounds logical but unfortunately I do not have enough experience to actually write this code in the query. – GeekGirl02134 Jul 21 '23 at 21:21
  • Search for Gaps and Islands since this is a common name for this. Example https://stackoverflow.com/q/31722337/125981 – Mark Schultheiss Jul 21 '23 at 21:25
  • What if user A had several dates in one year or is it only ever 1 row per year? – Stu Jul 21 '23 at 21:32
  • @Stu - only 1 row per year, does this make it better or worse? – GeekGirl02134 Jul 25 '23 at 15:45

2 Answers2

2

You could use a CTE or sub-query to determine the number of years between Max and Min, and then compare to count using a case expression.

create table my_data (
  user_id varchar(5), 
  some_date date
  );
  
insert into my_data values 
('A', '2022-08-05'),
('A', '2021-08-05'),
('A', '2020-08-06'),
('B', '2022-08-05'),
('B', '2020-07-07');

select user_id, total_rows as total, 
 case when total_rows = max_minus_min then 'Y' else 'N' end as Sequential
from (
  select user_id, year(max(some_date)) - year(min(some_date)) + 1 as max_minus_min, 
   count(*) as total_rows
    from my_data
  group by user_id
)z
order by 1;
user_id total Sequential
A 3 Y
B 2 N

fiddle

Isolated
  • 5,169
  • 1
  • 6
  • 18
0

You can use DATE_ADD to add a year to a particular date and then check.

SELECT
  User,
  COUNT(*) AS Count,
  CASE
    WHEN COUNT(*) > 1 AND 
         NOT EXISTS (
           SELECT 1 
           FROM dates_table t2 
           WHERE t2.User = t1.User 
             AND t2.Date = DATE_ADD(t1.Date, INTERVAL 1 YEAR)
         ) THEN 'N'
    ELSE 'Y'
  END AS Sequential
FROM dates_table t1
GROUP BY User;
Praveen Kumar Ugiri
  • 11
  • 3