select
sum(Usage1) as CubicFeetUsed
--a.ID, a.Date as ReadDate, a.tblMeterID, b.MeterID, c.ServiceAddress, Usage1, a.Reading1, a.tblActionCodeID, 
--f.ActionCode, e.CustomerType, d.FinalBillDate, d.Name, d.IsLandlord, g.Abbreviation1
from [UM0_MolallaOR].[dbo].[tblMeterActivity] a
inner join [UM0_MolallaOR].[dbo].[tblMeter] b on a.tblMeterID = b.ID
inner join [UM0_MolallaOR].[dbo].[tblLocation] c on a.tblLocationID = c.ID
inner join [UM0_MolallaOR].[dbo].[tblCustomer] d on c.ID = d.tblLocationID
inner join [UM0_MolallaOR].[dbo].[tblCustomerType] e on d.tblCustomerTypeID = e.ID
inner join [UM0_MolallaOR].[dbo].[tblActionCode] f on a.tblActionCodeID = f.ID
inner join [UM0_MolallaOR].[dbo].[tblService] g on a.tblServiceID = g.ID
--inner join [UM0_MolallaOR].[dbo].[tblRate] g on a.tblServiceID
where usage1 > 0 and --only positive usage
(
	(d.FinalBillDate is null)
	or 
	(d.FinalBillDate >= '2020-01-01' and d.FinalBillDate<='2020-01-31')
) and
d.IsLandlord = 0 and
Date>= '2020-01-01' and Date<='2020-12-31' --baseline year 
--Date>= '2020-01-01' and Date<='2020-01-31' 
--Date>= '2020-02-01' and Date<='2020-02-29'--select month
--Date>= '2020-03-01' and Date<='2020-03-31'--select month
--Date>= '2020-04-01' and Date<='2020-04-30'--select month
--Date>= '2020-05-01' and Date<='2020-05-31'--select month
--Date>= '2020-06-01' and Date<='2020-06-30'--select month
--Date>= '2020-07-01' and Date<='2020-07-31'--select month
--Date>= '2020-08-01' and Date<='2020-08-31'--select month
--Date>= '2020-09-01' and Date<='2020-09-30'--select month
--Date>= '2020-10-01' and Date<='2020-10-31'--select month
--Date>= '2020-11-01' and Date<='2020-11-30'--select month
--Date>= '2020-12-01' and Date<='2020-12-31'--select month
--and (Size='.75') --uncomment to find Residential Use Only
and Usage1<200000 --anything more than 200,000 cubic feet in a month shall be considered a leak, or erroneous data. 
--and (Usage1>200000 and Usage1<90000000) --uncomment to massive metered losses, but exclude possible erroneous data.
--order by Usage1 desc MeterID, Date --comment out if getting sums
--and MeterID='364122831' --to troubleshoot duplicates
--Group by e.CustomerType
--order by CustomerType desc

Notes: I tried to use these new queries to verify Heather’s old numbers (to double check that their output  matches the old information you used for the original rate study, but I can’t do that because the Meter Readings no longer exist in the Caselle Database – they were wiped a few years back when a Records Management Temp came on board and did some things). Also, there is some garbage records in the database that make the Caselle Crystal Reports unreliable, so until that is cleaned up I can’t verify using Caselle’s native reports. If there are errors in the numbers I’m supplying now they are on the LOW side for usage.


UPDATE: FOLLOWING UP, 

--this query gives me 1,090,692, same as that which appears on the report. That gives me a lot of confidence in the numbers I submitted to Donovan!
select Sum(Usage1)
from [UM0_MolallaOR].[dbo].[tblMeterActivity] a
inner join [UM0_MolallaOR].[dbo].[tblCustomer] b on b.ID = a.tblCustomerID
inner join [UM0_MolallaOR].[dbo].[tblCustomerType] c on b.tblCustomerTypeID = c.ID
inner join [UM0_MolallaOR].[dbo].[tblMeter] d on a.tblMeterID = d.ID
where
a.Date>= '2020-01-01' and a.Date<='2020-12-31'
and c.CustomerType='Institutional/Government'

--this one cuts off rediculously high garbage data usage
select sum(Usage1)
from [UM0_MolallaOR].[dbo].[tblMeterActivity] a
inner join [UM0_MolallaOR].[dbo].[tblCustomer] b on b.ID = a.tblCustomerID
inner join [UM0_MolallaOR].[dbo].[tblCustomerType] c on b.tblCustomerTypeID = c.ID
inner join [UM0_MolallaOR].[dbo].[tblMeter] d on a.tblMeterID = d.ID
inner join [UM0_MolallaOR].[dbo].[tblLocation] e on d.tblLocationID = e.ID
where
a.Date>= '2020-01-01' and a.Date<='2020-12-31'
and c.CustomerType='Institutional/Government'
and Usage1<300000 --cutting off rediculously high usages (usually garbage data). It looks like there was some kind of leak at the Middle School in October that year.