平衡表分配后公式

分配后公式：重工:气瓶:撬装:阀门=0.19:0.36:0.28:0.17

select b.Department,
case when Department='阀门' then (isnull((select SUM(Purchase) from
PriorToCapDistributionItem e inner join PriorToCapDistribution f on
e.MainID=f.MainID
inner join JHOA_Approve c
on f.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0 where f.Department='总部'
[@ and f.[Year]=:year @] [@ and f.[Month]=:month @]
group by f.Department
),0)*0.17)+SUM(isnull(Purchase,0))
when Department='气瓶' then (isnull((select SUM(Purchase) from
PriorToCapDistributionItem e inner join PriorToCapDistribution f on
e.MainID=f.MainID
inner join JHOA_Approve c
on f.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
where f.Department='总部'
[@ and f.[Year]=:year @] [@ and f.[Month]=:month @]
group by f.Department
),0)*0.36)+SUM(isnull(Purchase,0))
when Department='撬装' then (isnull((select SUM(Purchase) from
PriorToCapDistributionItem e inner join PriorToCapDistribution f on
e.MainID=f.MainID
inner join JHOA_Approve c
on f.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
where f.Department='总部'
[@ and f.[Year]=:year @] [@ and f.[Month]=:month @]
group by f.Department
),0)*0.28)+SUM(isnull(Purchase,0))
when Department='重工' then (isnull((select SUM(Purchase) from
PriorToCapDistributionItem e inner join PriorToCapDistribution f on
e.MainID=f.MainID
inner join JHOA_Approve c
on f.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
where f.Department='总部'
[@ and f.[Year]=:year @] [@ and f.[Month]=:month @]
group by f.Department
 ),0)*0.19)+SUM(isnull(Purchase,0))
when Department='韩中' then SUM(isnull(Purchase,0))
when Department='工程事业部' then SUM(isnull(Purchase,0))
else 0
end  as Purchase,

 

 

 

 

 

 

case when Department='阀门' then (isnull((select SUM(Outsourcing) from
PriorToCapDistributionItem e inner join PriorToCapDistribution f on
e.MainID=f.MainID
inner join JHOA_Approve c
on f.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
where f.Department='总部'
[@ and f.[Year]=:year @] [@ and f.[Month]=:month @]
group by f.Department
 ),0)*0.17)+SUM(isnull(Outsourcing,0))
when Department='气瓶' then (isnull((select SUM(Outsourcing) from
PriorToCapDistributionItem e inner join PriorToCapDistribution f on
e.MainID=f.MainID
inner join JHOA_Approve c
on f.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
where f.Department='总部'
[@ and f.[Year]=:year @] [@ and f.[Month]=:month @]
group by f.Department
 ),0)*0.36)+SUM(isnull(Outsourcing,0))
when Department='撬装' then (isnull((select SUM(Outsourcing) from
PriorToCapDistributionItem e inner join PriorToCapDistribution f on
e.MainID=f.MainID
inner join JHOA_Approve c
on f.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
where f.Department='总部'
[@ and f.[Year]=:year @] [@ and f.[Month]=:month @]
group by f.Department
 ),0)*0.28)+SUM(isnull(Outsourcing,0))
when Department='重工' then (isnull((select SUM(Outsourcing) from
PriorToCapDistributionItem e inner join PriorToCapDistribution f on
e.MainID=f.MainID
inner join JHOA_Approve c
on f.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
where f.Department='总部'
[@ and f.[Year]=:year @] [@ and f.[Month]=:month @]
group by f.Department
 ),0)*0.19)+SUM(isnull(Outsourcing,0))
when Department='韩中' then SUM(isnull(Outsourcing,0))
when Department='工程事业部' then SUM(isnull(Outsourcing,0))
else 0
end  as Outsourcing,

 

 

 

 

 

case when Department='阀门' then (isnull((select SUM(EngineeringEquip) from
PriorToCapDistributionItem e inner join PriorToCapDistribution f on
e.MainID=f.MainID
inner join JHOA_Approve c
on f.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
where f.Department='总部'
[@ and f.[Year]=:year @] [@ and f.[Month]=:month @]
group by f.Department
 ),0)*0.17)+SUM(isnull(EngineeringEquip,0))
when Department='气瓶' then (isnull((select SUM(EngineeringEquip) from
PriorToCapDistributionItem e inner join PriorToCapDistribution f on
e.MainID=f.MainID
inner join JHOA_Approve c
on f.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
where f.Department='总部'
[@ and f.[Year]=:year @] [@ and f.[Month]=:month @]
group by f.Department
 ),0)*0.36)+SUM(isnull(EngineeringEquip,0))
when Department='撬装' then (isnull((select SUM(EngineeringEquip) from
PriorToCapDistributionItem e inner join PriorToCapDistribution f on
e.MainID=f.MainID
inner join JHOA_Approve c
on f.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
where f.Department='总部'
[@ and f.[Year]=:year @] [@ and f.[Month]=:month @]
group by f.Department
 ),0)*0.28)+SUM(isnull(EngineeringEquip,0))
when Department='重工' then (isnull((select SUM(EngineeringEquip) from
PriorToCapDistributionItem e inner join PriorToCapDistribution f on
e.MainID=f.MainID inner join JHOA_Approve c
on f.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0 where f.Department='总部'
[@ and f.[Year]=:year @] [@ and f.[Month]=:month @]
group by f.Department
 ),0)*0.19)+SUM(isnull(EngineeringEquip,0))
when Department='韩中' then SUM(isnull(EngineeringEquip,0))
when Department='工程事业部' then SUM(isnull(EngineeringEquip,0))
else 0
end  as EngineeringEquip,

 

 

 

case when Department='阀门' then (isnull((select SUM(Income) from
PriorToCapDistributionItem e inner join PriorToCapDistribution f on
e.MainID=f.MainID
inner join JHOA_Approve c
on f.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
where f.Department='总部'
[@ and f.[Year]=:year @] [@ and f.[Month]=:month @]
group by f.Department
 ),0)*0.17)+SUM(isnull(Income,0))
when Department='气瓶' then (isnull((select SUM(Income) from
PriorToCapDistributionItem e inner join PriorToCapDistribution f on
e.MainID=f.MainID
inner join JHOA_Approve c
on f.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
where f.Department='总部'
[@ and f.[Year]=:year @] [@ and f.[Month]=:month @]
group by f.Department
 ),0)*0.36)+SUM(isnull(Income,0))
when Department='撬装' then (isnull((select SUM(Income) from
PriorToCapDistributionItem e inner join PriorToCapDistribution f on
e.MainID=f.MainID
inner join JHOA_Approve c
on f.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
where f.Department='总部'
[@ and f.[Year]=:year @] [@ and f.[Month]=:month @]
group by f.Department
 ),0)*0.28)+SUM(isnull(Income,0))
when Department='重工' then (isnull((select SUM(Income) from
PriorToCapDistributionItem e inner join PriorToCapDistribution f on
e.MainID=f.MainID
inner join JHOA_Approve c
on f.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
where f.Department='总部'
[@ and f.[Year]=:year @] [@ and f.[Month]=:month @]
group by f.Department
 ),0)*0.19)+SUM(isnull(Income,0))
when Department='韩中' then SUM(isnull(Income,0))
when Department='工程事业部' then SUM(isnull(Income,0))
else 0
end  as Income,

 

 

case when Department='阀门' then (isnull((select SUM(Tax) from
PriorToCapDistributionItem e inner join PriorToCapDistribution f on
e.MainID=f.MainID
inner join JHOA_Approve c
on f.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
where f.Department='总部'
[@ and f.[Year]=:year @] [@ and f.[Month]=:month @]
group by f.Department
 ),0)*0.17)+SUM(isnull(Tax,0))
when Department='气瓶' then (isnull((select SUM(Tax) from
PriorToCapDistributionItem e inner join PriorToCapDistribution f on
e.MainID=f.MainID
inner join JHOA_Approve c
on f.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
where f.Department='总部'
[@ and f.[Year]=:year @] [@ and f.[Month]=:month @]
group by f.Department
 ),0)*0.36)+SUM(isnull(Tax,0))
when Department='撬装' then (isnull((select SUM(Tax) from
PriorToCapDistributionItem e inner join PriorToCapDistribution f on
e.MainID=f.MainID
inner join JHOA_Approve c
on f.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
where f.Department='总部'
[@ and f.[Year]=:year @] [@ and f.[Month]=:month @]
group by f.Department
 ),0)*0.28)+SUM(isnull(Tax,0))
when Department='重工' then (isnull((select SUM(Tax) from
PriorToCapDistributionItem e inner join PriorToCapDistribution f on
e.MainID=f.MainID
inner join JHOA_Approve c
on f.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
where f.Department='总部'
[@ and f.[Year]=:year @] [@ and f.[Month]=:month @]
 group by f.Department
 ),0)*0.19)+SUM(isnull(Tax,0))
when Department='韩中' then SUM(isnull(Tax,0))
when Department='工程事业部' then SUM(isnull(Tax,0))
else 0
end  as Tax,

 

case when Department='阀门' then (isnull((select SUM(LoanInterest) from
PriorToCapDistributionItem e inner join PriorToCapDistribution f on
e.MainID=f.MainID
inner join JHOA_Approve c
on f.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
where f.Department='总部'
[@ and f.[Year]=:year @] [@ and f.[Month]=:month @]
group by f.Department
 ),0)*0.17)+SUM(isnull(LoanInterest,0))
when Department='气瓶' then (isnull((select SUM(LoanInterest) from
PriorToCapDistributionItem e inner join PriorToCapDistribution f on
e.MainID=f.MainID
inner join JHOA_Approve c
on f.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
where f.Department='总部'
[@ and f.[Year]=:year @] [@ and f.[Month]=:month @]
group by f.Department
),0)*0.36)+SUM(isnull(LoanInterest,0))
when Department='撬装' then (isnull((select SUM(LoanInterest) from
PriorToCapDistributionItem e inner join PriorToCapDistribution f on
e.MainID=f.MainID
inner join JHOA_Approve c
on f.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
where f.Department='总部'
[@ and f.[Year]=:year @] [@ and f.[Month]=:month @]
group by f.Department
),0)*0.28)+SUM(isnull(LoanInterest,0))
when Department='重工' then (isnull((select SUM(LoanInterest) from
PriorToCapDistributionItem e inner join PriorToCapDistribution f on
e.MainID=f.MainID
inner join JHOA_Approve c
on f.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
where f.Department='总部'
[@ and f.[Year]=:year @] [@ and f.[Month]=:month @]
group by f.Department
 ),0)*0.19)+SUM(isnull(LoanInterest,0))
when Department='韩中' then SUM(isnull(LoanInterest,0))
when Department='工程事业部' then SUM(isnull(LoanInterest,0))
else 0
end  as LoanInterest,

case when Department='阀门' then (isnull((select SUM(Others) from
PriorToCapDistributionItem e inner join PriorToCapDistribution f on
e.MainID=f.MainID
inner join JHOA_Approve c
on f.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
where f.Department='总部'
[@ and f.[Year]=:year @] [@ and f.[Month]=:month @]
group by f.Department
 ),0)*0.17)+SUM(isnull(Others,0))
when Department='气瓶' then (isnull((select SUM(Others) from
PriorToCapDistributionItem e inner join PriorToCapDistribution f on
e.MainID=f.MainID
inner join JHOA_Approve c
on f.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
where f.Department='总部'
[@ and f.[Year]=:year @] [@ and f.[Month]=:month @]
group by f.Department
 ),0)*0.36)+SUM(isnull(Others,0))
when Department='撬装' then (isnull((select SUM(Others) from
PriorToCapDistributionItem e inner join PriorToCapDistribution f on
e.MainID=f.MainID
inner join JHOA_Approve c
on f.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
where f.Department='总部'
[@ and f.[Year]=:year @] [@ and f.[Month]=:month @]
group by f.Department
 ),0)*0.28)+SUM(isnull(Others,0))
when Department='重工' then (isnull((select SUM(Others) from
PriorToCapDistributionItem e inner join PriorToCapDistribution f on
e.MainID=f.MainID
inner join JHOA_Approve c
on f.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
where f.Department='总部'
[@ and f.[Year]=:year @] [@ and f.[Month]=:month @]
group by f.Department
 ),0)*0.19)+SUM(isnull(Others,0))
when Department='韩中' then SUM(isnull(Others,0))
when Department='工程事业部' then SUM(isnull(Others,0))
else 0
end  as Others,

 

case when Department='阀门' then (isnull((select SUM(AdmDepartment) from
PriorToCapDistributionItem e inner join PriorToCapDistribution f on
e.MainID=f.MainID
inner join JHOA_Approve c
on f.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
where f.Department='总部'
[@ and f.[Year]=:year @] [@ and f.[Month]=:month @]
group by f.Department
 ),0)*0.17)+SUM(isnull(AdmDepartment,0))
when Department='气瓶' then (isnull((select SUM(AdmDepartment) from
PriorToCapDistributionItem e inner join PriorToCapDistribution f on
e.MainID=f.MainID
inner join JHOA_Approve c
on f.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
where f.Department='总部'
[@ and f.[Year]=:year @] [@ and f.[Month]=:month @]
group by f.Department
 ),0)*0.36)+SUM(isnull(AdmDepartment,0))
when Department='撬装' then (isnull((select SUM(AdmDepartment) from
PriorToCapDistributionItem e inner join PriorToCapDistribution f on
e.MainID=f.MainID
inner join JHOA_Approve c
on f.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
where f.Department='总部'
[@ and f.[Year]=:year @] [@ and f.[Month]=:month @]
group by f.Department
 ),0)*0.28)+SUM(isnull(AdmDepartment,0))
when Department='重工' then (isnull((select SUM(AdmDepartment) from
PriorToCapDistributionItem e inner join PriorToCapDistribution f on
e.MainID=f.MainID
inner join JHOA_Approve c
on f.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
where f.Department='总部'
[@ and f.[Year]=:year @] [@ and f.[Month]=:month @]
group by f.Department
 ),0)*0.19)+SUM(isnull(AdmDepartment,0))
when Department='韩中' then SUM(isnull(AdmDepartment,0))
when Department='工程事业部' then SUM(isnull(AdmDepartment,0))
else 0
end  as AdmDepartment,
case when Department='阀门' then (isnull((select SUM(ListingOffice) from
PriorToCapDistributionItem e inner join PriorToCapDistribution f on
e.MainID=f.MainID
inner join JHOA_Approve c
on f.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
where f.Department='总部'
[@ and f.[Year]=:year @] [@ and f.[Month]=:month @]
group by f.Department
 ),0)*0.17)+SUM(isnull(ListingOffice,0))
when Department='气瓶' then (isnull((select SUM(ListingOffice) from
PriorToCapDistributionItem e inner join PriorToCapDistribution f on
e.MainID=f.MainID
inner join JHOA_Approve c
on f.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
where f.Department='总部'
[@ and f.[Year]=:year @] [@ and f.[Month]=:month @]
group by f.Department
 ),0)*0.36)+SUM(isnull(ListingOffice,0))
when Department='撬装' then (isnull((select SUM(ListingOffice) from
PriorToCapDistributionItem e inner join PriorToCapDistribution f on
e.MainID=f.MainID
inner join JHOA_Approve c
on f.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
where f.Department='总部'
[@ and f.[Year]=:year @] [@ and f.[Month]=:month @]
group by f.Department
 ),0)*0.28)+SUM(isnull(ListingOffice,0))
when Department='重工' then (isnull((select SUM(ListingOffice) from
PriorToCapDistributionItem e inner join PriorToCapDistribution f on
e.MainID=f.MainID
inner join JHOA_Approve c
on f.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
where f.Department='总部'
[@ and f.[Year]=:year @] [@ and f.[Month]=:month @]
group by f.Department
 ),0)*0.19)+SUM(isnull(ListingOffice,0))
when Department='韩中' then SUM(isnull(ListingOffice,0))
when Department='工程事业部' then SUM(isnull(ListingOffice,0))
else 0
end  as ListingOffice,
case when Department='阀门' then (isnull((select SUM(EngineeringCenter) from
PriorToCapDistributionItem e inner join PriorToCapDistribution f on
e.MainID=f.MainID
inner join JHOA_Approve c
on f.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
where f.Department='总部'
[@ and f.[Year]=:year @] [@ and f.[Month]=:month @]
group by f.Department
 ),0)*0.17)+SUM(isnull(EngineeringCenter,0))
when Department='气瓶' then (isnull((select SUM(EngineeringCenter) from
PriorToCapDistributionItem e inner join PriorToCapDistribution f on
e.MainID=f.MainID
inner join JHOA_Approve c
on f.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
where f.Department='总部'
[@ and f.[Year]=:year @] [@ and f.[Month]=:month @]
group by f.Department
 ),0)*0.36)+SUM(isnull(EngineeringCenter,0))
when Department='撬装' then (isnull((select SUM(EngineeringCenter) from
PriorToCapDistributionItem e inner join PriorToCapDistribution f on
e.MainID=f.MainID
inner join JHOA_Approve c
on f.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
where f.Department='总部'
[@ and f.[Year]=:year @] [@ and f.[Month]=:month @]
group by f.Department
 ),0)*0.28)+SUM(isnull(EngineeringCenter,0))
when Department='重工' then (isnull((select SUM(EngineeringCenter) from
PriorToCapDistributionItem e inner join PriorToCapDistribution f on
e.MainID=f.MainID
inner join JHOA_Approve c
on f.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
where f.Department='总部'
[@ and f.[Year]=:year @] [@ and f.[Month]=:month @]
group by f.Department
 ),0)*0.19)+SUM(isnull(EngineeringCenter,0))
when Department='韩中' then SUM(isnull(EngineeringCenter,0))
when Department='工程事业部' then SUM(isnull(EngineeringCenter,0))
else 0
end  as EngineeringCenter,
case when Department='阀门' then (isnull((select SUM(Finance) from
PriorToCapDistributionItem e inner join PriorToCapDistribution f on
e.MainID=f.MainID
inner join JHOA_Approve c
on f.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
where f.Department='总部'
[@ and f.[Year]=:year @] [@ and f.[Month]=:month @]
group by f.Department
 ),0)*0.17)+SUM(isnull(Finance,0))
when Department='气瓶' then (isnull((select SUM(Finance) from
PriorToCapDistributionItem e inner join PriorToCapDistribution f on
e.MainID=f.MainID
inner join JHOA_Approve c
on f.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
where f.Department='总部'
[@ and f.[Year]=:year @] [@ and f.[Month]=:month @]
group by f.Department
 ),0)*0.36)+SUM(isnull(Finance,0))
when Department='撬装' then (isnull((select SUM(Finance) from
PriorToCapDistributionItem e inner join PriorToCapDistribution f on
e.MainID=f.MainID
inner join JHOA_Approve c
on f.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
where f.Department='总部'
[@ and f.[Year]=:year @] [@ and f.[Month]=:month @]
group by f.Department
 ),0)*0.28)+SUM(isnull(Finance,0))
when Department='重工' then (isnull((select SUM(Finance) from
PriorToCapDistributionItem e inner join PriorToCapDistribution f on
e.MainID=f.MainID
inner join JHOA_Approve c
on f.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
where f.Department='总部'
[@ and f.[Year]=:year @] [@ and f.[Month]=:month @]
group by f.Department
 ),0)*0.19)+SUM(isnull(Finance,0))
when Department='韩中' then SUM(isnull(Finance,0))
when Department='工程事业部' then SUM(isnull(Finance,0))
else 0
end  as Finance,
remark

from PriorToCapDistributionItem a
inner join PriorToCapDistribution b on
a.MainID=b.MainID
inner join JHOA_Approve c
on b.MainID=c.AppO_Values and c.App_Over=1  and
c.App_IdeaFlag=9 and c.Del_flag=0
where department !='瓶撬'
[@ and b.[Year]=:year @] [@ and b.[Month]=:month @]
group by b.Department,a.remark