CODEWITHSUNDEEP
sqlsql-server
Maor
Maor

Reputation: 9

Filtering consecutive dates ranges using SQL Server

I want to filter categories that only have consecutive dates.

I will explain with an example.

My table is

|         ID         |     Category    |        Date         |
|--------------------|-----------------|---------------------|
|          1         |         1       |          01-04-2021 |
|          2         |         1       |          02-04-2021 |
|          3         |         2       |          01-03-2021 |
|          4         |         2       |          04-03-2021 |
|          5         |         2       |          01-02-2010 |
|          6         |         3       |          02-02-2010 |
|          7         |         3       |          03-02-2010 |
|          8         |         4       |          03-02-2010 |

Expected output:

|    Category    |
|----------------|
|        1       |
|        3       |
|        4       |

I would like to filter my data such as I only have categories that do not contain consecutive dates.

Upvotes: 0

Views: 320

Answers (4)

Serg
Serg

Reputation: 22811

Categories where the sequence of dates is the same as the sequence of ids.

with cte as (
   select [category],  
       row_number() over (partition by [category] order by [date], [id]) 
       - row_number() over (partition by [category] order by [id]) drn
)
select [category]
from cte
group by [category]
having sum(abs(drn)) = 0;

Upvotes: 0

lptr
lptr

Reputation: 6788

… for unique dates per category

select category
from mytable
group by category
having max(Date) = dateadd(day, count(*)-1, min(Date))

Upvotes: 1

Anton Grig
Anton Grig

Reputation: 1719

select distinct category 
from dates
where category not in ( 
select distinct category
from (
select category, [date], 
       row_number() over (partition by category order by [date]) as days_cnt, 
       min([date]) over (partition by category) as min_date
from dates
group by category, [date]
) as c
where c.[date]<>dateadd(d, c.days_cnt-1, c.min_date))
order by category

Upvotes: 0

Aviv Goldgeier
Aviv Goldgeier

Reputation: 809

Here's one way. You'll have to maybe adjust it for your particular flavor of SQL.

WITH a AS (
  SELECT
    category,
    DATEDIFF('days', date, LAG(date) OVER (PARTITION BY category ORDER BY 
  date)) AS days_apart
  FROM tbl
),
b AS (
  SELECT
    category,
    MAX(days_apart) AS max_days_apart
  FROM a
  GROUP BY 1
)

SELECT
  category
FROM b
WHERE max_days_apart IS NULL OR max_days_apart = 1

Upvotes: 0

Related Questions