CODEWITHSUNDEEP
sql-servert-sqlmerge
jmc
jmc

Reputation: 1729

Merge columns and add values based on duplicate column

I have rows in my SQL Server that I would like to merge based on duplicate StartDate column. By merging, I would also like to

ID     CustomerID     Amount     PurchaseDate     TimeStamp
1      113            20         2015-10-01       0x0000000000029817
2      113            30         2015-10-01       0x0000000000029818

Based on the example above, I would like to have a single column where the values for the Amount column are summed up.

ID     CustomerID     Amount     PurchaseDate     TimeStamp
2      113            50         2015-10-01       0x0000000000029818

I'm not certain how I should go about this whether I should:

  1. Create a new row with the new values or;
  2. Update the latest added row and add the Amount to that row

But first I'd like to know how to get rows with duplicate StartDate column values

UPDATE: I have here a delete script for old values

DELETE FROM Table WHERE ID NOT IN (SELECT MAX(ID) FROM Table GROUP BY CustomerID, PurchaseDate)

Upvotes: 0

Views: 587

Answers (1)

Simone
Simone

Reputation: 1924

I suggest updating the last inserted;

UPDATE T
SET Amount = X.Amount
FROM Table T INNER JOIN (
    SELECT MAX(ID), SUM(Amount)
    FROM Table
    GROUP BY CustomerID, PurchaseDate) X ON T.ID = X.ID)

In this case I'd suggest also to remove the old values

Upvotes: 1

Related Questions