Group by one column and return several columns on multiple conditions – T-SQL

I have two tables which I can generate with SELECT statements (joining multiple tables) as follows:

Table 1:

ID	Site	type	time
1	Dallas	2	01-01-2021
2	Denver	1	02-01-2021
3	Chicago	1	03-01-2021
4	Chicago	2	29-11-2020
5	Denver	1	28-02-2020
6	Toronto	2	11-05-2019

Table 2:

ID	Site	collected	deposited
1	Denver	NULL	29-01-2021
2	Denver	01-04-2021	29-01-2021
3	Chicago	NULL	19-01-2020
4	Dallas	NULL	29-01-2019
5	Winnipeg	13-02-2021	17-01-2021
6	Toronto	14-02-2020	29-01-2020

I would like the result to be grouped by Site, having on each column the COUNT of type=1 , type=2, deposited and collected, all of the 4 columns between a selected time interval. Example: (interval between 01-06-2020 and 01-06-2021:

Site	type1	type2	deposited	collected
Dallas	0	1	0	0
Denver	1	0	2	1
Chicago	1	1	0	0
Toronto	0	0	0	0
Winnipeg	0	0	1	1

Answer

How about union all and aggregation?

select site,
       sum(case when type = 1 then 1 else 0 end) as type_1,
       sum(case when type = 2 then 1 else 0 end) as type_2,
       sum(deposited) as deposited, sum(collected) as collected
from ((select site, type, 0 as deposited, 0 as collected
       from table1
      ) union all
      (select site, null, 
              (case when deposited is not null then 1 else 0 end),
              (case when collected is not null then 1 else 0 end)
       from table2
      )
     ) t12
group by site;