There are few ways of doing this, here is a quick window function solution for SQL Server 2012/14
😎
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID('dbo.TBL_LABEL') IS NOT NULL DROP TABLE dbo.TBL_LABEL;
CREATE TABLE dbo.TBL_LABEL
(
c1 VARCHAR(5)
,c2 VARCHAR(10)
,c3 VARCHAR(10)
,[date] DATE
);
INSERT INTO dbo.TBL_LABEL(c1,c2,c3,[date])
VALUES ('a' ,NULL ,NULL,'1/1/2014' )
,('d' ,'good' ,NULL,'1/2/2014' )
,('x' ,NULL ,NULL,'1/3/2014' )
,('z' ,NULL ,NULL,'1/4/2014' )
,('d' ,NULL ,NULL,'1/5/2014' )
,('e' ,'bad' ,NULL,'1/6/2014' )
,('d' ,NULL ,NULL,'1/7/2014' )
,('c' ,NULL ,NULL,'1/8/2014' )
,('a' ,NULL ,NULL,'1/9/2014' )
,('c' ,NULL ,NULL,'1/10/2014')
,('a' ,'bad' ,NULL,'1/11/2014')
,('c' ,NULL ,NULL,'1/12/2014')
,('g' ,'good' ,NULL,'1/13/2014')
,('bn',NULL ,NULL,'1/14/2014');
;WITH BASE_DATA AS
(
SELECT
TL.c1
,TL.c2
,TL.c3
,TL.[date]
,SUM(SIGN(ISNULL(LEN(TL.c2),0))) OVER
(
PARTITION BY (SELECT NULL)
ORDER BY TL.[date]
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) -SIGN(ISNULL(LEN(TL.c2),0)) AS GRP_VAL
,SIGN(ISNULL(LEN(TL.c2),0)) AS GRP_FLG
FROM dbo.TBL_LABEL TL
)
SELECT
BD.c1
,BD.c2
,CASE
WHEN BD.GRP_FLG = 1 THEN NULL
ELSE
CASE
WHEN MAX(BD.c2) OVER
(
PARTITION BY BD.GRP_VAL
) = 'good' THEN 1
WHEN MAX(BD.c2) OVER
(
PARTITION BY BD.GRP_VAL
) IS NULL THEN NULL
ELSE 0
END
END AS c3
,bd.[date]
FROM BASE_DATA BD;
Solution for SQL Server 2005/2008
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID('dbo.TBL_LABEL') IS NOT NULL DROP TABLE dbo.TBL_LABEL;
CREATE TABLE dbo.TBL_LABEL
(
c1 VARCHAR(5)
,c2 VARCHAR(10)
,c3 VARCHAR(10)
,[date] DATE
);
INSERT INTO dbo.TBL_LABEL(c1,c2,c3,[date])
VALUES ('a' ,NULL ,NULL,'1/1/2014' )
,('d' ,'good' ,NULL,'1/2/2014' )
,('x' ,NULL ,NULL,'1/3/2014' )
,('z' ,NULL ,NULL,'1/4/2014' )
,('d' ,NULL ,NULL,'1/5/2014' )
,('e' ,'bad' ,NULL,'1/6/2014' )
,('d' ,NULL ,NULL,'1/7/2014' )
,('c' ,NULL ,NULL,'1/8/2014' )
,('a' ,NULL ,NULL,'1/9/2014' )
,('c' ,NULL ,NULL,'1/10/2014')
,('a' ,'bad' ,NULL,'1/11/2014')
,('c' ,NULL ,NULL,'1/12/2014')
,('g' ,'good' ,NULL,'1/13/2014')
,('bn',NULL ,NULL,'1/14/2014');
;WITH BASE_DATA AS
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY (SELECT NULL)
ORDER BY TL.[date]
) AS TL_RID
,TL.c1
,TL.c2
,TL.c3
,TL.[date]
FROM dbo.TBL_LABEL TL
)
,VALUE_ROWS AS
(
SELECT
BD.TL_RID
,BD.c2
FROM BASE_DATA BD
WHERE BD.c2 IS NOT NULL
)
,MATCHED_SETS AS
(
SELECT
BD.c1
,BD.c2
,R.c2 AS XVAL
,R.TL_RID
,BD.TL_RID AS XL_RID
,ROW_NUMBER() OVER
(
PARTITION BY BD.TL_RID
ORDER BY R.TL_RID
) AS NM_RID
,BD.[date]
FROM VALUE_ROWS R
RIGHT OUTER JOIN BASE_DATA BD
ON R.TL_RID >= BD.TL_RID
)
SELECT
MS.c1
,MS.c2
,CASE
WHEN MS.TL_RID = MS.XL_RID THEN NULL
WHEN MS.XVAL = 'bad' THEN 0
WHEN MS.XVAL = 'good' THEN 1
END AS c3
,MS.[date]
FROM MATCHED_SETS MS
WHERE MS.NM_RID = 1;
Results
c1 c2 c3 date
----- ---------- ----------- ----------
a NULL 1 2014-01-01
d good NULL 2014-01-02
x NULL 0 2014-01-03
z NULL 0 2014-01-04
d NULL 0 2014-01-05
e bad NULL 2014-01-06
d NULL 0 2014-01-07
c NULL 0 2014-01-08
a NULL 0 2014-01-09
c NULL 0 2014-01-10
a bad NULL 2014-01-11
c NULL 1 2014-01-12
g good NULL 2014-01-13
bn NULL NULL 2014-01-14
Edit: Added solution for SQL Server 2005/2008