Exclude from SUM if revised record exists

Transact-SQL
1

Hi all,

I have a table of data that includes monthly oil production totals. Sometimes a total for a given month will be revised many months later. For example, I may submit a report in March of 2010 stating my total for January of 2010 was 150 barrels from the NB-CD formation. Then, in August of 2011, I may revise my March calculations and say that there were actually 250 barrels produced from NB-CD in January of 2010. When this happens, I include a "Y" in the "revised" column of the record containing the new number, indicating that the new record should replace the old record. I have included a snippet of the table here for reference: http://databin.pudo.org/t/5c2b3f

API Sequence No = well number
Revised = the revised (newer) record
Accepted date = date the report showing the production totals was accepted
Formation = formation from which oil was produced

How would I query totals for a given month/year? It is necessary to exclude any records that were subsequently revised. For example, the total for March of 2012 would be 884 (865 from NB-CD and 19 from JSND). Total for May would be 656 (628 from NB-CD and 28 from JSND).

2

Anyhow after formation you are updating the actual barrels. Don't exclude anything. just sum for the month and year.

3

I would have to exclude the old record. Otherwise it would be included in the total.

4 
SELECT
	SUM([Oil Vol]),
	ReportYear,
	ReportMonth
FROM
	YourTable y1
WHERE
	NOT EXISTS
	(
		SELECT * FROM YourTable y2
		WHERE y2.ReportYear = y1.ReportYear
			AND y2.ReportMonth = y1.ReportMonth
			AND y2.[COGCC Formation Code] = y1.[COGCC Formation Code]
			AND y2.Revised = 'Y'
			AND y1.Revised <> 'Y'
	)
GROUP BY
	ReportYear,
	ReportMonth;
5

Just so I understand how this works, what are y1 and y2?

6

y1 and y2 are aliases. The full syntax includes the optional "AS" keyword, which perhaps would make it a bit more clearer.

.....
YourTable  AS y1
.....

You are telling SQL Server that "See this here table called YourTable? I am going to refer to it as y1 everywhere else in this query"

An alias is required in this query because the same table YourTable is used in the outer query and the inner query, and we want to refer to columns from both those references without causing ambiguity.

7

Great - thanks!