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.