Propagate non NULL values Up and Down in SQL Server.md

Propagate non NULL values Up and Down in SQL Server.

1. Create table

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Create Table
CREATE TABLE dbo.LVExample(
	ID smallint IDENTITY(1,1) NOT NULL,
	TaskID int NULL,
	Status varchar(50) NULL,
	Worker varchar(50) NULL,
	Date datetime NOT NULL
) 
GO

-- Set Identity Column
SET IDENTITY_INSERT dbo.LVExample ON 
GO

2. Insert some data

INSERT dbo.LVExample (ID, TaskID, Status, Worker, Date) VALUES (1, 1001, N'Open', N'Merin', CAST(N'2021-09-21T10:08:30.247' AS DateTime))
GO
INSERT dbo.LVExample (ID, TaskID, Status, Worker, Date) VALUES (2, 1002, N'Open', N'Suraj', CAST(N'2021-09-21T10:08:30.247' AS DateTime))
GO
INSERT dbo.LVExample (ID, TaskID, Status, Worker, Date) VALUES (3, 1001, N'Pending', N'Lily', CAST(N'2021-09-21T10:08:30.247' AS DateTime))
GO
INSERT dbo.LVExample (ID, TaskID, Status, Worker, Date) VALUES (4, 1002, N'Pending', N'Suraj', CAST(N'2021-09-21T10:08:30.247' AS DateTime))
GO
INSERT dbo.LVExample (ID, TaskID, Status, Worker, Date) VALUES (5, 1002, N'Completed', N'Suraj', CAST(N'2021-09-21T10:08:30.247' AS DateTime))
GO
INSERT dbo.LVExample (ID, TaskID, Status, Worker, Date) VALUES (6, 1001, N'Reassigned', N'Lily', CAST(N'2021-09-21T10:09:54.633' AS DateTime))
GO
INSERT dbo.LVExample (ID, TaskID, Status, Worker, Date) VALUES (7, 1002, NULL, NULL, CAST(N'2021-09-21T10:12:38.743' AS DateTime))
GO
INSERT dbo.LVExample (ID, TaskID, Status, Worker, Date) VALUES (8, 1002, N'Reassigned', NULL, CAST(N'2021-09-21T10:12:49.940' AS DateTime))
GO
INSERT dbo.LVExample (ID, TaskID, Status, Worker, Date) VALUES (9, 1002, N'Done', NULL, CAST(N'2021-09-21T10:12:58.003' AS DateTime))
GO
INSERT dbo.LVExample (ID, TaskID, Status, Worker, Date) VALUES (10, 1001, NULL, NULL, CAST(N'2021-09-21T10:13:12.850' AS DateTime))
GO
INSERT dbo.LVExample (ID, TaskID, Status, Worker, Date) VALUES (11, 1001, N'Completed', NULL, CAST(N'2021-09-21T10:13:24.587' AS DateTime))
GO
INSERT dbo.LVExample (ID, TaskID, Status, Worker, Date) VALUES (12, 1001, NULL, N'Merin', CAST(N'2021-09-21T10:13:38.447' AS DateTime))
GO
INSERT dbo.LVExample (ID, TaskID, Status, Worker, Date) VALUES (13, 1001, NULL, N'Merin', CAST(N'2021-09-21T10:16:11.603' AS DateTime))
GO
INSERT dbo.LVExample (ID, TaskID, Status, Worker, Date) VALUES (14, 1001, NULL, N'Merin', CAST(N'2021-09-21T10:16:51.613' AS DateTime))
GO
INSERT dbo.LVExample (ID, TaskID, Status, Worker, Date) VALUES (15, 1001, NULL, N'Merin', CAST(N'2021-09-21T10:17:17.367' AS DateTime))
GO
INSERT dbo.LVExample (ID, TaskID, Status, Worker, Date) VALUES (16, 1001, N'Done', N'Merin', CAST(N'2021-09-21T10:17:28.930' AS DateTime))
GO
SET IDENTITY_INSERT dbo.LVExample OFF
GO
ALTER TABLE dbo.LVExample ADD  CONSTRAINT DF_LVExample_LastUpdated  DEFAULT (getdate()) FOR Date
GO

3. See the data

SELECT * FROM dbo.LVExample  ORDER BY TaskID, Date;

4. Query to propate data

WITH RankedColumns AS
(
SELECT ID,
	TaskID,
	Status,
	COUNT(Status) OVER(PARTITION BY TaskID ORDER BY Date DESC, ID DESC) AS Status_ReversedRank,
	COUNT(Status) OVER(PARTITION BY TaskID ORDER BY Date, ID) AS Status_Rank,
	Worker,
	COUNT(Worker) OVER(PARTITION BY TaskID ORDER BY Date DESC, ID DESC) AS Worker_ReversedRank,
	COUNT(Worker) OVER(PARTITION BY TaskID ORDER BY Date, ID) AS Worker_Rank,
	Date
	FROM dbo.LVExample--  ORDER BY TaskID, Date
)
SELECT 
	TaskID, 
	Status,
	MAX(Status) OVER(PARTITION BY TaskID, Status_ReversedRank ORDER BY Date DESC, ID DESC) AS Status_PropagatedUp,
	Status,
	MAX(Status) OVER(PARTITION BY TaskID, Status_Rank ORDER BY Date, ID) AS Status_PropagatedDown,
	Worker,
	MAX(Worker) OVER(PARTITION BY TaskID, Worker_ReversedRank ORDER BY Date DESC, ID DESC) AS Worker_PropagatedUp,
	Worker,
	MAX(Worker) OVER(PARTITION BY TaskID, Worker_Rank ORDER BY Date, ID) AS Worker_PropagatedDown,
	Date
FROM RankedColumns
 ORDER BY TaskID, Date 

Final Result

t