In the below table, the [MonthEndDate] has non-null values. Relative to this, from column [employeeId] till column [jobDescription] all null values should be forward-filled with data in the row 4 till row 16 and for last 2 columns i.e. [StaffTypeID] and [Description], all null values should be forward-filled with data in the row 1 till row 12 and similarly for the remaining rows.

Please use this db-fiddle link containing the required CREATE and INSERT statements to generate the table in problem statement.

Table 1 is table in Problem Statement and Table 2 is the desired output.

I would be appreciate if you could share the required SQL code.

Table 1

Table 2:

CodePudding user response：

This shows only for the columns employeeId,last_day_jobeffectiveDate how you can do it.

It uses for every column the island and gaps algorithm

You of course have to complete it for the rest

You need to check the CASE WHENfor every column, as determins which cases should get then MAX values and which not

MERGE empworkhours AS tgt  
USING (SELECT
[MonthEndDate],
CASE WHEN
  ([employeeId] IS NULL 
  OR TRIM([employeeId]) = '')
    AND MAX([employeeId]) OVER(
                      ORDER BY MonthEndDate
                      ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) IS NOT NULL
  
  THEN 
  MAX([employeeId]) OVER(
                      ORDER BY MonthEndDate
                      ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
  ELSE [employeeId] END as [employeeId]
  ,CASE WHEN
  ([last_day_jobeffectiveDate] IS NULL 
  )
    AND MAX([last_day_jobeffectiveDate]) OVER(
                      ORDER BY MonthEndDate
                      ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) IS NOT NULL
  
  THEN 
  MAX([last_day_jobeffectiveDate]) OVER(
                      ORDER BY MonthEndDate
                      ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
  ELSE [last_day_jobeffectiveDate] END as [last_day_jobeffectiveDate]
FROM [empworkhours]) as src ([MonthEndDate],employeeId,last_day_jobeffectiveDate)  
ON (tgt.[MonthEndDate] = src.[MonthEndDate])  
WHEN MATCHED AND tgt.last_day_jobeffectiveDate IS NULL  
    THEN UPDATE SET tgt.[employeeId] =  src.[employeeId],
                    tgt.[last_day_jobeffectiveDate] = src.[last_day_jobeffectiveDate];

Warning: Null value is eliminated by an aggregate or other SET operation.
22 rows affected

SELECT * FROM empworkhours

fiddle

CodePudding user response：

I did this just a little differently as I did not use a merge and was also forced to use an additional CTE to fix the employeeStatus issue. I also put my data into a temp table, so you will have to change that in your fiddle. Something like this might work:

;WITH CTE AS
(
SELECT MonthEndDate, MAX(last_day_jobeffectiveDate) OVER(ORDER BY MonthEndDate ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW) last_day_jobeffectiveDate ,
          MAX(employeeType) OVER(ORDER BY MonthEndDate ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW) employeeType,
         MAX(EmployeeID) OVER(ORDER BY MonthEndDate ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW) EmployeeID,
          MAX(scheduledWorkHours) OVER(ORDER BY MonthEndDate ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW) scheduledWorkHours ,
          MAX(supervisorId) OVER(ORDER BY MonthEndDate ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW) supervisorId ,
          MAX(JobCode) OVER(ORDER BY MonthEndDate ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW) JobCode,
          MAX(JobDescription) OVER(ORDER BY MonthEndDate ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW) JobDescription,
          MAX(StaffTypeID) OVER(ORDER BY MonthEndDate ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW) StaffTypeID,
          MAX(Description) OVER(ORDER BY MonthEndDate ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW) Description
         , t1.employeeStatus        
FROM #empworkhours t1
),CTE2 AS
(
    SELECT MonthEndDate,
            EmployeeID,
            last_day_jobeffectiveDate,
            employeeType, 
            MAX(employeeStatus) OVER( Partition by last_day_jobeffectiveDate ORDER BY MonthEndDate ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW ) employeeStatus,
            scheduledWorkHours,
            supervisorId,
            JobCode,
            JobDescription,
            StaffTypeID,
            Description
    FROM CTE
)

UPdATE #empworkhours
    SET employeeId = t2.EmployeeID
    , last_day_jobeffectiveDate = t2.last_day_jobeffectiveDate
    ,employeeType = t2.employeeType
    ,employeeStatus = t2.employeeStatus
    ,scheduledWorkHours = t2.scheduledWorkHours
    ,supervisorId = t2.supervisorId
    ,jobCode = t2.JobCode
    ,jobDescription = t2.JobDescription
    ,StaffTypeID = t2.StaffTypeID
    ,Description = t2.Description
FROM CTE2 t2
INNER JOIN #empworkhours t1 on t1.MonthEndDate = t2.MonthEndDate