I wanted to get TOP 5 records from log table where “Approve date” is changed like NULL to value and vice versa. Date value is doesn’t matter, but order matters.
ApprovedDate ChangeDate changeByUser NULL 2019-12-09 06:40:15.437 vaisakh NULL 2019-12-09 06:42:31.563 vaisakh NULL 2019-12-09 06:42:33.140 vaisakh NULL 2019-12-09 07:03:54.660 vaisakh 2019-12-09 07:05:29.800 2019-12-09 07:05:29.817 vaisakh 2019-12-09 07:05:29.800 2019-12-09 07:05:38.707 vaisakh NULL 2019-12-09 07:09:33.160 vaisakh NULL 2019-12-09 07:09:42.440 vaisakh NULL 2019-12-09 09:38:19.757 vaisakh 2019-12-09 09:41:42.977 2019-12-09 09:41:43.243 Raveendran
In this case I want first record and 5th record (Someone approved the data that’s why a value),then 7th record value is null someone rejected it.
I tried using recursive CTE it is working but for large records huge performance issue
DECLARE @today DATETIME = GETDATE(); with RESULT (CIPApprovedDate,ChangeDate,changeByUser,legalId,depth)AS( SELECT TOP 1 CIPApprovedDate, ChangeDate, changeByUser, legalId,1 FROM LegalEntityExtensionLog WHERE legalId= 2688518 ORDER BY ChangeDate ASC union ALL select L.CIPApprovedDate, L.ChangeDate, L.changeByUser, L.legalId,ct.depth+1 FROM LegalEntityExtensionLog L INNER JOIN Result CT on L.legalId=CT.legalId AND L.changeDate>CT.changeDate AND ISNULL(L.CIPApprovedDate,@today) <> ISNULL(CT.CIPApprovedDate,@today) )select * from Log where ChangeDate in(select MIN(ChangeDate) from Result group by depth)
Advertisement
Answer
You can pull out the transition records like so:
select ApprovedDate, ChangeDate, changeByUser from ( select l.*, lag(ApprovedDate) ver(partition by LegalId order by ChangeDate) lagApprovedDate from LegalEntityExtensionLog l ) t where (lagApprovedDate is null and ApprovedDate is not null) or (lagApprovedDate is not null and ApprovedDate is null)
This will exhibit records where ApprovedDate
transitioned from a null
to a non null
value (or the other way around).