Update NULL values - SQL Help

• kishoremania

  Ten Centuries

  Points: 1059

  More actions

  August 2, 2017 at 11:47 am

  #327837

  Hello Everyone, 

  Need some help in updating one column, I come up with some sample data.

  Create Table #ACC (Flag Varchar(1), Period Varchar(6))

  Insert Into #ACC
  Values ('N', NULL),
  ('N', NULL),
  ('N', NULL),
  ('N', NULL),
  ('Y', '201812'),
  ('N', NULL),
  ('N', NULL),
  ('N', NULL),
  ('N', NULL),
  ('Y', '201901'),
  ('N', NULL),
  ('N', NULL),
  ('N', NULL),
  ('N', NULL),
  ('Y', '201902')

  Looking for below output
  

  Looking for a sql logic (update/select script), whenever there is a flag 'Y' to 'Y', I need the date to be updated like I showed in the above picture. Please let me know if it doesn't make sense.

  Thanks for help in advance.

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  August 2, 2017 at 12:11 pm

  #1953830

  kishoremania - Wednesday, August 2, 2017 11:47 AM

  Hello Everyone, 

  Need some help in updating one column, I come up with some sample data.

  Create Table #ACC (Flag Varchar(1), Period Varchar(6))

  Insert Into #ACC
  Values ('N', NULL),
  ('N', NULL),
  ('N', NULL),
  ('N', NULL),
  ('Y', '201812'),
  ('N', NULL),
  ('N', NULL),
  ('N', NULL),
  ('N', NULL),
  ('Y', '201901'),
  ('N', NULL),
  ('N', NULL),
  ('N', NULL),
  ('N', NULL),
  ('Y', '201902')

  Looking for below output
  

  Looking for a sql logic (update/select script), whenever there is a flag 'Y' to 'Y', I need the date to be updated like I showed in the above picture. Please let me know if it doesn't make sense.

  Thanks for help in advance.

  so...how are you ordering the data?   dont see any columns that can be used for order?

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  August 2, 2017 at 12:15 pm

  #1953831

  kishoremania - Wednesday, August 2, 2017 11:47 AM

  Hello Everyone, 

  Need some help in updating one column, I come up with some sample data.

  Create Table #ACC (Flag Varchar(1), Period Varchar(6))

  Insert Into #ACC
  Values ('N', NULL),
  ('N', NULL),
  ('N', NULL),
  ('N', NULL),
  ('Y', '201812'),
  ('N', NULL),
  ('N', NULL),
  ('N', NULL),
  ('N', NULL),
  ('Y', '201901'),
  ('N', NULL),
  ('N', NULL),
  ('N', NULL),
  ('N', NULL),
  ('Y', '201902')

  Looking for below output
  

  Looking for a sql logic (update/select script), whenever there is a flag 'Y' to 'Y', I need the date to be updated like I showed in the above picture. Please let me know if it doesn't make sense.

  Thanks for help in advance.

  It makes sense, but it can't be done as it is now. There's no order inside a table, you need to have a column to define the order of the rows. The way you presented the sample data is incomplete to fulfill this requirement.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• kishoremania

  Ten Centuries

  Points: 1059

  More actions

  August 2, 2017 at 12:43 pm

  #1953836

  Thanks for your reply guys.

  That's the tough part, I have barely one column and generating rest of the columns using it.

  I generated flag based on Date column (flag enables every second wednesday of fiscal year)

  Not sure the below script helps here (from real data), if not I think it's not possible in sql.

  Create Table #ACT (Flag Varchar(1), Period Varchar(6), Date Varchar(20))
  Insert Into #ACT
  Values
  ('N',    NULL,    '09/29/2018'),
  ('N',    NULL,    '09/30/2018'),
  ('N',    NULL,    '10/01/2018'),
  ('N',    NULL,    '10/02/2018'),
  ('N',    NULL,    '10/03/2018'),
  ('N',    NULL,    '10/04/2018'),
  ('N',    NULL,    '10/05/2018'),
  ('N',    NULL,    '10/06/2018'),
  ('N',    NULL,    '10/07/2018'),
  ('N',    NULL,    '10/08/2018'),
  ('N',    NULL,    '10/09/2018'),
  ('Y',    '201812',    '10/10/2018'),
  ('N',    NULL,    '10/11/2018'),
  ('N',    NULL,    '10/12/2018'),
  ('N',    NULL,    '10/13/2018'),
  ('N',    NULL,    '10/14/2018'),
  ('N',    NULL,    '10/15/2018'),
  ('N',    NULL,    '10/16/2018'),
  ('N',    NULL,    '10/17/2018'),
  ('N',    NULL,    '10/18/2018'),
  ('N',    NULL,    '10/19/2018'),
  ('N',    NULL,    '10/20/2018'),
  ('N',    NULL,    '10/21/2018'),
  ('N',    NULL,    '10/22/2018'),
  ('N',    NULL,    '10/23/2018'),
  ('N',    NULL,    '10/24/2018'),
  ('N',    NULL,    '10/25/2018'),
  ('N',    NULL,    '10/26/2018'),
  ('N',    NULL,    '10/27/2018'),
  ('N',    NULL,    '10/28/2018'),
  ('N',    NULL,    '10/29/2018'),
  ('N',    NULL,    '10/30/2018'),
  ('N',    NULL,    '10/31/2018'),
  ('N',    NULL,    '11/01/2018'),
  ('N',    NULL,    '11/02/2018'),
  ('N',    NULL,    '11/03/2018'),
  ('N',    NULL,    '11/04/2018'),
  ('N',    NULL,    '11/05/2018'),
  ('N',    NULL,    '11/06/2018'),
  ('Y',    '201901',    '11/07/2018'),
  ('N',    NULL,    '11/08/2018'),
  ('N',    NULL,    '11/09/2018'),
  ('N',    NULL,    '11/10/2018'),
  ('N',    NULL,    '11/11/2018'),
  ('N',    NULL,    '11/12/2018'),
  ('N',    NULL,    '11/13/2018'),
  ('N',    NULL,    '11/14/2018'),
  ('N',    NULL,    '11/15/2018'),
  ('N',    NULL,    '11/16/2018'),
  ('N',    NULL,    '11/17/2018'),
  ('N',    NULL,    '11/18/2018'),
  ('N',    NULL,    '11/19/2018'),
  ('N',    NULL,    '11/20/2018'),
  ('N',    NULL,    '11/21/2018'),
  ('N',    NULL,    '11/22/2018'),
  ('N',    NULL,    '11/23/2018'),
  ('N',    NULL,    '11/24/2018'),
  ('N',    NULL,    '11/25/2018'),
  ('N',    NULL,    '11/26/2018'),
  ('N',    NULL,    '11/27/2018'),
  ('N',    NULL,    '11/28/2018'),
  ('N',    NULL,    '11/29/2018'),
  ('N',    NULL,    '11/30/2018'),
  ('N',    NULL,    '12/01/2018'),
  ('N',    NULL,    '12/02/2018'),
  ('N',    NULL,    '12/03/2018'),
  ('N',    NULL,    '12/04/2018'),
  ('Y',    '201902',    '12/05/2018')

  Select * from #ACT

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  August 2, 2017 at 12:58 pm

  #1953838

  Here's an option on how to do it. I'm not sure if it's the optimal way.
  
--Using SELECT
SELECT *
FROM #ACT o
OUTER APPLY (SELECT TOP 1 Period
    FROM #ACT i
    WHERE i.Date >= o.Date
    AND i.Flag = 'Y'
    ORDER BY i.Date) x ;

  --Using UPDATE
UPDATE o SET
  Period = x.Period
FROM #ACT o
OUTER APPLY (SELECT TOP 1 Period
    FROM #ACT i
    WHERE i.Date >= o.Date
    AND i.Flag = 'Y'
    ORDER BY i.Date) x ;

  SELECT *
FROM #ACT
ORDER BY Date;


  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Jacob Wilkins

  One Orange Chip

  Points: 27976

  More actions

  August 2, 2017 at 1:53 pm

  #1953845

  Here's another sort of approach. It relies on some assumptions about your data (all and only rows with 'Y' for flag have a non-null value for Period; the value for Period on rows with Flag='Y' increases as Date increases; there won't be multiple rows for the same value of Date), but performs pretty nicely as a SELECT.

  SELECT *, NewPeriod=MIN(Period) OVER (ORDER BY Date ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM #Act
ORDER BY Date;

  Cheers!

• kishoremania

  Ten Centuries

  Points: 1059

  More actions

  August 2, 2017 at 3:21 pm

  #1953858

  Luis Cazares - Wednesday, August 2, 2017 12:58 PM

  Here's an option on how to do it. I'm not sure if it's the optimal way.
  
  --Using SELECT
  SELECT *
  FROM #ACT o
  OUTER APPLY (SELECT TOP 1 Period
      FROM #ACT i
      WHERE i.Date >= o.Date
      AND i.Flag = 'Y'
      ORDER BY i.Date) x ;

  --Using UPDATE
  UPDATE o SET
    Period = x.Period
  FROM #ACT o
  OUTER APPLY (SELECT TOP 1 Period
      FROM #ACT i
      WHERE i.Date >= o.Date
      AND i.Flag = 'Y'
      ORDER BY i.Date) x ;

  SELECT *
FROM #ACT
ORDER BY Date;


  Amazing! This is what I am looking for.
  As this process I am trying to automate runs yearly, so this is more than enough.

  Really appreciate your help.

• Jason A. Long

  SSC-Insane

  Points: 23786

  More actions

  August 3, 2017 at 3:46 pm

  #1954053

  Here's a solution that only takes a single pass over the data an. I wish I could take credit for this solution but this is all Itzik... The Last non NULL Puzzle

  SELECT
    a.Flag,
    Period = CAST(SUBSTRING(MIN(CAST(a.Date AS BINARY(4)) + CAST(a.Period AS BINARY(6))) OVER (ORDER BY a.Date ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), 5, 6) AS VARCHAR(6)),
    a.Date
FROM
    #ACT a
ORDER BY a.Date;

  HTH,
  Jason

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  August 4, 2017 at 8:04 am

  #1954149

  Jason A. Long - Thursday, August 3, 2017 3:46 PM

  Here's a solution that only takes a single pass over the data an. I wish I could take credit for this solution but this is all Itzik... The Last non NULL Puzzle

  SELECT
    a.Flag,
    Period = CAST(SUBSTRING(MIN(CAST(a.Date AS BINARY(4)) + CAST(a.Period AS BINARY(6))) OVER (ORDER BY a.Date ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), 5, 6) AS VARCHAR(6)),
    a.Date
FROM
    #ACT a
ORDER BY a.Date;

  HTH,
  Jason

  I think that this is a bit overly complicated for this particular purpose.  It's necessary when the value you are interested in is not monotonic with respect to the ORDER BY expression, but it appears that the value is monotonic in this case, so a simple MIN() should work.

  SELECT
  a.Flag,
  Period = MIN(a.Period) OVER (ORDER BY a.Date ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
  a.Date
FROM
  #ACT a
ORDER BY a.Date;

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA