lable records based on next non null value of a column

• Mark Lancour

  Newbie

  Points: 5

  More actions

  December 11, 2014 at 1:35 pm

  #99072

  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

• Eirikur Eiriksson

  SSC Guru

  Points: 182947

  More actions

  December 12, 2014 at 12:57 am

  #1764290

  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