XYZ | |||
ID | CustomerID [Reference to Cus.] | RelatedCustomerID [Reference to Cus.] | Relation Ship of Customers |
1 | 20021 | 20022 | is children of |
2 | 20022 | 20021 | is father of |
3 | 20023 | 20024 | is friend of |
4 | 20024 | 20023 | is brother of |
5 | 20025 | 20026 | is children of |
Do not forget to provide your inputs in comment section
Now the case is earlier the application is designed in way to hold the relationship between 2 customers in either way. Now as per new requirement this relationship are duplicate. We need to keep only 1 relation ship from cross reference rows.
example -
1. Row 1 and Row 2 are duplicate, we need to keep only 1 row (any one)
2. Row 3 and Row 4 are duplicate, we need to keep only 1 row (any one)
3. Row 5 is itself is unique
So did workaround with friends, of-course nothing is found on internet.google. Tried Differnt type of join , inner join , self join, min , max, dense rank, pivot but nothing works
Below is the query we prepared after all.
SELECT
distinct
CASE
WHEN CustomerID < RelatedCustomerID THEN CustomerID
ELSE RelatedCustomerID
END AS CustomerID_v,
CASE
WHEN CustomerID > RelatedCustomerID THEN CustomerID
ELSE RelatedCustomerID
END AS RelatedCustomerID_V
FROM
XYZ
This could be a good interview question as well
No comments:
Post a Comment