select a.RouteCode,a.RouteName,a.DriveDate,t.[11],t.[12],t.[13],t.[14],t.[15] from a,
(
select RouteCode,DriveDate,
sum(case shapecode when 11 then amount else 0 end) [11],
sum(case shapecode when 12 then amount else 0 end) [12],
sum(case shapecode when 13 then amount else 0 end) [13],
sum(case shapecode when 14 then amount else 0 end) [14],
sum(case shapecode when 15 then amount else 0 end) [15]
from B
group by RouteCode,DriveDate
) t
where a.RouteCode = t.RouteCode and a.DriveDate = t.DriveDate
(
select RouteCode,DriveDate,
sum(case shapecode when 11 then amount else 0 end) [11],
sum(case shapecode when 12 then amount else 0 end) [12],
sum(case shapecode when 13 then amount else 0 end) [13],
sum(case shapecode when 14 then amount else 0 end) [14],
sum(case shapecode when 15 then amount else 0 end) [15]
from B
group by RouteCode,DriveDate
) t
where a.RouteCode = t.RouteCode and a.DriveDate = t.DriveDate
set a.[11] = t.[11],
set a.[12] = t.[12],
set a.[13] = t.[13],
set a.[14] = t.[14],
set a.[15] = t.[15]
from a,(
select RouteCode,DriveDate,
sum(case shapecode when 11 then amount else 0 end) [11],
sum(case shapecode when 12 then amount else 0 end) [12],
sum(case shapecode when 13 then amount else 0 end) [13],
sum(case shapecode when 14 then amount else 0 end) [14],
sum(case shapecode when 15 then amount else 0 end) [15]
from B
group by RouteCode,DriveDate
) t
where a.RouteCode = t.RouteCode and a.DriveDate = t.DriveDate
--update update a
set a.[11] = t.[11],
set a.[12] = t.[12],
set a.[13] = t.[13],
set a.[14] = t.[14],
set a.[15] = t.[15]
from a,(
select RouteCode,DriveDate,
sum(case shapecode when 11 then amount else 0 end) [11],
sum(case shapecode when 12 then amount else 0 end) [12],
sum(case shapecode when 13 then amount else 0 end) [13],
sum(case shapecode when 14 then amount else 0 end) [14],
sum(case shapecode when 15 then amount else 0 end) [15]
from B
group by RouteCode,DriveDate
) t
where a.RouteCode = t.RouteCode and a.DriveDate = t.DriveDate ----insertinsert into a(RounteCode,DriveDate,[11],[12],[13],[14],[15])
select RounteCode,DriveDate,[11],[12],[13],[14],[15]
from (
select RouteCode,DriveDate,
sum(case shapecode when 11 then amount else 0 end) [11],
sum(case shapecode when 12 then amount else 0 end) [12],
sum(case shapecode when 13 then amount else 0 end) [13],
sum(case shapecode when 14 then amount else 0 end) [14],
sum(case shapecode when 15 then amount else 0 end) [15]
from B
group by RouteCode,DriveDate
) t
where not exists(select 1 from a where t.routecode=routecode and t.drivedate=drivedate)