Sql-server – copy data from Previous non null rows and if first row is null copy next non null value

Here is a recursive CTE solution using a technique that Paul White blogged about in Performance Tuning the Whole Query Plan.

declare @T table
(
  Eventdate date index IX_Eventdate clustered,
  Val int
);

insert into @T(Eventdate, Val) values
('2012-03-23',  3965),
('2012-03-26',  3979),
('2012-03-27',  3974),
('2012-03-28',  3965),
('2012-03-29',  3967),
('2012-03-30',  3959),
('2012-04-02',  3951),
('2012-04-03',  3961),
('2012-04-04',  3944),
('2012-04-05',  3935),
('2012-04-09',  3901),
('2012-04-10',  3822);

with C as
(
  select top(1) 
         T.Eventdate,
         T.Val,
         T.Val as PrevVal1,
         T.Val as PrevVal2
  from @T as T
  order by T.eventdate
  union all
  select T.Eventdate,
         T.Val,
         T.PrevVal1,
         T.PrevVal2
  from (
       select T.Eventdate,
              T.Val,
              iif(abs(T.Val - C.PrevVal1) <= 12, C.PrevVal1, T.Val) as PrevVal1,
              iif(abs(T.Val - C.PrevVal1) <= 12, null, T.Val) as PrevVal2,
              row_number() over(order by T.Eventdate) as rn
       from @T as T
       inner join C
         on C.Eventdate < T.Eventdate
       ) as T
  where T.rn = 1
)
select C.Eventdate,
       C.PrevVal2 as Val
from C
order by C.Eventdate
option (maxrecursion 0);

Is it possible to redesign your schema? It feels like you are making life harder for yourself by basically trying to pivot the data you're importing from the excel spreadsheets.

CREATE TABLE dbo.Hardware -- hw?
(
    [Event_Num] INT NOT NULL PRIMARY KEY,
    [Name] NVARCHAR(100) NOT NULL,
    [Install_Date] DATE NULL,  -- Install after pulling?
    [Pull_Date] DATE NOT NULL,
    [Install_Tech] INT NULL FOREIGN KEY REFERENCES dbo.techs(UserId), -- separate out your techs to another table
    [Pull_Tech] DATE NOT NULL FOREIGN KEY REFERENCES dbo.techs(UserId), -- separate out your techs to another table
    <Add other common fields here>
)

CREATE TABLE dbo.ComponentType -- Or some better name for PMP, Motor, dis, etc. table
(
    [ComponentTypeId] INT NOT NULL PRIMARY KEY,
    [Description] NVARCHAR(50) NOT NULL -- This is where you would put PMP, Motor, etc.
)

CREATE TABLE dbo.WorkPerformed
(
    [WorkPerformedId] INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    [ComponentTypeId] INT NOT NULL FOREIGN KEY REFERENCES dbo.ComponentType(ComponentTypeId), -- FK to type lets us reuse this structure for all components and prevents the need to pivot the excel data
    [Event_Num] INT NOT NULL FOREIGN KEY REFERENCES dbo.Hardware(Event_Num), -- Allows you to associate the work performed with the hw
    [Description] NVARCHAR(400) NULL,
    [SerialNumber] NVARCHAR(50) NOT NULL,
    [Part] NVARCHAR(50) NOT NULL,
    <Other valuable info not currently tracking, ie pulled/installed, tech doing work, etc>
)

Obviously that is just a rough sample of the way the schema could be setup. I'm sure you can see that mapping the data will be much easier now, and more flexable for future updates. In order to maintain backwards compatibility, if needed, you could just create a view with the current table's name and select the data from the new tables.

If going down this path is not possible/acceptable I would look at pulling your data out of the temp table and inserting it into your current table with a PIVOT. See this TechNet article for basic information about pivoting (the syntax from 2008R2 will work in 2012).