I have 3 tables here:
Property table.PropertyAudit - capture one or more changes made in "Property" table.PStatus - which is a lookup table for StautsID column.Here is what I have so far.
This is the tables script and sample data:
CREATE TABLE Property
(
PropertyID INTEGER NOT NULL PRIMARY KEY
,StatusID INTEGER NOT NULL
,Country VARCHAR(11) NOT NULL
,Town VARCHAR(10) NOT NULL
,LastChangedBy VARCHAR(20) NOT NULL
,LastChanged VARCHAR(23) NOT NULL
);
INSERT INTO Property (
PropertyID
,StatusID
,Country
,Town
,LastChangedBy
,LastChanged
)
VALUES (
73555
,1
,'NoMansLand'
,'BEEREN'
,'Agent009'
,'2020-10-15 12:41:14.280'
);
INSERT INTO Property (
PropertyID
,StatusID
,Country
,Town
,LastChangedBy
,LastChanged
)
VALUES (
72896
,6
,'Neverlands'
,'RIDDERK'
,'Agent007'
,'2020-08-10 08:41:22.447'
);
CREATE TABLE PropertyAudit
(
AID INTEGER NOT NULL PRIMARY KEY
,PropertyID INTEGER NOT NULL
,StatusID INTEGER NOT NULL
,LastChangedBy VARCHAR(20) NOT NULL
,LastChanged VARCHAR(23) NOT NULL
)
INSERT INTO PropertyAudit (
AID
,PropertyID
,StatusID
,LastChangedBy
,LastChanged
)
VALUES (
12340
,73555
,7
,'Agent009'
,'2020-10-15 12:41:14.280'
);
INSERT INTO PropertyAudit (
AID
,PropertyID
,StatusID
,LastChangedBy
,LastChanged
)
VALUES (
12341
,73555
,6
,'Agent007'
,'2020-08-24 08:10:53.223'
);
INSERT INTO PropertyAudit (
AID
,PropertyID
,StatusID
,LastChangedBy
,LastChanged
)
VALUES (
12342
,73555
,5
,'Agent009'
,'2020-02-13 14:38:10.913'
);
INSERT INTO PropertyAudit (
AID
,PropertyID
,StatusID
,LastChangedBy
,LastChanged
)
VALUES (
12343
,73555
,3
,'Agent009'
,'2020-02-13 14:33:25.967'
);
INSERT INTO PropertyAudit (
AID
,PropertyID
,StatusID
,LastChangedBy
,LastChanged
)
VALUES (
12344
,73555
,2
,'Agent009'
,'2020-02-10 13:37:57.527'
);
CREATE TABLE PStatus
(
ID INTEGER NOT NULL PRIMARY KEY
,StatusName VARCHAR(20) NOT NULL
,LastChangedBy VARCHAR(20) NOT NULL
,LastChanged VARCHAR(23) NOT NULL
);
INSERT INTO PStatus (
ID
,StatusName
,LastChangedBy
,LastChanged
)
VALUES (
1
,'REJECTED'
,'dbo'
,'2013-05-28 17:02:42.977'
);
INSERT INTO PStatus (
ID
,StatusName
,LastChangedBy
,LastChanged
)
VALUES (
2
,'NEW Contract'
,'dbo'
,'2013-05-28 17:02:42.977'
);
INSERT INTO PStatus (
ID
,StatusName
,LastChangedBy
,LastChanged
)
VALUES (
3
,'ACTIVE Contract'
,'dbo'
,'2013-10-15 12:41:14.280'
);
INSERT INTO PStatus (
ID
,StatusName
,LastChangedBy
,LastChanged
)
VALUES (
4
,'MONITOR'
,'dbo'
,'2013-10-15 12:41:14.280'
);
INSERT INTO PStatus (
ID
,StatusName
,LastChangedBy
,LastChanged
)
VALUES (
5
,'DEAL'
,'dbo'
,'2013-10-15 12:41:14.280'
);
INSERT INTO PStatus (
ID
,StatusName
,LastChangedBy
,LastChanged
)
VALUES (
6
,'DEALT'
,'dbo'
,'2013-10-15 12:41:14.280'
);
INSERT INTO PStatus (
ID
,StatusName
,LastChangedBy
,LastChanged
)
VALUES (
7
,'COMPLETED'
,'dbo'
,'2013-10-15 12:41:14.280'
);
INSERT INTO PStatus (
ID
,StatusName
,LastChangedBy
,LastChanged
)
VALUES (
8
,'ABORTED'
,'dbo'
,'2013-10-15 12:41:14.280'
);
This is what I'm trying to achieve.
Final output:
+---------+---------------+----------------+-----------+-------------------------+
| PRD Ref | Latest_Status | Opening_Status | ChangedBy | ChangedOn |
+---------+---------------+----------------+-----------+-------------------------+
| 73555 | COMPLETED | NEW CONTRACT | Agent009 | 2020-10-15 12:41:14.280 |
+---------+---------------+----------------+-----------+-------------------------+
Here is what I have tried.
How do I introduce 2 columns "Lastest_Status" and "Opening_Status" and copy the values from the Status column.
SELECT prop.[PropertyID] AS "PRD Ref"
,(
SELECT [StatusName]
FROM [PStatus]
WHERE pa.StatusID = [ID]
) AS "Status"
,pa.[LastChangedBy] AS "ChangedBy"
,pa.[LastChanged] AS "ChangedOn"
FROM [PropertyAudit] pa
INNER JOIN [Property] prop ON pa.PropertyID = prop.PropertyID
WHERE pa.PropertyID = 73555
ORDER BY pa.PropertyID DESC
+---------+-----------------+-----------+-------------------------+
| PRD Ref | Status | ChangedBy | ChangedOn |
+---------+-----------------+-----------+-------------------------+
| 73555 | COMPLETED | Agent009 | 2020-10-15 12:41:14.280 |
| 73555 | DEALT | Agent007 | 2020-08-24 08:10:53.223 |
| 73555 | DEAL | Agent009 | 2020-02-13 14:38:10.913 |
| 73555 | ACTIVE Contract | Agent009 | 2020-02-13 14:33:25.967 |
| 73555 | NEW Contract | Agent009 | 2020-02-10 13:37:57.527 |
+---------+-----------------+-----------+-------------------------+
Many thanks in advance.
Here is one approach using window functions and conditional aggregation:
select p.propertyid,
max(case when pa.rn_desc = 1 then s.statusname end) last_status,
max(case when pa.rn_asc = 1 then s.statusname end) opening_status,
p.lastchangedby,
p.lastchanged
from property p
inner join (
select pa.*,
row_number() over(partition by propertyid order by lastchanged) rn_asc,
row_number() over(partition by propertyid order by lastchanged desc) rn_desc
from propertyaudit pa
) pa on pa.propertyid = p.propertyid
inner join pstatus s on s.id = pa.statusid
where 1 in (rn_asc, rn_desc)
group by p.propertyid, p.lastchangedby, p.lastchanged
Note that you don't really need table property to get the result that you want. We can dig further into the audit table, like so:
select pa.propertyid,
max(case when pa.rn_desc = 1 then s.statusname end) last_status,
max(case when pa.rn_asc = 1 then s.statusname end) opening_status,
max(case when pa.rn_desc = 1 then pa.lastchangedby end) lastchangedby,
max(case when pa.rn_desc = 1 then pa.lastchanged end) lastchanged
from (
select pa.*,
row_number() over(partition by propertyid order by lastchanged) rn_asc,
row_number() over(partition by propertyid order by lastchanged desc) rn_desc
from propertyaudit pa
) pa
inner join pstatus s on s.id = pa.statusid
where 1 in (rn_asc, rn_desc)
group by pa.propertyid
Demo on DB Fiddle - both queries yield:
propertyid | last_status | opening_status | lastchangedby | lastchanged
---------: | :---------- | :------------- | :------------ | :----------------------
73555 | COMPLETED | NEW Contract | Agent009 | 2020-10-15 12:41:14.280