Select rows with same id but different *multiple* values in another columns

Question

I tried for some time now, read many posts but I still can't figure out how to handle this request:

How to create an output where the ID, date and all mutated data is shown (that is, including the ID, date and other data just before a change occurs, and the ID, date and other data of the change), whenever there's a change in one of the other columns.

For a one column change, this isn't too hard, using something like:

SELECT *
FROM YourTable
WHERE ARIDNR IN (
SELECT ARIDNR
FROM YourTable
GROUP BY ARIDNR
HAVING COUNT(*) > 1
)

But for changes in multiple columns...?

I have a table like this:

create table A (Reference_date date, Loan_id int, address NVARCHAR (100), house_nr int, status_loan int)
Insert into A values ('2020-01-31',1000,'Boulevard', 5, 10)
Insert into A values ('2020-02-28',1000,'Boulevard', 5, 10)
Insert into A values ('2020-03-31',1000,'Boulevard', 5, 10)
Insert into A values ('2020-04-30',1000,'Boulevard', 5, 10)
Insert into A values ('2020-05-31',1000,'Boulevard', 5, 10)
Insert into A values ('2020-06-30',1000,'Boulevard', 5, 10)
Insert into A values ('2020-07-31',1000,'Hill', 5, 10)
Insert into A values ('2020-08-31',1000,'Hill', 5, 10)
Insert into A values ('2020-09-30',1000,'Hill', 5, 10)
Insert into A values ('2020-10-31',1000,'Hill', 5, 10)

Insert into A values ('2020-01-31',1001,'Elm', 8, 10)
Insert into A values ('2020-02-28',1001,'Elm', 8, 10)
Insert into A values ('2020-03-31',1001,'View', 10, 10)
Insert into A values ('2020-04-30',1001,'View', 10, 10)
Insert into A values ('2020-05-31',1001,'View', 10, 10)
Insert into A values ('2020-06-30',1001,'View', 10, 10)
Insert into A values ('2020-07-31',1001,'Lake', 5, 10)
Insert into A values ('2020-08-31',1001,'Lake', 5, 10)
Insert into A values ('2020-09-30',1001,'Lake', 5, 10)
Insert into A values ('2020-10-31',1001,'Lake', 5, 10)

Insert into A values ('2020-01-31',1002,'Main', 1, 10)
Insert into A values ('2020-02-28',1002,'Main', 1, 10)
Insert into A values ('2020-03-31',1002,'Main', 1, 10)
Insert into A values ('2020-04-30',1002,'Main', 1, 5)
Insert into A values ('2020-05-31',1002,'Main', 1, 5)
Insert into A values ('2020-06-30',1002,'Main', 1, 6)
Insert into A values ('2020-07-31',1002,'Main', 5, 6)
Insert into A values ('2020-08-31',1002,'Main', 5, 6)
Insert into A values ('2020-09-30',1002,'Main', 5, 6)
Insert into A values ('2020-10-31',1002,'Main', 5, 6)

And I'm looking for a sub query in order to create the following outcome:

Reference_date, Loan_id, address, house_nr, status_loan
2020-06-30, 1000, Boulevard, 5, 10
2020-07-31, 1000, Hill, 5, 10

2020-02-28, 1001, Elm, 8, 10
2020-03-31, 1001, View, 10, 10
2020-06-30, 1001, View, 10, 10
2020-07-31, 1001, Lake, 5, 10

2020-03-31, 1002, Main, 1, 10
2020-04-30, 1002, Main, 1, 5
2020-05-31, 1002, Main, 1, 5
2020-06-30, 1002, Main, 1, 6
2020-06-30, 1002, Main, 1, 6
2020-07-31, 1002, Main, 5, 6

Does anyone know how to accomplish this?

Answer 1

Check a solution which is probably not the shortest one:

;
with E1 as
(
    select *, lead(Reference_date) over (partition by Loan_id order by Reference_date) nd
    from A
), E2 as
(
    select Loan_id, d
    from (
        select c.*
        from E1 c
        inner join A n on n.Loan_id = c.Loan_id and n.Reference_date = c.nd
        where exists (select c.address, c.house_nr, c.status_loan except select n.address, n.house_nr, n.status_loan)
    ) t
    unpivot 
    (
        d for col in (Reference_Date, nd)
    ) u
)
select A.* 
from E2
inner join A on A.Loan_id = E2.Loan_id and A.Reference_date = E2.d
order by Loan_id, Reference_date

Answer 2

Glad your problem is resolved, if you have any problems, please contact us.

Regards
Echo

Answer 3

Seems that I did not read the requirements fully. Here is a revised query:

; WITH CTE AS (
   SELECT *, concat_ws(char(17), address, house_nr, status_loan) AS othercols
   FROM   A
), taking_lead AS (
   SELECT *, LAG(othercols) OVER(PARTITION BY Loan_id ORDER BY Reference_date) AS prev,
            LEAD(othercols) OVER(PARTITION BY Loan_id ORDER BY Reference_date) AS next
   FROM   CTE
)
SELECT Reference_date, Loan_id, address, house_nr, status_loan
FROM   taking_lead
WHERE  next <> othercols 
UNION ALL
SELECT Reference_date, Loan_id, address, house_nr, status_loan
FROM   taking_lead
WHERE  prev <> othercols 
ORDER  BY Loan_id, Reference_date