Replace zero values with last non-zero value in new column

Transact-SQL
1

I have a select from a calendar table, where I want to include a new column with the zero values replaced the with the last non-zero value in column TCW

Here is the partial result of my select from the table:

+-----+------------+---------+-----+
| SID |  CALDATE   | CALWORK | TCW |
+-----+------------+---------+-----+
|  31 | 04/01/2016 |       1 |   1 |
|  41 | 05/01/2016 |       1 |   2 |
|  51 | 06/01/2016 |       1 |   3 |
|  61 | 07/01/2016 |       1 |   4 |
|  71 | 08/01/2016 |       1 |   5 |
|  81 | 09/01/2016 |       0 |   0 |
|  91 | 10/01/2016 |       0 |   0 |
| 101 | 11/01/2016 |       1 |   6 |
| 111 | 12/01/2016 |       1 |   7 |
| 121 | 13/01/2016 |       1 |   8 |
| 131 | 14/01/2016 |       1 |   9 |
| 141 | 15/01/2016 |       1 |  10 |
| 151 | 16/01/2016 |       0 |   0 |
| 161 | 17/01/2016 |       0 |   0 |
| 171 | 18/01/2016 |       1 |  11 |
| 181 | 19/01/2016 |       1 |  12 |
| 191 | 20/01/2016 |       1 |  13 |
| 201 | 21/01/2016 |       1 |  14 |
| 211 | 22/01/2016 |       1 |  15 |
| 221 | 23/01/2016 |       0 |   0 |
| 231 | 24/01/2016 |       0 |   0 |
| 241 | 25/01/2016 |       1 |  16 |
+-----+------------+---------+-----+

What I want to achieve is a new column result TCWN, with the zeroes replaced with the last non-zero value:

+-----+------------+---------+-----+------+
| SID |  CALDATE   | CALWORK | TCW | TCWN |
+-----+------------+---------+-----+------+
|  31 | 04/01/2016 |       1 |   1 |    1 |
|  41 | 05/01/2016 |       1 |   2 |    2 |
|  51 | 06/01/2016 |       1 |   3 |    3 |
|  61 | 07/01/2016 |       1 |   4 |    4 |
|  71 | 08/01/2016 |       1 |   5 |    5 |
|  81 | 09/01/2016 |       0 |   0 |    5 |
|  91 | 10/01/2016 |       0 |   0 |    5 |
| 101 | 11/01/2016 |       1 |   6 |    6 |
| 111 | 12/01/2016 |       1 |   7 |    7 |
| 121 | 13/01/2016 |       1 |   8 |    8 |
| 131 | 14/01/2016 |       1 |   9 |    9 |
| 141 | 15/01/2016 |       1 |  10 |   10 |
| 151 | 16/01/2016 |       0 |   0 |   10 |
| 161 | 17/01/2016 |       0 |   0 |   10 |
| 171 | 18/01/2016 |       1 |  11 |   11 |
| 181 | 19/01/2016 |       1 |  12 |   12 |
| 191 | 20/01/2016 |       1 |  13 |   13 |
| 201 | 21/01/2016 |       1 |  14 |   14 |
| 211 | 22/01/2016 |       1 |  15 |   15 |
| 221 | 23/01/2016 |       0 |   0 |   15 |
| 231 | 24/01/2016 |       0 |   0 |   15 |
| 241 | 25/01/2016 |       1 |  16 |   16 |
+-----+------------+---------+-----+------+

My select is like this:

SELECT [SID],
CALDATE,
CALWORK,
TCW
FROM DimDate
ORDER BY CALDATE asc

What's the best way to achieve this? Here is a create table script and some sample data:

CREATE TABLE DimDate (
[SID] int,
[CALDATE] date,
[CALWORK] int,
[TCW] int
)

INSERT [dbo].[DimDate] ([SID], [CALDATE], [CALWORK], [TCW]) VALUES (31, CAST(N'2016-01-04' AS Date), 1, 1)
GO
INSERT [dbo].[DimDate] ([SID], [CALDATE], [CALWORK], [TCW]) VALUES (41, CAST(N'2016-01-05' AS Date), 1, 2)
GO
INSERT [dbo].[DimDate] ([SID], [CALDATE], [CALWORK], [TCW]) VALUES (51, CAST(N'2016-01-06' AS Date), 1, 3)
GO
INSERT [dbo].[DimDate] ([SID], [CALDATE], [CALWORK], [TCW]) VALUES (61, CAST(N'2016-01-07' AS Date), 1, 4)
GO
INSERT [dbo].[DimDate] ([SID], [CALDATE], [CALWORK], [TCW]) VALUES (71, CAST(N'2016-01-08' AS Date), 1, 5)
GO
INSERT [dbo].[DimDate] ([SID], [CALDATE], [CALWORK], [TCW]) VALUES (81, CAST(N'2016-01-09' AS Date), 0, 0)
GO
INSERT [dbo].[DimDate] ([SID], [CALDATE], [CALWORK], [TCW]) VALUES (91, CAST(N'2016-01-10' AS Date), 0, 0)
GO
INSERT [dbo].[DimDate] ([SID], [CALDATE], [CALWORK], [TCW]) VALUES (101, CAST(N'2016-01-11' AS Date), 1, 6)
GO
INSERT [dbo].[DimDate] ([SID], [CALDATE], [CALWORK], [TCW]) VALUES (111, CAST(N'2016-01-12' AS Date), 1, 7)
GO
INSERT [dbo].[DimDate] ([SID], [CALDATE], [CALWORK], [TCW]) VALUES (121, CAST(N'2016-01-13' AS Date), 1, 8)
GO
INSERT [dbo].[DimDate] ([SID], [CALDATE], [CALWORK], [TCW]) VALUES (131, CAST(N'2016-01-14' AS Date), 1, 9)
GO
INSERT [dbo].[DimDate] ([SID], [CALDATE], [CALWORK], [TCW]) VALUES (141, CAST(N'2016-01-15' AS Date), 1, 10)
GO
INSERT [dbo].[DimDate] ([SID], [CALDATE], [CALWORK], [TCW]) VALUES (151, CAST(N'2016-01-16' AS Date), 0, 0)
GO
INSERT [dbo].[DimDate] ([SID], [CALDATE], [CALWORK], [TCW]) VALUES (161, CAST(N'2016-01-17' AS Date), 0, 0)
GO
INSERT [dbo].[DimDate] ([SID], [CALDATE], [CALWORK], [TCW]) VALUES (171, CAST(N'2016-01-18' AS Date), 1, 11)
GO
INSERT [dbo].[DimDate] ([SID], [CALDATE], [CALWORK], [TCW]) VALUES (181, CAST(N'2016-01-19' AS Date), 1, 12)
GO
INSERT [dbo].[DimDate] ([SID], [CALDATE], [CALWORK], [TCW]) VALUES (191, CAST(N'2016-01-20' AS Date), 1, 13)
GO
INSERT [dbo].[DimDate] ([SID], [CALDATE], [CALWORK], [TCW]) VALUES (201, CAST(N'2016-01-21' AS Date), 1, 14)
GO
INSERT [dbo].[DimDate] ([SID], [CALDATE], [CALWORK], [TCW]) VALUES (211, CAST(N'2016-01-22' AS Date), 1, 15)
GO
INSERT [dbo].[DimDate] ([SID], [CALDATE], [CALWORK], [TCW]) VALUES (221, CAST(N'2016-01-23' AS Date), 0, 0)
GO
INSERT [dbo].[DimDate] ([SID], [CALDATE], [CALWORK], [TCW]) VALUES (231, CAST(N'2016-01-24' AS Date), 0, 0)
GO
INSERT [dbo].[DimDate] ([SID], [CALDATE], [CALWORK], [TCW]) VALUES (241, CAST(N'2016-01-25' AS Date), 1, 16)

Many thanks
Martyn

2 

SELECT 
    dd.*, 
    CASE WHEN TCW <> 0 THEN TCW ELSE 
        (SELECT TOP (1) TCW FROM dbo.DimDate dd2 
         WHERE dd2.CALDATE < dd.CALDATE AND dd2.TCW <> 0 ORDER BY dd2.CALDATE DESC)
    END AS TCWN
FROM dbo.DimDate dd
1 Like
3

For best performance, you'll want an index keyed on CALDATE INCLUDE ( TCW) to support that query.

4

Thanks Scott, works perfectly and I've added the index.

5

Awesome. Thanks for the feedback.