SQL Select data between two dates by period

Question

I have a table that keep date data in insert_dttm as smalldatetime and i have a column with name count as integer in the same table. I need to get max count for every days between two datetimes day by day. How can i get it?

Answer 1

Your question is not clear. Is this what you want?

create table t(
insert_dttm smalldatetime,
name_count int);

insert into t values
('2022-01-01',1),
('2022-02-01',1),
('2022-03-01',1),
('2022-01-05',2),
('2022-02-20',2);

select 
name_count,
min(insert_dttm) first_date,
max(insert_dttm) last_date,
datediff(day, min(insert_dttm), max(insert_dttm)) days_between
from t
group by name_count;

name_count | first_date       | last_date        | days_between
---------: | :--------------- | :--------------- | -----------:
         1 | 2022-01-01 00:00 | 2022-03-01 00:00 |           59
         2 | 2022-01-05 00:00 | 2022-02-20 00:00 |           46

db<>fiddle here