none
Populate NULL values from previous values

    질문

  • Hi All,

    Please need some out  of the box advice. I want to populate the NULL values and only those NULL's which come after a populated value.

    Please see the below example. I tried using the temp tables, doing self join , LEAD and LAG on the tables and the temp tables but with no luck.

    id credit_limit end_date required required
    86A6FB22E08A 125000 5/10/15 11:59 PM 125000 5/10/15 11:59 PM
    86A6FB22E08A NULL NULL 135000 4/10/15 11:59 PM
    86A6FB22E08A 135000 NULL 135000 4/10/15 11:59 PM
    86A6FB22E08A 125000 NULL 125000 4/10/15 11:59 PM
    86A6FB22E08A NULL 4/10/15 11:59 PM NULL 4/10/15 11:59 PM
    86A6FB22E08A NULL 3/4/15 11:59 PM NULL 3/4/15 11:59 PM
    86A6FB22E08A NULL 3/3/15 11:59 PM NULL 3/3/15 11:59 PM
    86A6FB22E08A NULL 3/3/14 11:59 PM NULL 3/3/14 11:59 PM
    86A6FB22E08A NULL NULL NULL NULL

    Thanks

    2018년 6월 14일 목요일 오후 7:18

답변

  • Did your the whole thing and compare your data?

    What you posted now is different that the one you posted earlier.

    CREATE TABLE mytable(
       id           VARCHAR(12) NOT NULL  
      ,startdate    VARCHAR(17) NOT NULL
      ,EndDate      VARCHAR(17) NOT NULL
      ,credit_limit INTEGER 
      ,end_date     VARCHAR(16)
      ,required     INTEGER 
      ,required2     VARCHAR(16)
    ) 
    INSERT INTO mytable(id,startdate,EndDate,credit_limit,end_date,required,required2) VALUES 
    ('86A6FB22E08A','5/8/15 12:00 AM','12/31/99 11:59 PM',125000,'5/10/15 11:59 PM',125000,'5/10/15 11:59 PM'),
    ('86A6FB22E08A','5/6/15 12:00 AM','5/7/15 11:59 PM',NULL,NULL,135000,'4/10/15 11:59 PM'),
    ('86A6FB22E08A','4/30/15 12:00 AM','5/5/15 11:59 PM',135000,NULL,135000,'4/10/15 11:59 PM'),
    ('86A6FB22E08A','4/11/15 12:00 AM','4/29/15 11:59 PM',125000,NULL,125000,'4/10/15 11:59 PM'),
    ('86A6FB22E08A','3/3/15 12:00 AM','4/10/15 11:59 PM',NULL,'4/10/15 11:59 PM',NULL,'4/10/15 11:59 PM'),
    ('86A6FB22E08A','1/6/15 12:00 AM','3/2/15 11:59 PM',NULL,'3/4/15 11:59 PM',NULL,'3/4/15 11:59 PM'),
    ('86A6FB22E08A','12/30/14 12:00 AM','1/5/15 11:59 PM',NULL,'3/3/15 11:59 PM',NULL,'3/3/15 11:59 PM'),
    ('86A6FB22E08A','12/23/14 12:00 AM','12/29/14 11:59 PM',NULL,'3/3/14 11:59 PM',NULL,'3/3/14 11:59 PM'),
    ('86A6FB22E08A','12/21/14 12:00 AM','12/27/14 11:59 PM',NULL,NULL,NULL,NULL) 
    
    select id   ,startdate,EndDate,credit_limit,end_date
     ,Cast(SUBSTRING(MAX(Cast(id  AS BINARY(8))+ CAST(Cast(startdate as date) AS BINARY(4)) +CAST(Cast(EndDate as date) AS BINARY(4)) +Cast( credit_limit  as  BINARY(8))    ) 
            OVER(  ORDER BY Cast(startdate as date) ROWS UNBOUNDED PRECEDING ), 17,8) as int) credit_limit2
     ,Cast(SUBSTRING(MAX( Cast(id  AS BINARY(8))+ CAST(Cast(startdate as date) AS BINARY(4)) +CAST(Cast(EndDate as date) AS BINARY(4)) +Cast(Cast(end_date as date) as  BINARY(8))    ) 
            OVER( ORDER BY Cast(startdate as date)   ROWS UNBOUNDED PRECEDING ), 17,8) as date) end_date2
    
     from mytable
      Order by Cast(startdate as date) DESC
    
    
     drop table mytable
     /*
     id	startdate	EndDate	credit_limit	end_date	credit_limit2	end_date2
    86A6FB22E08A	5/8/15 12:00 AM	12/31/99 11:59 PM	125000	5/10/15 11:59 PM	125000	2015-05-10
    86A6FB22E08A	5/6/15 12:00 AM	5/7/15 11:59 PM	NULL	NULL	135000	2015-04-10
    86A6FB22E08A	4/30/15 12:00 AM	5/5/15 11:59 PM	135000	NULL	135000	2015-04-10
    86A6FB22E08A	4/11/15 12:00 AM	4/29/15 11:59 PM	125000	NULL	125000	2015-04-10
    86A6FB22E08A	3/3/15 12:00 AM	4/10/15 11:59 PM	NULL	4/10/15 11:59 PM	NULL	2015-04-10
    86A6FB22E08A	1/6/15 12:00 AM	3/2/15 11:59 PM	NULL	3/4/15 11:59 PM	NULL	2015-03-04
    86A6FB22E08A	12/30/14 12:00 AM	1/5/15 11:59 PM	NULL	3/3/15 11:59 PM	NULL	2015-03-03
    86A6FB22E08A	12/23/14 12:00 AM	12/29/14 11:59 PM	NULL	3/3/14 11:59 PM	NULL	2014-03-03
    86A6FB22E08A	12/21/14 12:00 AM	12/27/14 11:59 PM	NULL	NULL	NULL	NULL
     */
    
    
     --From your posting
     /*
     
    id	startdate	EndDate	credit_limit	end_date	required	required
    86A6FB22E08A	5/8/15 12:00 AM	12/31/99 11:59 PM	125000	5/10/15 11:59 PM	125000	5/10/15 11:59 PM
    86A6FB22E08A	5/6/15 12:00 AM	5/7/15 11:59 PM	NULL	NULL	135000	4/10/15 11:59 PM
    86A6FB22E08A	4/30/15 12:00 AM	5/5/15 11:59 PM	135000	NULL	135000	4/10/15 11:59 PM
    86A6FB22E08A	4/11/15 12:00 AM	4/29/15 11:59 PM	125000	NULL	125000	4/10/15 11:59 PM
    86A6FB22E08A	3/3/15 12:00 AM	4/10/15 11:59 PM	NULL	4/10/15 11:59 PM	NULL	4/10/15 11:59 PM
    86A6FB22E08A	1/6/15 12:00 AM	3/2/15 11:59 PM	NULL	3/4/15 11:59 PM	NULL	3/4/15 11:59 PM
    86A6FB22E08A	12/30/14 12:00 AM	1/5/15 11:59 PM	NULL	3/3/15 11:59 PM	NULL	3/3/15 11:59 PM
    86A6FB22E08A	12/23/14 12:00 AM	12/29/14 11:59 PM	NULL	3/3/14 11:59 PM	NULL	3/3/14 11:59 PM
    86A6FB22E08A	12/21/14 12:00 AM	12/27/14 11:59 PM	NULL	NULL	NULL	NULL
     */

    • 답변으로 표시됨 LisaKruger 22시간 24분 전
    2018년 6월 18일 월요일 오후 1:32
    중재자

모든 응답

  • Can you post the sample table and data scripts for this ?

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    2018년 6월 14일 목요일 오후 7:22
  • Hi Lisa,

    How do you define "previous?" Is there an ORDER BY clause we can use? Otherwise, tables are not really sorted in any particular way by itself.

    Just my 2 cents...

    2018년 6월 14일 목요일 오후 7:36
  • thanks
    create table credits (id varchar,creditlimit money, end_date datetime)
    insert into credits values ('86A6FB22E08A',125000,'5/10/15 11:59 PM')
    insert into credits values('86A6FB22E08A',NULL,NULL)
    insert into credits values('86A6FB22E08A',135000,NULL)
    insert into credits values ('86A6FB22E08A',125000,NULL)
    insert into credits values('86A6FB22E08A',NULL,	'4/10/15 11:59 PM')
    insert into credits values ('86A6FB22E08A',NULL,	'3/4/15 11:59 PM')
    insert into credits values ('86A6FB22E08A',NULL,	'3/3/15 11:59 PM')
    insert into credits values ('86A6FB22E08A',NULL,	'3/3/14 11:59 PM')
    insert into credits values ('86A6FB22E08A',	NULL,	NULL)
    

    2018년 6월 14일 목요일 오후 7:37
  • Hi Lisa,

    How do you define "previous?" Is there an ORDER BY clause we can use? Otherwise, tables are not really sorted in any particular way by itself.

    Just my 2 cents...

    Lisa, This is valid point. How did you choose/define the order ? May that was the reason your earlier attempts didn't work

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    2018년 6월 14일 목요일 오후 7:40
  • you need to have a unique valued column to determine order here like may be primary key or audit column


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 6월 14일 목요일 오후 7:41
  • Thanks All

    here is what i have. What i am actually looking for is when either of the columns start with NULL i am not worried about that, but when they HIT  a value and go back to NULL, i want to replace that NULL with the previous value (the value before the NULL)

    id startdate EndDate credit_limit end_date required required
    86A6FB22E08A 5/8/15 12:00 AM 12/31/99 11:59 PM 125000 5/10/15 11:59 PM 125000 5/10/15 11:59 PM
    86A6FB22E08A 5/6/15 12:00 AM 5/7/15 11:59 PM NULL NULL 135000 4/10/15 11:59 PM
    86A6FB22E08A 4/30/15 12:00 AM 5/5/15 11:59 PM 135000 NULL 135000 4/10/15 11:59 PM
    86A6FB22E08A 4/11/15 12:00 AM 4/29/15 11:59 PM 125000 NULL 125000 4/10/15 11:59 PM
    86A6FB22E08A 3/3/15 12:00 AM 4/10/15 11:59 PM NULL 4/10/15 11:59 PM NULL 4/10/15 11:59 PM
    86A6FB22E08A 1/6/15 12:00 AM 3/2/15 11:59 PM NULL 3/4/15 11:59 PM NULL 3/4/15 11:59 PM
    86A6FB22E08A 12/30/14 12:00 AM 1/5/15 11:59 PM NULL 3/3/15 11:59 PM NULL 3/3/15 11:59 PM
    86A6FB22E08A 12/23/14 12:00 AM 12/29/14 11:59 PM NULL 3/3/14 11:59 PM NULL 3/3/14 11:59 PM
    86A6FB22E08A 12/21/14 12:00 AM 12/27/14 11:59 PM NULL NULL NULL NULL

    2018년 6월 14일 목요일 오후 8:05
  • CREATE TABLE mytable(
       id           VARCHAR(12) NOT NULL  
      ,startdate    VARCHAR(17) NOT NULL
      ,EndDate      VARCHAR(17) NOT NULL
      ,credit_limit INTEGER 
      ,end_date     VARCHAR(16)
      ,required     INTEGER 
      ,required2     VARCHAR(16)
    ) 
    INSERT INTO mytable(id,startdate,EndDate,credit_limit,end_date,required,required2) VALUES 
    ('86A6FB22E08A','5/8/15 12:00 AM','12/31/99 11:59 PM',125000,'5/10/15 11:59 PM',125000,'5/10/15 11:59 PM'),
    ('86A6FB22E08A','5/6/15 12:00 AM','5/7/15 11:59 PM',NULL,NULL,135000,'4/10/15 11:59 PM'),
    ('86A6FB22E08A','4/30/15 12:00 AM','5/5/15 11:59 PM',135000,NULL,135000,'4/10/15 11:59 PM'),
    ('86A6FB22E08A','4/11/15 12:00 AM','4/29/15 11:59 PM',125000,NULL,125000,'4/10/15 11:59 PM'),
    ('86A6FB22E08A','3/3/15 12:00 AM','4/10/15 11:59 PM',NULL,'4/10/15 11:59 PM',NULL,'4/10/15 11:59 PM'),
    ('86A6FB22E08A','1/6/15 12:00 AM','3/2/15 11:59 PM',NULL,'3/4/15 11:59 PM',NULL,'3/4/15 11:59 PM'),
    ('86A6FB22E08A','12/30/14 12:00 AM','1/5/15 11:59 PM',NULL,'3/3/15 11:59 PM',NULL,'3/3/15 11:59 PM'),
    ('86A6FB22E08A','12/23/14 12:00 AM','12/29/14 11:59 PM',NULL,'3/3/14 11:59 PM',NULL,'3/3/14 11:59 PM'),
    ('86A6FB22E08A','12/21/14 12:00 AM','12/27/14 11:59 PM',NULL,NULL,NULL,NULL) 
    
    select id   ,startdate,EndDate,credit_limit,end_date
     ,Cast(SUBSTRING(MAX(Cast(id  AS BINARY(8))+ CAST(Cast(startdate as date) AS BINARY(4)) +CAST(Cast(EndDate as date) AS BINARY(4)) +Cast( credit_limit  as  BINARY(8))    ) 
            OVER(  ORDER BY Cast(startdate as date) ROWS UNBOUNDED PRECEDING ), 17,8) as int) credit_limit2
     ,Cast(SUBSTRING(MAX( Cast(id  AS BINARY(8))+ CAST(Cast(startdate as date) AS BINARY(4)) +CAST(Cast(EndDate as date) AS BINARY(4)) +Cast(Cast(end_date as date) as  BINARY(8))    ) 
            OVER( ORDER BY Cast(startdate as date)   ROWS UNBOUNDED PRECEDING ), 17,8) as date) end_date2
    
     from mytable
      Order by Cast(startdate as date) DESC
    
    
     drop table mytable

    2018년 6월 14일 목요일 오후 8:38
    중재자
  • looks like this to me

    SELECT t.Id,t.startdate,t.EndDate,t.credit_limit,t.end_date,COALESCE(t.credit_limit,t1.credit_limit) AS requireddate,COALESCE(t.end_date,t2.end_date) AS requiredenddate
    FROM Yourtable t
    OUTER APPLY
    (
    SELECT TOP 1 credit_limit
    FROM YourTable
    WHERE STartDate < t.StartDate
    AND Id = t.Id
    AND credit_limit IS NOT NULL
    ORDER BY StartDate DESC )t1 OUTER APPLY ( SELECT TOP 1 end_date FROM YourTable WHERE STartDate < t.StartDate AND Id = t.Id AND end_date IS NOT NULL
    ORDER BY StartDate DESC )t2


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 6월 14일 목요일 오후 8:45
  • Thanks Jingyan Li, but this is what i am getting. I am not getting the time stamp and the credit amount is not right

     credit_limit2  end_date2 startdate enddate id credit_limit start_date end_date
     $ 1,000,000,000.00 5/10/2015 5/8/15 12:00 AM 12/31/99 11:59 PM 318B7204-A073-49E4-903C-86A6FB22E08A 125000 1/1/15 12:00 AM 5/10/15 11:59 PM
     $ 1,000,000,000.00 5/10/2015 5/6/15 12:00 AM 5/7/15 11:59 PM 318B7204-A073-49E4-903C-86A6FB22E08A NULL NULL NULL
     $ 1,000,000,000.00 5/10/2015 4/30/15 12:00 AM 5/5/15 11:59 PM 318B7204-A073-49E4-903C-86A6FB22E08A 135000 NULL NULL
     $ 1,250,000,000.00 4/10/2015 4/11/15 12:00 AM 4/29/15 11:59 PM 318B7204-A073-49E4-903C-86A6FB22E08A 125000 NULL NULL
     NULL  4/10/2015 3/3/15 12:00 AM 4/10/15 11:59 PM 318B7204-A073-49E4-903C-86A6FB22E08A NULL NULL 4/10/15 11:59 PM
     NULL  3/4/2015 1/6/15 12:00 AM 3/2/15 11:59 PM 318B7204-A073-49E4-903C-86A6FB22E08A NULL NULL 3/4/15 11:59 PM
     NULL  3/3/2015 12/30/14 12:00 AM 1/5/15 11:59 PM 318B7204-A073-49E4-903C-86A6FB22E08A NULL NULL 3/3/15 11:59 PM
     NULL  3/3/2014 12/23/14 12:00 AM 12/29/14 11:59 PM 318B7204-A073-49E4-903C-86A6FB22E08A NULL NULL 3/3/14 11:59 PM
    2018년 6월 18일 월요일 오후 12:42
  • Thanks Jingyan Li, but this is what i am getting. I am not getting the time stamp and the credit amount is not right

     credit_limit2  end_date2 startdate enddate id credit_limit start_date end_date
     $ 1,000,000,000.00 5/10/2015 5/8/15 12:00 AM 12/31/99 11:59 PM 318B7204-A073-49E4-903C-86A6FB22E08A 125000 1/1/15 12:00 AM 5/10/15 11:59 PM
     $ 1,000,000,000.00 5/10/2015 5/6/15 12:00 AM 5/7/15 11:59 PM 318B7204-A073-49E4-903C-86A6FB22E08A NULL NULL NULL
     $ 1,000,000,000.00 5/10/2015 4/30/15 12:00 AM 5/5/15 11:59 PM 318B7204-A073-49E4-903C-86A6FB22E08A 135000 NULL NULL
     $ 1,250,000,000.00 4/10/2015 4/11/15 12:00 AM 4/29/15 11:59 PM 318B7204-A073-49E4-903C-86A6FB22E08A 125000 NULL NULL
     NULL  4/10/2015 3/3/15 12:00 AM 4/10/15 11:59 PM 318B7204-A073-49E4-903C-86A6FB22E08A NULL NULL 4/10/15 11:59 PM
     NULL  3/4/2015 1/6/15 12:00 AM 3/2/15 11:59 PM 318B7204-A073-49E4-903C-86A6FB22E08A NULL NULL 3/4/15 11:59 PM
     NULL  3/3/2015 12/30/14 12:00 AM 1/5/15 11:59 PM 318B7204-A073-49E4-903C-86A6FB22E08A NULL NULL 3/3/15 11:59 PM
     NULL  3/3/2014 12/23/14 12:00 AM 12/29/14 11:59 PM 318B7204-A073-49E4-903C-86A6FB22E08A NULL NULL 3/3/14 11:59 PM

    As per the data you posted, my suggestion seems to work fine

    CREATE TABLE Yourtable(
       id           VARCHAR(12) NOT NULL  
      ,startdate    VARCHAR(17) NOT NULL
      ,EndDate      VARCHAR(17) NOT NULL
      ,credit_limit INTEGER 
      ,end_date     VARCHAR(16)
      ,required     INTEGER 
      ,required2     VARCHAR(16)
    ) 
    INSERT INTO Yourtable(id,startdate,EndDate,credit_limit,end_date,required,required2) VALUES 
    ('86A6FB22E08A','5/8/15 12:00 AM','12/31/99 11:59 PM',125000,'5/10/15 11:59 PM',125000,'5/10/15 11:59 PM'),
    ('86A6FB22E08A','5/6/15 12:00 AM','5/7/15 11:59 PM',NULL,NULL,135000,'4/10/15 11:59 PM'),
    ('86A6FB22E08A','4/30/15 12:00 AM','5/5/15 11:59 PM',135000,NULL,135000,'4/10/15 11:59 PM'),
    ('86A6FB22E08A','4/11/15 12:00 AM','4/29/15 11:59 PM',125000,NULL,125000,'4/10/15 11:59 PM'),
    ('86A6FB22E08A','3/3/15 12:00 AM','4/10/15 11:59 PM',NULL,'4/10/15 11:59 PM',NULL,'4/10/15 11:59 PM'),
    ('86A6FB22E08A','1/6/15 12:00 AM','3/2/15 11:59 PM',NULL,'3/4/15 11:59 PM',NULL,'3/4/15 11:59 PM'),
    ('86A6FB22E08A','12/30/14 12:00 AM','1/5/15 11:59 PM',NULL,'3/3/15 11:59 PM',NULL,'3/3/15 11:59 PM'),
    ('86A6FB22E08A','12/23/14 12:00 AM','12/29/14 11:59 PM',NULL,'3/3/14 11:59 PM',NULL,'3/3/14 11:59 PM'),
    ('86A6FB22E08A','12/21/14 12:00 AM','12/27/14 11:59 PM',NULL,NULL,NULL,NULL) 
    
    
    SELECT t.Id,t.startdate,t.EndDate,t.credit_limit,t.end_date,COALESCE(t.credit_limit,t1.credit_limit) AS requiredcredit,COALESCE(t.end_date,t2.end_date) AS requiredenddate
    FROM Yourtable t
    OUTER APPLY
    (
    SELECT TOP 1 credit_limit
    FROM YourTable
    WHERE STartDate < t.StartDate
    AND Id = t.Id
    AND credit_limit IS NOT NULL
    ORDER BY StartDate DESC
    )t1
    OUTER APPLY
    (
    SELECT TOP 1 end_date
    FROM YourTable
    WHERE STartDate < t.StartDate
    AND Id = t.Id
    AND end_date IS NOT NULL
    ORDER BY StartDate DESC
    )t2
    
    
    /*
    Output
    -------------------------------------------------------
    Id	startdate	EndDate	credit_limit	end_date	requireddate	requiredenddate
    ---------------------------------------------------------------------------------------------
    86A6FB22E08A	5/8/15 12:00 AM	12/31/99 11:59 PM	125000	5/10/15 11:59 PM	125000	5/10/15 11:59 PM
    86A6FB22E08A	5/6/15 12:00 AM	5/7/15 11:59 PM	NULL	NULL	135000	4/10/15 11:59 PM
    86A6FB22E08A	4/30/15 12:00 AM	5/5/15 11:59 PM	135000	NULL	135000	4/10/15 11:59 PM
    86A6FB22E08A	4/11/15 12:00 AM	4/29/15 11:59 PM	125000	NULL	125000	4/10/15 11:59 PM
    86A6FB22E08A	3/3/15 12:00 AM	4/10/15 11:59 PM	NULL	4/10/15 11:59 PM	NULL	4/10/15 11:59 PM
    86A6FB22E08A	1/6/15 12:00 AM	3/2/15 11:59 PM	NULL	3/4/15 11:59 PM	NULL	3/4/15 11:59 PM
    86A6FB22E08A	12/30/14 12:00 AM	1/5/15 11:59 PM	NULL	3/3/15 11:59 PM	NULL	3/3/15 11:59 PM
    86A6FB22E08A	12/23/14 12:00 AM	12/29/14 11:59 PM	NULL	3/3/14 11:59 PM	NULL	3/3/14 11:59 PM
    86A6FB22E08A	12/21/14 12:00 AM	12/27/14 11:59 PM	NULL	NULL	NULL	3/4/15 11:59 PM
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    */


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 6월 18일 월요일 오후 12:50
  • Did your the whole thing and compare your data?

    What you posted now is different that the one you posted earlier.

    CREATE TABLE mytable(
       id           VARCHAR(12) NOT NULL  
      ,startdate    VARCHAR(17) NOT NULL
      ,EndDate      VARCHAR(17) NOT NULL
      ,credit_limit INTEGER 
      ,end_date     VARCHAR(16)
      ,required     INTEGER 
      ,required2     VARCHAR(16)
    ) 
    INSERT INTO mytable(id,startdate,EndDate,credit_limit,end_date,required,required2) VALUES 
    ('86A6FB22E08A','5/8/15 12:00 AM','12/31/99 11:59 PM',125000,'5/10/15 11:59 PM',125000,'5/10/15 11:59 PM'),
    ('86A6FB22E08A','5/6/15 12:00 AM','5/7/15 11:59 PM',NULL,NULL,135000,'4/10/15 11:59 PM'),
    ('86A6FB22E08A','4/30/15 12:00 AM','5/5/15 11:59 PM',135000,NULL,135000,'4/10/15 11:59 PM'),
    ('86A6FB22E08A','4/11/15 12:00 AM','4/29/15 11:59 PM',125000,NULL,125000,'4/10/15 11:59 PM'),
    ('86A6FB22E08A','3/3/15 12:00 AM','4/10/15 11:59 PM',NULL,'4/10/15 11:59 PM',NULL,'4/10/15 11:59 PM'),
    ('86A6FB22E08A','1/6/15 12:00 AM','3/2/15 11:59 PM',NULL,'3/4/15 11:59 PM',NULL,'3/4/15 11:59 PM'),
    ('86A6FB22E08A','12/30/14 12:00 AM','1/5/15 11:59 PM',NULL,'3/3/15 11:59 PM',NULL,'3/3/15 11:59 PM'),
    ('86A6FB22E08A','12/23/14 12:00 AM','12/29/14 11:59 PM',NULL,'3/3/14 11:59 PM',NULL,'3/3/14 11:59 PM'),
    ('86A6FB22E08A','12/21/14 12:00 AM','12/27/14 11:59 PM',NULL,NULL,NULL,NULL) 
    
    select id   ,startdate,EndDate,credit_limit,end_date
     ,Cast(SUBSTRING(MAX(Cast(id  AS BINARY(8))+ CAST(Cast(startdate as date) AS BINARY(4)) +CAST(Cast(EndDate as date) AS BINARY(4)) +Cast( credit_limit  as  BINARY(8))    ) 
            OVER(  ORDER BY Cast(startdate as date) ROWS UNBOUNDED PRECEDING ), 17,8) as int) credit_limit2
     ,Cast(SUBSTRING(MAX( Cast(id  AS BINARY(8))+ CAST(Cast(startdate as date) AS BINARY(4)) +CAST(Cast(EndDate as date) AS BINARY(4)) +Cast(Cast(end_date as date) as  BINARY(8))    ) 
            OVER( ORDER BY Cast(startdate as date)   ROWS UNBOUNDED PRECEDING ), 17,8) as date) end_date2
    
     from mytable
      Order by Cast(startdate as date) DESC
    
    
     drop table mytable
     /*
     id	startdate	EndDate	credit_limit	end_date	credit_limit2	end_date2
    86A6FB22E08A	5/8/15 12:00 AM	12/31/99 11:59 PM	125000	5/10/15 11:59 PM	125000	2015-05-10
    86A6FB22E08A	5/6/15 12:00 AM	5/7/15 11:59 PM	NULL	NULL	135000	2015-04-10
    86A6FB22E08A	4/30/15 12:00 AM	5/5/15 11:59 PM	135000	NULL	135000	2015-04-10
    86A6FB22E08A	4/11/15 12:00 AM	4/29/15 11:59 PM	125000	NULL	125000	2015-04-10
    86A6FB22E08A	3/3/15 12:00 AM	4/10/15 11:59 PM	NULL	4/10/15 11:59 PM	NULL	2015-04-10
    86A6FB22E08A	1/6/15 12:00 AM	3/2/15 11:59 PM	NULL	3/4/15 11:59 PM	NULL	2015-03-04
    86A6FB22E08A	12/30/14 12:00 AM	1/5/15 11:59 PM	NULL	3/3/15 11:59 PM	NULL	2015-03-03
    86A6FB22E08A	12/23/14 12:00 AM	12/29/14 11:59 PM	NULL	3/3/14 11:59 PM	NULL	2014-03-03
    86A6FB22E08A	12/21/14 12:00 AM	12/27/14 11:59 PM	NULL	NULL	NULL	NULL
     */
    
    
     --From your posting
     /*
     
    id	startdate	EndDate	credit_limit	end_date	required	required
    86A6FB22E08A	5/8/15 12:00 AM	12/31/99 11:59 PM	125000	5/10/15 11:59 PM	125000	5/10/15 11:59 PM
    86A6FB22E08A	5/6/15 12:00 AM	5/7/15 11:59 PM	NULL	NULL	135000	4/10/15 11:59 PM
    86A6FB22E08A	4/30/15 12:00 AM	5/5/15 11:59 PM	135000	NULL	135000	4/10/15 11:59 PM
    86A6FB22E08A	4/11/15 12:00 AM	4/29/15 11:59 PM	125000	NULL	125000	4/10/15 11:59 PM
    86A6FB22E08A	3/3/15 12:00 AM	4/10/15 11:59 PM	NULL	4/10/15 11:59 PM	NULL	4/10/15 11:59 PM
    86A6FB22E08A	1/6/15 12:00 AM	3/2/15 11:59 PM	NULL	3/4/15 11:59 PM	NULL	3/4/15 11:59 PM
    86A6FB22E08A	12/30/14 12:00 AM	1/5/15 11:59 PM	NULL	3/3/15 11:59 PM	NULL	3/3/15 11:59 PM
    86A6FB22E08A	12/23/14 12:00 AM	12/29/14 11:59 PM	NULL	3/3/14 11:59 PM	NULL	3/3/14 11:59 PM
    86A6FB22E08A	12/21/14 12:00 AM	12/27/14 11:59 PM	NULL	NULL	NULL	NULL
     */

    • 답변으로 표시됨 LisaKruger 22시간 24분 전
    2018년 6월 18일 월요일 오후 1:32
    중재자
  • >> Please need some out of the box advice. I want to populate the NULL values [sic] and only those NULL's which come after [sic] a populated value. <<

    NULLs are not values; they are the absence of the value, and you need to make some rules about how you want to handle them. This is actually a really tricky design problem

    The rows in the table have no ordering by definition. This is what Dr. Codd called “the information principle”; it says it all relationships (like an ordering) have to be defined by scalar values in the columns of the rows of a table. 

    In order to be a valid table. We need to have a key. One of your rows has too many nullable columns, with the exception of your improperly named “id” column. In formal logic. There is a principal called “the law of identity”, which states that to be is to be something in particular and be anything in general, is to be nothing at all. This principle is embedded in the ISO 11179 naming standards, which use “_id” as what is called an attribute property – “<something in particular>_id” would be the preferred name.

    You had to columns named “required”, which is illegal, a column name must be unique within a table. Actually it should be unique within the schema.

    In short, what you posted makes no sense. Then on top of all of that you got the date display format wrong. 

    CREATE TABLE Foobar
    (generic_id CHAR(12) N0T NULL, --– needs a real name!
     credit_limit DECIMAL (12,2),
     something_end_date DATE, --- end of what? When did start?
     foo DECIMAL (12,2), --- real name?
     bar DECIMAL (12,2), --- real name?
     floob_date DATE); --- real name?
    PRIMARY KEY (????)); ---impossible!

    INSERT INTO Foobar ---not a table
    VALUES
    ('86A6FB22E08A', 125000.00 ‘2015-05-10’, 125000.00, ‘2015-05-10'),
    ('86A6FB22E08A', NULL, NULL, 135000 ‘2015-04-10'),
    ('86A6FB22E08A', 135000.00, NULL, 135000.00 '2015-10-15'),
    ('86A6FB22E08A', 125000.00, NULL, 125000.00 '2015-10-15'),
    ('86A6FB22E08A', NULL, '2015-10-15', NULL, '2015-10-15'),
    ('86A6FB22E08A', NULL, '2015-03-04',NULL, '2015-03-04'),
    ('86A6FB22E08A', NULL, '2015-03-03', NULL, '2015-03-03'),
    ('86A6FB22E08A', NULL, '2014-03-03' NULL, '2014-03-03'),
    ('86A6FB22E08A', NULL, NULL, NULL, NULL); 

    I seriously think you need to sit down and read an introductory book on RDBMS. We can spend a lot of time writing kludges for you, but that is not your real problem. You’re literally using SQL as if this was the 1960s and you’re still working with punch cards. Back in those days we used to do this in COBOL, a language which supports repressing or carrying down previous physical line data to the next physical line on outputs. These concepts do not exist in SQL. 

    Would you like to try again?



    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    2018년 6월 18일 월요일 오후 11:12