I have the following query which counts up values for all organisation for a specified year. The problem is the BudgetProfile = 0. Some of the organisations values have changed but the old values are still in the table. So an organisation could have 2 records for one year one as BudgetProfile =0 and One as budget profile = 1. I have an other table which states profile the organisation is on. Basically I want to run this query but only count the records on each organisation with their current profile.
SELECT
sum(Salaries) as TotalBudget_Salaries,
sum(NatIns) as TotalBudget_NatIns,
sum(Pension) as TotalBudget_Pension,
sum(StaffTravel) as TotalBudget_StaffTravel,
sum(StaffTraining) as TotalBudget_StaffTraining,
sum(Recruitment) as TotalBudget_Recruitment,
sum(TotalStaff) as TotalBudget_TotalStaff,
sum(PropertyCosts) as TotalBudget_PropertyCosts,
sum(Heat) as TotalBudget_Heat,
sum(Maintenance) as TotalBudget_Maintenance,
sum(Cleaning) as TotalBudget_Cleaning,
sum(Postage) as TotalBudget_Postage,
sum(Tel) as TotalBudget_Tel,
sum(Insurance) as TotalBudget_Insurance,
sum(TotalRunning) as TotalBudget_TotalRunning,
sum(Travel) as TotalBudget_Travel,
sum(Subsistence) as TotalBudget_Subsistence,
sum(Training) as TotalBudget_Training,
sum(ChildCare) as TotalBudget_ChildCare,
sum(OtherRunning) as TotalBudget_OtherRunning,
sum(TotalVolunteer) as TotalBudget_TotalVolunteer
,
sum(ManagementCharge) as TotalBudget_ManagementChar
ge,
sum(Audit) as TotalBudget_Audit,
sum(OnceOffCapital) as TotalBudget_OnceOffCapital
,
sum(Other1) as TotalBudget_Other1,
sum(Other2) as TotalBudget_Other2,
sum(TotalOther) as TotalBudget_TotalOther,
sum(TotalStaff + TotalRunning + TotalVolunteer + TotalOther) as TotalBudget_OverallBudget
FROM dbo.TBL_Expenditure_CVS
WHERE
Year = 1
AND Target= 1
AND BudgetProfile = 0
Start Free Trial
