Member Table:
| ID | Date | DateCreated |
|---|---|---|
| 303896 | 2039-11-29 | 2017-11-03 21:47:51.000 |
| 303863 | 2044-03-02 | 2017-11-03 21:44:33.000 |
| 303843 | 2061-07-30 | 2017-11-03 21:43:39.000 |
| 303804 | 2025-10-08 | 2017-11-03 21:41:42.000 |
| 303795 | 2026-09-25 | 2017-11-03 21:41:18.000 |
| 303734 | 2035-02-21 | 2017-11-03 21:38:21.000 |
| 303701 | 2025-05-02 | 2017-11-03 21:36:44.000 |
| 303681 | 2065-12-13 | 2017-11-03 21:35:45.000 |
| 303398 | 2026-11-12 | 2017-11-03 21:21:39.000 |
| 303279 | 2028-02-14 | 2017-11-03 21:14:49.000 |
| 1 | 2054-10-15 | 2017-10-24 16:47:06.000 |
Audit Member Table:
| ID | Date | InsertedDateTime | DateCreated |
|---|---|---|---|
| 1 | 2054-10-15 | 2023-10-26 02:04:03.167 | 2017-10-24 16:47:06.000 |
| 1 | NULL | 2023-10-06 01:51:29.740 | 2017-10-24 16:47:06.000 |
| 1 | NULL | 2023-02-21 02:44:33.050 | 2017-10-24 16:47:06.000 |
| 1 | NULL | 2023-02-12 03:00:17.440 | 2017-10-24 16:47:06.000 |
| 1 | NULL | 2021-04-24 07:44:12.060 | 2017-10-24 16:47:06.000 |
| 1 | NULL | 2020-11-30 02:38:42.583 | 2017-10-24 16:47:06.000 |
| 240125 | 2032-06-29 | 2023-10-12 01:50:52.803 | 2017-10-26 16:25:39.000 |
| 240125 | 2032-06-29 | 2023-02-12 03:00:17.440 | 2017-10-26 16:25:39.000 |
| 240125 | 2032-06-29 | 2021-05-20 01:36:25.320 | 2017-10-26 16:25:39.000 |
| 240125 | NULL | 2020-09-30 01:35:13.203 | 2017-10-26 16:25:39.000 |
| 240126 | 2047-01-02 | 2023-02-12 03:00:17.440 | 2017-10-26 17:03:18.000 |
| 245705 | 2035-08-28 | 2023-02-12 03:00:17.440 | 2017-10-27 10:56:08.000 |
| 245705 | 2065-08-28 | 2021-09-09 01:38:13.567 | 2017-10-27 10:56:08.000 |
| 245705 | 2035-08-28 | 2021-07-06 01:37:45.513 | 2017-10-27 10:56:08.000 |
| 245705 | 2038-08-28 | 2021-07-01 01:37:30.303 | 2017-10-27 10:56:08.000 |
| 245707 | 2049-03-13 | 2023-02-12 03:00:17.440 | 2017-10-27 11:33:38.000 |
| 251228 | 2047-01-01 | 2023-02-12 03:00:17.440 | 2017-10-27 21:08:16.000 |
| 251228 | NULL | 2021-07-15 08:32:57.513 | 2017-10-27 21:08:16.000 |
I have two tables a Cus table and an Audit Member table the audit_Member table consists of copies of previous records in the main Member Table to keep a history of records. There is an inserted date in the Audit table to keep track of when a record was updated and inserted into the Audit table.
I am trying to find out when a certain column has been changed in this case (Retirement Date).
Output Wanted
| Date | DateFrom | ID | ToDate | FromDate |
|---|---|---|---|---|
| 2035-08-28 | 2035-08-28 | 245705 | 3000-01-01 00:00:00.000 | 2023-02-12 03:00:17.440 |
| 2065-08-28 | 2035-08-28 | 245705 | 2021-09-09 01:38:13.567 | 2021-07-06 01:37:45.513 |
| 2035-08-28 | 2038-08-28 | 245705 | 2021-07-06 01:37:45.513 | 2021-07-01 01:37:30.303 |
| 2038-08-28 | NULL | 245705 | 2021-07-01 01:37:30.303 | 2017-10-27 10:56:08.000 |
My current code so far for current output:
I have only shown one Member record for reference
WITH MemberAudit
AS (SELECT a.[RetirmentDate],
a.ID,
a.Date,
[AuditRecord],
ROW_NUMBER() OVER (PARTITION BY a.ID ORDER BY a.ID, a.Date DESC) + 1 AS [ROWS] -- Create a sequential row number for each record
FROM
(
SELECT c.RetirementDate [RetirmentDate],
c.ID,
GETDATE() [Date],
'No' [AuditRecord]
FROM Members c
UNION
SELECT ac.RetirementDate [RetirmentDate],
ac.ID,
ac.InsertedDateTime,
'Yes' [AuditRecord]
FROM AuditMembers ac
) a
WHERE a.[RetirmentDate] IS NOT NULL),
MemberAudit2
AS (SELECT a.[RetirmentDate],
a.ID,
a.Date,
[AuditRecord],
ROW_NUMBER() OVER (PARTITION BY a.ID ORDER BY a.ID, a.Date DESC) AS [ROWS] -- Create a sequential row number for each record
FROM
(
SELECT c.RetirementDate [RetirmentDate],
c.ID,
GETDATE() [Date],
'No' [AuditRecord]
FROM DwhV21.Members c
UNION
SELECT ac.RetirementDate [RetirmentDate],
ac.ID,
ac.InsertedDateTime,
'Yes' [AuditRecord]
FROM DwhV21.AuditMembers ac
) a
ON a.ID = dc.Member_ID
WHERE a.[RetirmentDate] IS NOT NULL),
MemberAuditJoin
AS (SELECT ca.RetirmentDate,
ca.ID,
ca.Date,
ca.AuditRecord,
ca.ROWS,
ca2.RetirmentDate [RetirmentDate1],
ca2.ID [ID1],
ca2.Date [Date1],
ca2.AuditRecord [AuditRecord1],
ca2.ROWS [ROWS1]
FROM MemberAudit ca
LEFT JOIN MemberAudit2 ca2
ON ca2.ID = ca.ID
AND ca2.ROWS = ca.ROWS),
accountgrouping
AS (SELECT c.*,
--c.RetirmentDate,
--c.ID,
CASE
WHEN c.AuditRecord = 'No' THEN
'3000-01-01'
WHEN c.AuditRecord = 'Yes' THEN
c.Date
END [ToDate],
CASE
WHEN c.AuditRecord = 'No'
AND c.ID1 IS NOT NULL THEN
Date1
WHEN c.AuditRecord = 'No'
AND c.ID1 IS NULL THEN
cus.DateCreated
WHEN c.AuditRecord = 'Yes'
AND c.ID1 IS NULL THEN
cus.DateCreated
WHEN c.AuditRecord = 'Yes'
AND c.ID1 IS NOT NULL THEN
c.Date1
END [FromDate],
CASE
WHEN c.AuditRecord = 'No' THEN
'Yes'
ELSE
'No'
END [CurrentRecord],
CASE
WHEN c.RetirmentDate <> c.Date1 THEN
'Yes'
ELSE
'No'
END [RetirmentDateChange],
CASE
WHEN c.AuditRecord = 'Yes'
AND c.ID1 IS NULL THEN
'FirstAuditRecord'
END [FirstAuditRecordMarker]
FROM MemberAuditJoin c
--WHERE c.RetirmentDate <> ISNULL(c.RetirmentDate1, '2099-01-01')
JOIN DwhV21.Members cus
ON c.ID = cus.ID)
SELECT RetirmentDate,
ag.RetirmentDate1 [RetirmentDateFrom],
ag.ID,
ToDate [ToDate],
ag.FromDate [FromDate],
-- ag.CurrentRecord,
ag.RetirmentDateChange,
ag.FirstAuditRecordMarker,
ROW_NUMBER() OVER (PARTITION BY ag.RetirmentDate,
ag.ID,
ag.RetirmentDate1
ORDER BY ToDate
) AS lineNum
FROM accountgrouping ag
ORDER BY ag.ID,
ToDate DESC;
This I believe creates the results you wish. Just a simple case of using LAG to get the previous values to detect change and build the from/to date range.
with cte1 as (
-- Lag relevant columns to detect changes
select c.CustomerID, c.DateCreated
, case when ac.CustomerID is not null then ac.[RetirementDate] else c.RetirementDate end RetirementDate
, ac.[InsertedDateTime]
, lag(ac.RetirementDate) over (partition by ac.CustomerId order by ac.InsertedDateTime asc) RetirementDateFrom
, lag(ac.InsertedDateTime) over (partition by ac.CustomerId order by ac.InsertedDateTime asc) PreviousChangeDate
from Customers c
left join AuditCustomers ac on ac.CustomerID = c.CustomerID
), cte2 as (
-- Remove sequential duplicate rows i.e. if the retirement age doesn't change from one row to the next
select
RetirementDate
, RetirementDateFrom
, CustomerId
, coalesce(PreviousChangeDate,DateCreated) FromDate
, InsertedDateTime
, row_number() over (partition by cte1.CustomerId order by cte1.InsertedDateTime desc) rn
from cte1
where RetirementDate is not null
and (RetirementDateFrom is null or RetirementDate <> RetirementDateFrom)
)
-- Transform a few values as required
select
RetirementDate
, RetirementDateFrom
, CustomerId
, FromDate
, case when rn = 1 then '3000-01-01' else InsertedDateTime end ToDate
, case when RetirementDate <> RetirementDateFrom then 1 else 0 end RetirementDateChange
, case when RetirementDateFrom is null then 'FirstAuditRecord' else null end FirstAuditRecord
from cte2
order by customerid asc, InsertedDateTime desc;
| RetirementDate | RetirementDateFrom | CustomerId | FromDate | ToDate | RetirementDateChange | FirstAuditRecord |
|---|---|---|---|---|---|---|
| 2054-10-15 | null | 1 | 2023-10-06 01:51:29.740 | 3000-01-01 00:00:00.000 | 0 | FirstAuditRecord |
| 2054-10-15 | null | 2 | 2017-10-24 00:00:00.000 | 3000-01-01 00:00:00.000 | 0 | FirstAuditRecord |
| 2032-06-29 | null | 240125 | 2020-09-30 01:35:13.203 | 3000-01-01 00:00:00.000 | 0 | FirstAuditRecord |
| 2047-01-02 | null | 240126 | 2017-10-26 00:00:00.000 | 3000-01-01 00:00:00.000 | 0 | FirstAuditRecord |
| 2035-08-28 | 2065-08-28 | 245705 | 2021-09-09 01:38:13.567 | 3000-01-01 00:00:00.000 | 1 | null |
| 2065-08-28 | 2035-08-28 | 245705 | 2021-07-06 01:37:45.513 | 2021-09-09 01:38:13.567 | 1 | null |
| 2035-08-28 | 2038-08-28 | 245705 | 2021-07-01 01:37:30.303 | 2021-07-06 01:37:45.513 | 1 | null |
| 2038-08-28 | null | 245705 | 2017-10-27 00:00:00.000 | 2021-07-01 01:37:30.303 | 0 | FirstAuditRecord |
| 2049-03-13 | null | 245707 | 2017-10-27 00:00:00.000 | 3000-01-01 00:00:00.000 | 0 | FirstAuditRecord |
| 2047-01-01 | null | 251228 | 2021-07-15 08:32:57.513 | 3000-01-01 00:00:00.000 | 0 | FirstAuditRecord |
| 2071-10-01 | null | 266770 | 2022-11-17 02:50:49.087 | 3000-01-01 00:00:00.000 | 0 | FirstAuditRecord |
| 2044-06-02 | null | 297740 | 2021-02-22 02:38:36.873 | 3000-01-01 00:00:00.000 | 0 | FirstAuditRecord |
| 2037-01-07 | null | 299765 | 2020-06-20 01:34:39.790 | 3000-01-01 00:00:00.000 | 0 | FirstAuditRecord |
| 2053-09-06 | null | 303015 | 2019-11-08 02:32:52.680 | 3000-01-01 00:00:00.000 | 0 | FirstAuditRecord |
| 2034-01-25 | null | 303021 | 2017-11-03 00:00:00.000 | 3000-01-01 00:00:00.000 | 0 | FirstAuditRecord |
| 2025-10-08 | null | 303045 | 2017-11-03 00:00:00.000 | 3000-01-01 00:00:00.000 | 0 | FirstAuditRecord |
| 2028-10-30 | null | 303172 | 2020-03-29 01:32:11.793 | 3000-01-01 00:00:00.000 | 0 | FirstAuditRecord |
| 2028-02-14 | null | 303279 | 2023-02-02 02:54:20.713 | 3000-01-01 00:00:00.000 | 0 | FirstAuditRecord |
| 2022-11-12 | null | 303398 | 2021-12-23 02:38:29.560 | 3000-01-01 00:00:00.000 | 0 | FirstAuditRecord |
| 2065-12-13 | null | 303681 | 2020-07-04 01:39:00.880 | 3000-01-01 00:00:00.000 | 0 | FirstAuditRecord |
| 2019-02-21 | null | 303734 | 2017-11-03 00:00:00.000 | 3000-01-01 00:00:00.000 | 0 | FirstAuditRecord |
| 2026-09-25 | null | 303795 | 2021-06-28 01:36:26.573 | 3000-01-01 00:00:00.000 | 0 | FirstAuditRecord |
| 2025-10-08 | null | 303804 | 2022-09-06 01:45:54.723 | 3000-01-01 00:00:00.000 | 0 | FirstAuditRecord |
| 2061-07-30 | null | 303843 | 2017-11-03 00:00:00.000 | 3000-01-01 00:00:00.000 | 0 | FirstAuditRecord |
| 2044-03-02 | null | 303863 | 2021-01-25 02:39:37.847 | 3000-01-01 00:00:00.000 | 0 | FirstAuditRecord |
| 2039-11-29 | null | 303896 | 2020-12-11 06:48:24.517 | 3000-01-01 00:00:00.000 | 0 | FirstAuditRecord |