using lag/lead with SQL Server

Question

Struggling with a query using LAG:

My goal is to get ONE record per customer and show the CURRENT value of a field and the PREVIOS value of a field all in the same record. I guess I'm going about it all wrong. My current query and a snippet of results is below. I also cant seem to get rid of the NULLs. Any ideas (or let me know if you need more info). Thanks.

select accountid,property,convert(date,left(traceid,8)) setdate,lag(value,1) over(partition by accountid order by convert(date,left(traceid,8)) desc) as curval

,lag(value,2) over(partition by accountid order by convert(date,left(traceid,8)) desc) as prevval

from propertyaudit

where property = 'BlockedAccountReason' and value is not null;

Answer 1

Hi @Dom

My goal is to get ONE record per customer and show the CURRENT value of a field and the PREVIOS value of a field all in the same record.

Normally, you don't need to use Lag to get current value.

I also cant seem to get rid of the NULLs.

For the first row of each group, it is always Null when calculate previous row.

If you do not want NULL values, then you could add a default blank value like this: lag(value,1,'') over(partition by accountid order by convert(date,left(traceid,8)) desc)

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".