Sql-server – When duplicate date values exist in a column, change the value of the oldest

sql servert-sql

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

Best Answer

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       |
Related Question