CODEWITHSUNDEEP
sqlsql-server
Abdio68
Abdio68

Reputation: 59

How to add an column to the same table making the 2 columns in the table unique (query)

I have an table with the following:

Name NamePartner DateStartCustomer DateCustomerEnd
123 356 01-01-1990 NULL
356 123 01-01-1990 NULL
567 NULL 01-01-2020 NULL

What I would like to want is an query to add an extra column in this table were I can say that the if Name in the first column is also in the column of NamePartner and the DateCustomerEnd is NULL --> so ongoing relation, then those rows are 1 household. So row 1 and row 2 should be 1 household. I want to give the value of the first columns to that new column

Example:

Name NamePartner UniqueHousehold DateStartCustomer DateCustomerEnd
123 356 123 01-01-1990 NULL
356 123 123 01-01-1990 NULL
567 NULL 567 01-01-2020 NULL

UPDATE:

Example

@Chris Albert After closely looking at the logic Im seeing duplicates for several rows. This has to do with the ID which is generated of your logic. See picture I added. These are the duplicates im getting. Is there a way to not get duplicates here?

Upvotes: 0

Views: 421

Answers (1)

Chris Albert
Chris Albert

Reputation: 2507

The query below will give you the desired result set.

To accomplish this I used ROW_NUMBER to generate a unique ID for each row.
This is needed to establish a unique household. Based on your example it appears you want whatever record is first to be the household value. Ideally your source table would have an ID column or primary key to use for this.

DROP TABLE IF EXISTS #Customer;
CREATE TABLE #Customer
(
    Name INT
    , NamePartner INT
    , DateStartCustomer DATETIME
    , DateCustomerEnd DATETIME
);

INSERT INTO #Customer
VALUES
    (123, 356, '01/01/1990', NULL)
    , (356, 123, '01/01/1990', NULL)
    , (567, NULL, '01/01/2020', NULL);

WITH RowNum AS
(
    SELECT *, ROW_NUMBER() OVER(ORDER BY Name) AS Id
    FROM #Customer
)

SELECT
    C.Name
    , C.NamePartner
    , COALESCE(HH.Name, C.Name) AS UniqueHousehold
    , C.DateStartCustomer
    , C.DateCustomerEnd
FROM
    RowNum           AS C
    LEFT JOIN RowNum AS HH
        ON C.NamePartner = HH.Name
           AND  C.Id > HH.Id;

DROP TABLE IF EXISTS #Customer;

Upvotes: 1

Related Questions