🚀 go-pugleaf

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




ALTER  proc JGE_Budget
@theYear int,
@theMonth int
as

declare 
--@departmentnumber   int,
        @Budget         decimal(18,2),
        @SalaryBonus    decimal(18,2),
        @NonAPPayment   decimal(18,2),
        @FixExpense     decimal(18,2),
        @StaffClaim     decimal(18,2),
        @Opex           decimal(18,2)

select  @Budget=0.0,
        @SalaryBonus=0.0,
        @NonAPPayment=0.0,
        @FixExpense=0.0,
        @StaffClaim=0.0,
        @Opex=0.0


create table  #Budget_Opex(
-- departmentnumber    int,
 budget          decimal(18,2),
 SalaryBonus     decimal(18,2),
 NonAPPayment    decimal(18,2),
 FixExpense      decimal(18,2),
 StaffClaim      decimal(18,2),
 Opex            decimal(18,2)
)
--Substract-----------------
--Department-----------
--select @departmentnumber=department.departmentno
--from department
--order by department.departmentno asc

--Budget----------------
select @budget=isnull(sum(budgetingamount),0.0) 
from department left join budgeting
on (department.departmentno = budgeting.departmentno and budgetingyear = 
@theYear and budgetingmonth = @theMonth)
group by department.departmentno
order by department.departmentno asc

--SalaryBonus----------------
select @SalaryBonus=isnull(sum(salaryamount+bonusamount),0.0)
from department left join staff
on department.departmentno = staff.departmentno left join salarybonusitem
on staff.staffno = salarybonusitem.staffno and datepart(mm, 
salarybonusitem.paymentday) = @theMonth and datepart(yy, 
salarybonusitem.paymentday) = @theYear
group by department.departmentno
order by department.departmentno asc

--NonAPPayment-----
select @NonAPPayment=isnull(sum(apamount),0.0)
from department left join staff
on department.departmentno = staff.departmentno left join apitem
on staff.staffno = apitem.staffno and apitem.typeno = '1' and datepart(mm, 
apitem.appaymentdate) = @theMonth and datepart(yy, apitem.appaymentdate) = 
@theYear
group by department.departmentno
order by department.departmentno asc

--StaffClaim------
select @StaffClaim=isnull(sum(claimamount),0.0)
from department left join staff
on department.departmentno = staff.departmentno left join staffclaimitem
on department.departmentno = staffclaimitem.departmentno and datepart(mm, 
staffclaimitem.paymentday) = @theMonth and datepart(yy, 
staffclaimitem.paymentday) = @theYear
group by department.departmentno
order by department.departmentno asc

--FixExpense--------
select @FixExpense=isnull(expenseamount,0.0)
from department left join staff
on department.departmentno = staff.departmentno left join fixexpenseitem
on department.departmentno = fixexpenseitem.departmentno and datepart(mm, 
fixexpenseitem.paymentday) = @theMonth and datepart(yy, 
fixexpenseitem.paymentday) = @theYear
group by department.departmentno, expenseamount
order by department.departmentno asc


select @Opex= (@Salarybonus+@NonAPPayment+@StaffClaim+@FixExpense)
insert into  #Budget_Opex(Budget, Opex)
             values(@Budget, @Opex)
select Budget, Opex
from #Budget_Opex

--select @departmentnumber
--insert into #budget_opex(departmentnumber)
--values(@departmentnumber)
--select departmentnumber, budget, opex
--from #budget_opex

drop table #Budget_Opex




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

�ҵ��뷨��Ӧ�÷������е�departmentnumber�����Ӧ��budget&opex
лл
 
--
�� ��Դ:����ˮ˼Դ bbs.sjtu.edu.cn��[FROM: 211.80.42.117]