I have a query that returns records with duplicate Month and Year in a date field. I need to identify the records with duplicate Month and Year, then identify the oldest based upon the full date and change the value of a column that displays the month based upon the date column to that month - 1.
Background: The query grabs the last 12 bills for a customer. In a few instances, the Date (actually the read date - which is typically the last day of the month) reflects when a meter was read. The only time there could be read dates with the same month year in that 12 month period is when a read date (the last day of the month) falls on a non business day, then it pushes to the next month. I'm looking for the usage in the month the meter was read for the last 12 months.
Example, in the query results below the highlighted month should read Feb as it is the oldest of the duplicate Mar 2017 results.