How to delete if the data for the days following each other are the same – T-SQL

I have a table like this;

OwnerName Date Value1 Value2 Value3
Jack 2020-01-01 5.5 500 22.76
Jack 2020-01-02 4.7 650 33.75
Jack 2020-01-03 4.7 650 33.75
Jack 2020-01-04 4.7 650 33.75
Jack 2020-01-05 5.1 780 35.50
Jack 2020-01-06 6.8 340 20.75
Jack 2020-01-07 9.2 450 15.50
Jack 2020-01-08 9.2 450 15.50
Jack 2020-01-09 9.2 450 15.50
Jack 2020-01-10 9.2 450 15.50

If Value 1, Value 2, and Value 3 are the same for consecutive dates, how can I delete dates other than the earliest date from those dates?

According to this sample table, after the delete query, the table should be as follows;

OwnerName Date Value1 Value2 Value3
Jack 2020-01-01 5.5 500 22.76
Jack 2020-01-02 4.7 650 33.75
Jack 2020-01-05 5.1 780 35.50
Jack 2020-01-06 6.8 340 20.75
Jack 2020-01-07 9.2 450 15.50

Table Script :

CREATE TABLE OwnerRateInfo(
   OwnerName NVARCHAR(20) NOT NULL
  ,Date      DATE  NOT NULL
  ,Value1    DECIMAL(20,1) NOT NULL
  ,Value2    DECIMAL(20,0) NOT NULL
  ,Value3    DECIMAL(20,2) NOT NULL
  ,PRIMARY KEY(OwnerName,Date)
);
INSERT INTO OwnerRateInfo(OwnerName,Date,Value1,Value2,Value3) VALUES (N'Jack','2020-01-01',5.5,500,22.76);
INSERT INTO OwnerRateInfo(OwnerName,Date,Value1,Value2,Value3) VALUES (N'Jack','2020-01-02',4.7,650,33.75);
INSERT INTO OwnerRateInfo(OwnerName,Date,Value1,Value2,Value3) VALUES (N'Jack','2020-01-03',4.7,650,33.75);
INSERT INTO OwnerRateInfo(OwnerName,Date,Value1,Value2,Value3) VALUES (N'Jack','2020-01-04',4.7,650,33.75);
INSERT INTO OwnerRateInfo(OwnerName,Date,Value1,Value2,Value3) VALUES (N'Jack','2020-01-05',5.1,780,35.50);
INSERT INTO OwnerRateInfo(OwnerName,Date,Value1,Value2,Value3) VALUES (N'Jack','2020-01-06',6.8,340,20.75);
INSERT INTO OwnerRateInfo(OwnerName,Date,Value1,Value2,Value3) VALUES (N'Jack','2020-01-07',9.2,450,15.50);
INSERT INTO OwnerRateInfo(OwnerName,Date,Value1,Value2,Value3) VALUES (N'Jack','2020-01-08',9.2,450,15.50);
INSERT INTO OwnerRateInfo(OwnerName,Date,Value1,Value2,Value3) VALUES (N'Jack','2020-01-09',9.2,450,15.50);
INSERT INTO OwnerRateInfo(OwnerName,Date,Value1,Value2,Value3) VALUES (N'Jack','2020-01-10',9.2,450,15.50);

Thanks in advance



Read more here: https://stackoverflow.com/questions/65709214/how-to-delete-if-the-data-for-the-days-following-each-other-are-the-same-t-sql

Content Attribution

This content was originally published by Cenk Fidan at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: