0

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.

enter image description here

  • Why only the last row change? What happen to other rows? – Eric Sep 26 '18 at 23:48
  • what happens if you have 3+ rows with the same Year/Month ... only the 'oldest' is changed? what if you have 2 rows with the identical 'oldest' date? what happens in your example if row #13 already has 2017/Feb? – markp Sep 27 '18 at 0:25
  • Great questions. The last row changes because it is the oldest record with duplicate Month Year values in the Date column. – lorigar Sep 27 '18 at 0:30
  • @markp - If somehow 3+ rows have the same Year/Month, I'd have invalid data. I've updated the post with some more info to hopefully clarify. Thanks for the help. – lorigar Sep 27 '18 at 0:38
  • What sql server and version are you you using? – danblack Sep 27 '18 at 0:51
1

I think this solves your problem.

--demo setup
drop table if exists billing;
go
CREATE TABLE Billing
    ([BillDt] datetime, [BillMonth] varchar(3))
;

INSERT INTO Billing
    ([BillDt], [BillMonth])
VALUES
    ('2018-01-30', 'Jan'),
    ('2017-12-28', 'Dec'),
    ('2017-11-30', 'Nov'),
    ('2017-10-31', 'Oct'),
    ('2017-09-28', 'Sep'),
    ('2017-08-31', 'Aug'),
    ('2017-07-31', 'Jul'),
    ('2017-06-29', 'Jun'),
    ('2017-05-31', 'May'),
    ('2017-04-27', 'Apr'),
    ('2017-03-30', 'Mar'),
    ('2017-03-01', 'Mar')
;

--the solution
--With SQL Server looking at each row on the Billing table...
--if you can find a row where the month is the same as the 'current' row, but
--the day is greater than the 'current' row, update the BillMonth of the 'current'
--row to be the previous month of the 'current' row.
UPDATE b
SET BillMonth = Substring(datename(month, DATEADD(month, - 1, billdt)), 1, 3)
FROM Billing b
WHERE EXISTS (
        SELECT *
        FROM Billing
        WHERE datepart(month, BillDt) = datepart(month, b.BillDt)
            AND datepart(day, BillDt) > datepart(day, b.BillDt)
        )

SELECT *
FROM billing
ORDER BY billdt desc

 BillDt                  | BillMonth |
|-------------------------|-----------|
| 2018-01-30 00:00:00.000 | Jan       |
| 2017-12-28 00:00:00.000 | Dec       |
| 2017-11-30 00:00:00.000 | Nov       |
| 2017-10-31 00:00:00.000 | Oct       |
| 2017-09-28 00:00:00.000 | Sep       |
| 2017-08-31 00:00:00.000 | Aug       |
| 2017-07-31 00:00:00.000 | Jul       |
| 2017-06-29 00:00:00.000 | Jun       |
| 2017-05-31 00:00:00.000 | May       |
| 2017-04-27 00:00:00.000 | Apr       |
| 2017-03-30 00:00:00.000 | Mar       |
| 2017-03-01 00:00:00.000 | Feb       |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.