22,300
社区成员




select isnull(a.syear,b.syear) as syear,
isnull(a.DepartmentID,b.DepartmentID) as DepartmentID,
isnull(a.CommodityID,b.CommodityID) as CommodityID
from
(select sYear ,DepartmentID,CommodityID,
sum(case when sMOnth =1 then isnull(quantity,0) else 0 end) as P01,
sum(case when sMOnth =2 then isnull(quantity,0) else 0 end) as P02,
sum(case when sMOnth =3 then isnull(quantity,0) else 0 end) as P03,
sum(case when sMOnth =4 then isnull(quantity,0) else 0 end) as P04,
sum(case when sMOnth =5 then isnull(quantity,0) else 0 end) as P05,
sum(case when sMOnth =6 then isnull(quantity,0) else 0 end) as P06,
sum(case when sMOnth =7 then isnull(quantity,0) else 0 end) as P07,
sum(case when sMOnth =8 then isnull(quantity,0) else 0 end) as P08,
sum(case when sMOnth =9 then isnull(quantity,0) else 0 end) as P09,
sum(case when sMOnth =10 then isnull(quantity,0) else 0 end) as P10,
sum(case when sMOnth =11 then isnull(quantity,0) else 0 end) as P11,
sum(case when sMOnth =12 then isnull(quantity,0) else 0 end) as P12
from tSalePlan
group by sYear,DepartmentID,CommodityID
) as a
full join
(select year(sDate) as sYear ,DepartmentID,CommodityID,
sum(case when month(sDate) =1 then isnull(quantity,0) else 0 end) as S01,
sum(case when month(sDate) =2 then isnull(quantity,0) else 0 end) as S02,
sum(case when month(sDate) =3 then isnull(quantity,0) else 0 end) as S03,
sum(case when month(sDate) =4 then isnull(quantity,0) else 0 end) as S04,
sum(case when month(sDate) =5 then isnull(quantity,0) else 0 end) as S05,
sum(case when month(sDate) =6 then isnull(quantity,0) else 0 end) as S06,
sum(case when month(sDate) =7 then isnull(quantity,0) else 0 end) as S07,
sum(case when month(sDate) =8 then isnull(quantity,0) else 0 end) as S08,
sum(case when month(sDate) =9 then isnull(quantity,0) else 0 end) as S09,
sum(case when month(sDate) =10 then isnull(quantity,0) else 0 end) as S10,
sum(case when month(sDate) =11 then isnull(quantity,0) else 0 end) as S11,
sum(case when month(sDate) =12 then isnull(quantity,0) else 0 end) as S12
from tSaleOrder
group by year(sDate),DepartmentID,CommodityID) as b
on a.sYear =b.sYear and a.DepartmentID=b.DepartmentID and a.CommodityID =b.CommodityID
order by a.sYear,a.DepartmentID,a.CommodityID
on a.sYear =b.sYear and a.DepartmentID=b.DepartmentID and a.CommodityID =b.CommodityID
===》
on rtrim(a.sYear) = rtrim(b.sYear)
and ltrim(rtrim(a.DepartmentID))=ltrim(rtrim(b.DepartmentID))
and ltrim(rtrim(a.CommodityID)) =ltrim(rtrim(b.CommodityID))
select isnull(a.syear,b.syear) as syear,
isnull(a.DepartmentID,b.DepartmentID) as DepartmentID,
isnull(a.CommodityID,b.CommodityID) as CommodityID
a.*,b.*
isnull(a.P01,0) - isnull(b.S01,0) as R01,
isnull(a.P02,0) - isnull(b.S01,0) as R02,
isnull(a.P03,0) - isnull(b.S01,0) as R03,
isnull(a.P04,0) - isnull(b.S01,0) as R04,
isnull(a.P05,0) - isnull(b.S01,0) as R05,
isnull(a.P06,0) - isnull(b.S01,0) as R06,
isnull(a.P07,0) - isnull(b.S01,0) as R07,
isnull(a.P08,0) - isnull(b.S01,0) as R08,
isnull(a.P09,0) - isnull(b.S01,0) as R09,
isnull(a.P10,0) - isnull(b.S01,0) as R10,
isnull(a.P11,0) - isnull(b.S01,0) as R11,
isnull(a.P12,0) - isnull(b.S01,0) as R12
from
(select sYear ,DepartmentID,CommodityID,
sum(case when sMOnth =1 then isnull(quantity,0) else 0 end) as P01,
sum(case when sMOnth =2 then isnull(quantity,0) else 0 end) as P02,
sum(case when sMOnth =3 then isnull(quantity,0) else 0 end) as P03,
sum(case when sMOnth =4 then isnull(quantity,0) else 0 end) as P04,
sum(case when sMOnth =5 then isnull(quantity,0) else 0 end) as P05,
sum(case when sMOnth =6 then isnull(quantity,0) else 0 end) as P06,
sum(case when sMOnth =7 then isnull(quantity,0) else 0 end) as P07,
sum(case when sMOnth =8 then isnull(quantity,0) else 0 end) as P08,
sum(case when sMOnth =9 then isnull(quantity,0) else 0 end) as P09,
sum(case when sMOnth =10 then isnull(quantity,0) else 0 end) as P10,
sum(case when sMOnth =11 then isnull(quantity,0) else 0 end) as P11,
sum(case when sMOnth =12 then isnull(quantity,0) else 0 end) as P12
from tSalePlan
group by sYear,DepartmentID,CommodityID
) as a
full join
(select year(sDate) as sYear ,DepartmentID,CommodityID,
sum(case when month(sDate) =1 then isnull(quantity,0) else 0 end) as S01,
sum(case when month(sDate) =2 then isnull(quantity,0) else 0 end) as S02,
sum(case when month(sDate) =3 then isnull(quantity,0) else 0 end) as S03,
sum(case when month(sDate) =4 then isnull(quantity,0) else 0 end) as S04,
sum(case when month(sDate) =5 then isnull(quantity,0) else 0 end) as S05,
sum(case when month(sDate) =6 then isnull(quantity,0) else 0 end) as S06,
sum(case when month(sDate) =7 then isnull(quantity,0) else 0 end) as S07,
sum(case when month(sDate) =8 then isnull(quantity,0) else 0 end) as S08,
sum(case when month(sDate) =9 then isnull(quantity,0) else 0 end) as S09,
sum(case when month(sDate) =10 then isnull(quantity,0) else 0 end) as S10,
sum(case when month(sDate) =11 then isnull(quantity,0) else 0 end) as S11,
sum(case when month(sDate) =12 then isnull(quantity,0) else 0 end) as S12
from tSaleOrder
group by year(sDate),DepartmentID,CommodityID) as b
on a.sYear =b.sYear and a.DepartmentID=b.DepartmentID and a.CommodityID =b.CommodityID
order by a.sYear,a.DepartmentID,a.CommodityID
--加个 sDate between '20080101' AND '20081231' 的条件试试
select isnull(a.syear,b.syear) as syear,
isnull(a.DepartmentID,b.DepartmentID) as DepartmentID,
isnull(a.CommodityID,b.CommodityID) as CommodityID
from
(select sYear ,DepartmentID,CommodityID,
sum(case when sMOnth =1 then isnull(quantity,0) else 0 end) as P01,
sum(case when sMOnth =2 then isnull(quantity,0) else 0 end) as P02,
sum(case when sMOnth =3 then isnull(quantity,0) else 0 end) as P03,
sum(case when sMOnth =4 then isnull(quantity,0) else 0 end) as P04,
sum(case when sMOnth =5 then isnull(quantity,0) else 0 end) as P05,
sum(case when sMOnth =6 then isnull(quantity,0) else 0 end) as P06,
sum(case when sMOnth =7 then isnull(quantity,0) else 0 end) as P07,
sum(case when sMOnth =8 then isnull(quantity,0) else 0 end) as P08,
sum(case when sMOnth =9 then isnull(quantity,0) else 0 end) as P09,
sum(case when sMOnth =10 then isnull(quantity,0) else 0 end) as P10,
sum(case when sMOnth =11 then isnull(quantity,0) else 0 end) as P11,
sum(case when sMOnth =12 then isnull(quantity,0) else 0 end) as P12
from tSalePlan
group by sYear,DepartmentID,CommodityID
) as a
full join
(select year(sDate) as sYear ,DepartmentID,CommodityID,
sum(case when month(sDate) =1 then isnull(quantity,0) else 0 end) as S01,
sum(case when month(sDate) =2 then isnull(quantity,0) else 0 end) as S02,
sum(case when month(sDate) =3 then isnull(quantity,0) else 0 end) as S03,
sum(case when month(sDate) =4 then isnull(quantity,0) else 0 end) as S04,
sum(case when month(sDate) =5 then isnull(quantity,0) else 0 end) as S05,
sum(case when month(sDate) =6 then isnull(quantity,0) else 0 end) as S06,
sum(case when month(sDate) =7 then isnull(quantity,0) else 0 end) as S07,
sum(case when month(sDate) =8 then isnull(quantity,0) else 0 end) as S08,
sum(case when month(sDate) =9 then isnull(quantity,0) else 0 end) as S09,
sum(case when month(sDate) =10 then isnull(quantity,0) else 0 end) as S10,
sum(case when month(sDate) =11 then isnull(quantity,0) else 0 end) as S11,
sum(case when month(sDate) =12 then isnull(quantity,0) else 0 end) as S12
from tSaleOrder
where sDate between '20080101' AND '20081231'
group by year(sDate),DepartmentID,CommodityID) as b
on a.sYear =b.sYear and a.DepartmentID=b.DepartmentID and a.CommodityID =b.CommodityID
order by a.sYear,a.DepartmentID,a.CommodityID