Search code examples
sql-serverlag

Ignoring Nulls using Lag() in SQL Server 2018

I am trying to get the Activity ID linked to Dispatch. In the data Attrib_43 will only get populated if Dispatch is created.

What i am looking to do is to get the Activity ID of the previous row before the Dispatch created.

Attached is the code that i am using

 Select  sea.ROW_ID, sea.CREATED_DTTM,sea.SRA_SR_ID,sea.ATTRIB_43, tsk.CRT_DTS, tsk.TASK_DESC, datediff(ss,sea.CREATED_DTTM, tsk.CRT_DTS) as dd 

, seal.x_isp_notes, seal.x_isp_comments, seal.comments, seal.x_isp_agent_desc

, tsk.TASK_SUB_TYPE_CD, tsk.TASK_TYPE_CD, tsk.WHAT_ID

, cdl.ORIGIN_NM
,LAG(sea.ROW_ID,1) over (partition by sea.ATTRIB_43 order by sea.CREATED_DTTM) AS 'FLAGID'

--, tsk.*,

, fdc.FISCAL_QUARTER

from GSEDATA.dbo.X_ISP_EXTRNL_CASE_ID sea

join rawdata.corp_ww.FISCAL_DAY_CALENDAR fdc on sea.CREATED_DATE = fdc.ACTUAL_DATE

left join rawdata.svc_base.SFDC_TASK_DTL tsk on sea.X_ISP_EXTRNL_CASE_ID = tsk.TASK_ID

left join rawdata.svc_base.SFDC_CASE_DTL cdl on cdl.case_id = tsk.what_id

left join GSEDATA.dbo.s_evt_act_logs seal on sea.ROW_ID = seal.row_id

where --sea.ATTRIB_43 = '04391481876'
sea.SRA_SR_ID = 'A-2Q7YF57W'

order by sea.CREATED_DTTM

But it is not working as per my Expectation - Activity ID flag for Attrib 43 is coming as Null

enter image description here

Solution

  • If I understand your question correctly, you are getting null FLAGID for non- null attrib_43 becuase you are using a partition by sea.ATTRIB_43 clause. Partition by divides the query result set into partitions. The window function is applied to each partition separately and computation restarts for each partition. That is why the null values for ATTRIB_43 are grouped into one window and the non-null ATTRIB_43 will have a separate window for each distinct value, therefore giving a NULL for LAG() function in the first row of each window. you should remove the clause partition by sea.ATTRIB_43 if you want lag values for all rows.

    LAG(sea.ROW_ID,1) over (order by sea.CREATED_DTTM) AS 'FLAGID'