CODEWITHSUNDEEP
sqlsql-server
Nic
Nic

Reputation: 127

Get the value on previous row (LAG not working on SQL Server 2008)

I have the following table:

Query ID    No_    Name     Value           Description
   3       PP120    C1         0            NULL
   2       PP121    NULL       140          Positive
   3       PP122    C1         0            NULL
   3       PP123    C1         0            NULL
   3       PP124    C1         0            NULL
   3       PP125    C1         0            NULL
   2       PP126    NULL       170          Positive 
   2       PP127    NULL       -658         Positive
   2       PP128    NULL       547          Positive
   3       PP129    C1         0            NULL
   3       PP130    C1         0            NULL
   3       PP131    C1         0            NULL

Which is the result of the union of two different queries (see ID query):

SELECT [Query ID]=2, SL.[No_],  CL.[Name] ,SL.Value, SL.Type
FROM SLine SL
LEFT JOIN CLine CL ON SL.[No_] = CL.[No_]       
WHERE 
SL.[Type] <> '' 

UNION 

SELECT [Query ID]=3, SL.[No_],  CL.[Name] ,SL.Value, SL.Type
FROM SLine] SL
LEFT JOIN CLine CL ON SL.[No_] = CL.[No_]   
WHERE 
SL.[Type] <> ''

I would need to replace all the variable 'name' with the value that has the same No_ on different Query ID. i.e I would need:

  Query ID    No_   Name       Value        Description
   3       PP120    C1         0            NULL
   2       PP121    C1         140          Positive
   3       PP122    C1         0            NULL
   3       PP123    C1         0            NULL
   3       PP124    C1         0            NULL
   3       PP125    C1         0            NULL
   2       PP126    C1         170          Positive 
   2       PP127    C1         -658         Positive
   2       PP128    C1     547          Positive
   3       PP129    C1         0            NULL
   3       PP130    C1         0            NULL
   3       PP131    C1         0            NULL

The table I am providing to you is just a part of the 12000 rows of the result. I tried to to the following to get the same Name for each No_ but as I need to use SQL server 2008 I cannot use the following code:

SELECT      
CASE WHEN [Name] IS NULL THEN LEAD([Name], 1, 0) OVER 
(PARTITION BY [No_] ORDER BY [No_] ASC) AS [Name_], * FROM (

SELECT [Query ID]=3, SL.[No_],  CL.[Name] ,SL.Value, SL.Type
FROM SLine SL
LEFT JOIN CLine CL ON SL.[No_] = CL.[No_]       
WHERE 
SL.[Type] <> '' 

UNION 

SELECT [Query ID]=2, SL.[No_],  CL.[Name] ,SL.Value, SL.Type
FROM SLine] SL
LEFT JOIN CLine CL ON SL.[No_] = CL.[No_]   
WHERE 
SL.[Type] <> '' 
)V

Any suggestion as alternative to LAG function?

Upvotes: 1

Views: 938

Answers (1)

JohnHC
JohnHC

Reputation: 11205

Use a CTE and row_number, then self join to create a pseudo lag/lead

With CTE as
(
select Q1.*, row_number() over (order by [No_]) rn
from MyQuery Q1
)
select C1.*, 
       case when c2.Column is null then C1.Column else c2.Column end as Pseudo_Lead
from CTE c1
left join CTE c2
on c2.rn = c1.rn +1
and <any other partition conditions>

Upvotes: 3

Related Questions