[Solved] SQL Server Query for last and average values

by


Please use this to achieve your result:

select 
     [name]     =   [name]      
    ,[rowid]    =   [rowid] 
    ,[type]     =   [type]          
    ,[company]  =   [company]     
    ,[date]     =   [date]      
    ,[kpi]      =   [kpi]   
    ,[value]    =   iif([rowid] = 1, isnull([kpi], [lvalue].[value]), isnull([kpi], [avgvalue].[value]))    
from
    #urgent as  [u]
outer apply
    (
        select top 1 [value] = [kpi] from #urgent where [rowid] = 1 and [kpi] is not null order by [date] desc 
    ) as [lvalue]
outer apply
    (
        select [value] = avg([kpi]) from #urgent where [rowid] = 2  and [kpi] is not null
    ) as [avgvalue];

Full script: https://pastebin.com/fJQB6wyP

solved SQL Server Query for last and average values