December 11, 2014 at 1:35 pm
Initial Dataset:
c1,c2,c3,date
a,,,1/1/2014
d,good,,1/2/2014
x,,,1/3/2014
z,,,1/4/2014
d,,,1/5/2014
e,bad,,1/6/2014
d,,,1/7/2014
c,,,1/8/2014
a,,,1/9/2014
c,,,1/10/2014
a,bad,,1/11/2014
c,,,1/12/2014
g,good,,1/13/2014
bn,,,1/14/2014
Desired outcome:
c1,c2,c3,date
a,,1,1/1/2014
d,good,,1/2/2014
x,,0,1/3/2014
z,,0,1/4/2014
d,,0,1/5/2014
e,bad,,1/6/2014
d,,0,1/7/2014
c,,0,1/8/2014
a,,0,1/9/2014
c,,0,1/10/2014
a,bad,,1/11/2014
c,,1,1/12/2014
g,good,,1/13/2014
bn,,,1/14/2014
December 12, 2014 at 12:57 am
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
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy