You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Propagate non NULL values Up and Down in SQL Server.
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
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
See the data
SELECT * FROM dbo.LVExample ORDER BY TaskID, Date;
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