select
yy=isnull(a.yy,b.yy),
mm=isnull(a.mm,b.mm),
data1=isnull(a.data1,0),
data2=isnull(b.data2,0)
from
(select yy=year(datetime1),mm=month(datetime1),data1=sum(data1) from table1 group by year(datetime1),month(datetime1)) a
full outer join
(select yy=year(datetime2),mm=month(datetime2),data2=sum(data2) from table2 group by year(datetime2),month(datetime2)) b
on
a.yy=b.yy and a.mm=b.mm
yy=isnull(a.yy,b.yy),
mm=isnull(a.mm,b.mm),
data1=isnull(a.data1,0),
data2=isnull(b.data2,0)
from
(select yy=year(datetime1),mm=month(datetime1),data1=sum(data1) from table1 group by year(datetime1),month(datetime1)) a
full outer join
(select yy=year(datetime2),mm=month(datetime2),data2=sum(data2) from table2 group by year(datetime2),month(datetime2)) b
on
a.yy=b.yy and a.mm=b.mm
from table1 a inner join table2 b on
a.datetime1=b.datetime2
group by left(convert(char,datetime1,12),4)
order by left(convert(char,datetime1,12),4)
(select datetime=case when a.datetime1 is null b.datetime2 else a.datetime1 end
from table1 a
full outer join table2 b
on datetime1=b.datetime2)a
group by convert(char(5),datetime,120)
select datetime1=convert(char(5),datetime,120),Sum=count(1) from
(select datetime=case when a.datetime1 is null then b.datetime2 else a.datetime1 end
from table1 a
full outer join table2 b
on datetime1=b.datetime2)a
group by convert(char(5),datetime,120)
select datetime=convert(char(5),datetime,120),Sum1=sum(data1),sum2=sum(data2) from
(select datetime=case when a.datetime1 is null then b.datetime2 else a.datetime1 end,data1,data2
from table1 a
full outer join table2 b
on datetime1=b.datetime2)a
group by convert(char(5),datetime,120)