By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select *
into t
from (values ('001', convert(date, '2020-05-09'), convert(date, '2020-05-09')),
('123', convert(date, '2020-05-13'), convert(date, '2020-05-16')),
('456', convert(date, '2020-05-15'), convert(date, '2020-05-22')),
('789', convert(date, '2020-05-21'), convert(date, '2020-05-23'))
) v(Ticket, Submit_Date, Complete_Date)
4 rows affected
select v2.week, sum(is_submit), sum(is_complete)
from t cross apply
(values (submit_date, 1, 0), (complete_date, 0, 1)
) v(dte, is_submit, is_complete) cross apply
(values (dateadd(week, datediff(week, 0, v.dte), 0))) v2(week)
group by v2.week
order by v2.week;
| week | (No column name) | (No column name) |
|---|---|---|
| 2020-05-04 00:00:00.000 | 1 | 1 |
| 2020-05-11 00:00:00.000 | 2 | 1 |
| 2020-05-18 00:00:00.000 | 1 | 2 |