By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Create table t (Ticket varchar(10), Submit_Date date, Complete_Date date)
Insert into t
Values
('001','05/09/2020','05/09/2020'),
('123','05/13/2020','05/16/2020'),
('456','05/15/2020','05/22/2020'),
('456','04/15/2020','07/22/2020'),
('789','05/21/2020','05/23/2020')
;With tCTE as
(select DATEPART(ww,Submit_Date) as wkNumSubmit, DATEPART(ww,Complete_Date) as wkNumComplete, * from t),
allDays (wkNum) as
(Select wkNumSubmit from tCTE Union Select wkNumComplete from tCTE )
Select * from allDays a
Left Join (select wkNumSubmit, Count(distinct Submit_Date) as Submitted from tCTE Group By wkNumSubmit)
as s on a.wkNum = s.wkNumSubmit
Left Join (select wkNumComplete, Count(distinct Complete_Date) as Worked from tCTE Group By wkNumComplete)
as w on a.wkNum = w.wkNumComplete
| wkNum | wkNumSubmit | Submitted | wkNumComplete | Worked |
|---|---|---|---|---|
| 16 | 16 | 1 | null | null |
| 19 | 19 | 1 | 19 | 1 |
| 20 | 20 | 2 | 20 | 1 |
| 21 | 21 | 1 | 21 | 2 |
| 30 | null | null | 30 | 1 |