locked
SQL to copy data from Previous non null rows RRS feed

  • Question

  • I am using SQL server 2008. I have a query in a stored procedure which returns data like below

    Prod_Order       Datetime                                                     DataValue

    106                 2015-02-20 09:00:00.000                          1010.3499756

    106                 2015-02-20 10:00:00.000                          Null

    106                 2015-02-20 11:00:00.000                          3113.1999512

    106                 2015-02-20 12:00:00.000                          Null

    106                 2015-02-20 13:00:00.000                          Null

    106                 2015-02-20 14:00:00.000                          Null

    106                 2015-02-20 15:00:00.000                          Null

    106                 2015-02-20 16:00:00.000                          5219.0998536

    Now my requirement is fill all the 'Null' datavalues with previous non null like below. I am not making any changes to the table data. I only need a select statement from the table with output below. Thanks in advance.

    Prod_Order       Datetime                                                     DataValue

    106                 2015-02-20 09:00:00.000                          1010.3499756

    106                 2015-02-20 10:00:00.000                          1010.3499756

    106                 2015-02-20 11:00:00.000                          3113.1999512

    106                 2015-02-20 12:00:00.000                          3113.1999512

    106                 2015-02-20 13:00:00.000                          3113.1999512

    106                 2015-02-20 14:00:00.000                          3113.1999512

    106                 2015-02-20 15:00:00.000                          3113.1999512

    106                 2015-02-20 16:00:00.000                          5219.0998536

    select Prod_Order,    Datetime  ,  DataValue from table1
    CREATE  TABLE Table1 (Prod_Order int, Datettime datetime2, Datavalue Varchar (200) )
    
    INSERT INTO Table1 values('106', '2015-02-20 09:00:00.000' , 1010.3499756)
    INSERT INTO Table1 values('106', '2015-02-20 10:00:00.000',    '')
    INSERT INTO Table1 values('106', '2015-02-20 11:00:00.000' ,  3113.1999512)
    INSERT INTO Table1 values('106', '2015-02-20 12:00:00.000' ,            '')
    INSERT INTO Table1 values('106', '2015-02-20 13:00:00.000' ,            '')
    INSERT INTO Table1 values('106', '2015-02-20 14:00:00.000' ,            '')
    INSERT INTO Table1 values('106', '2015-02-20 15:00:00.000' ,            '')
    INSERT INTO Table1 values('106', '2015-02-20 16:00:00.000' ,  5219.0998536)



    svk




    • Edited by czarvk Wednesday, February 25, 2015 11:20 PM
    Wednesday, February 25, 2015 11:06 PM

Answers

  • CREATE  TABLE Table1 (Prod_Order int, Datettime datetime, Datavalue Decimal(18,7))
    
    INSERT INTO Table1 values('106', '2015-02-20 09:00:00.000' , 1010.3499756)
    INSERT INTO Table1 values('106', '2015-02-20 10:00:00.000',    null)
    INSERT INTO Table1 values('106', '2015-02-20 11:00:00.000' ,  3113.1999512)
    INSERT INTO Table1 values('106', '2015-02-20 12:00:00.000' ,           null)
    INSERT INTO Table1 values('106', '2015-02-20 13:00:00.000' ,           null)
    INSERT INTO Table1 values('106', '2015-02-20 14:00:00.000' ,            null)
    INSERT INTO Table1 values('106', '2015-02-20 15:00:00.000' ,           null)
    INSERT INTO Table1 values('106', '2015-02-20 16:00:00.000' ,  5219.0998536)
    
    
    --select * from Table1
     
    SELECT Prod_Order, /*Datavalue, */
    Datettime,
    CAST(SUBSTRING(MAX( CAST(Datettime AS BINARY(32)) + CAST(Datavalue AS BINARY(12)) )
    OVER( ORDER BY Datettime ASC ROWS UNBOUNDED PRECEDING ),33,12) AS Decimal(18,7) ) lastNonNullval
    
      
    FROM Table1
     
    
    drop table Table1
    /*
    
    
    
    */
    
    --Check this link about this solution:
    --Itzik Ben-Gan
    --http://sqlmag.com/t-sql/last-non-null-puzzle
    
    --
    /*
    From your question, you need to use right data type for table columns and I made some changes to provide the solution posted.
    
    1.Use the right data type for your table columns (your have datetime data not datetime2 and Datavalue is not varchar(200));
    2. An empty string '' is not the same as a null value and null is null and it is an important concept.
    
    By the way, thanks for providing your table structure and sample data.
    
    */

    • Marked as answer by czarvk Friday, February 27, 2015 8:01 PM
    Thursday, February 26, 2015 12:20 AM
  • Hi Czarvk,

    To achieve your requirement in SQL Server 2008, you may reference the below.

    CREATE  TABLE Table1 (Prod_Order int, Datetime datetime, Datavalue Decimal(18,7))
    
    INSERT INTO Table1 values('106', '2015-02-20 09:00:00.000' , 1010.3499756)
    INSERT INTO Table1 values('106', '2015-02-20 10:00:00.000',    null)
    INSERT INTO Table1 values('106', '2015-02-20 11:00:00.000' ,  3113.1999512)
    INSERT INTO Table1 values('106', '2015-02-20 12:00:00.000' ,           null)
    INSERT INTO Table1 values('106', '2015-02-20 13:00:00.000' ,           null)
    INSERT INTO Table1 values('106', '2015-02-20 14:00:00.000' ,            null)
    INSERT INTO Table1 values('106', '2015-02-20 15:00:00.000' ,           null)
    INSERT INTO Table1 values('106', '2015-02-20 16:00:00.000' ,  5219.0998536)
    
    ;WITH cte AS(
    SELECT t.Prod_Order,t.Datetime,t.Datavalue,t1.Datetime dt,t1.Datavalue dv
    FROM Table1 t
    JOIN
    Table1 t1
    ON t.DateTime > t1.DateTime
    WHERE t1.Datavalue IS NOT NULL),
    cte2 AS(
    SELECT *, ROW_NUMBER() OVER(PARTITION BY Prod_Order,Datetime ORDER BY DT DESC) AS rn FROM cte
    ) 
    SELECT t.prod_Order,t.Datetime,ISNULL(t.Datavalue,c.dv) Datavalue
    FROM Table1 t LEFT JOIN cte2 c
    		ON t.Prod_Order=c.Prod_Order AND t.datetime=c.datetime
    WHERE c.rn=1 or c.rn IS NULL



    If you have any feedback on our support, please click here.

    Eric Zhang
    TechNet Community Support


    • Marked as answer by czarvk Friday, February 27, 2015 8:01 PM
    Thursday, February 26, 2015 2:34 AM
  • SELECT t.Prod_Order,t.[datetime],COALESCE(t.[Datavalue],t1.[Datavalue]) AS [Datetime]
    FROM table1 t
    OUTER APPLY (SELECT TOP 1 Datavalue
    FROM Table1
    WHERE Prod_Order = t.Prod_Order
    AND [Datetime] < t.[Datetime]
    AND Datavalue IS NOT NULL
    ORDER BY [Datetime] DESC
    )t1

    or

    ;With CTE
    AS
    (
    SELECT *,
    ROW_NUMBER() OVER (PARTITION BY Prod_Order ORDER BY [Datetime]) AS Seq
    FROM Table1
    )
    
    SELECT c1.Prod_Order,c1.[Datetime],
    COALESCE(c1.[Datetime],c2.{datetime]) AS [Datetime]
    FROM CTE c1
    LEFT JOIN CTE c2
    ON c2.Prod_Order = c1.Prod_Order
    AND c2.[Datetime] < c1.[Datetime]
    WHERE ((c2.Datevalue IS NOT NULL
    AND NOT EXISTS (SELECT 1
    FROM CTE
    WHERE Prod_Order = c1.Prod_Order
    AND [Datetime] > c2.[Datetime]
    AND c2.Datevalue IS NOT NULL
    ))
    OR c2.Prod_Order IS NULL
    )


    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

    • Marked as answer by czarvk Friday, February 27, 2015 8:01 PM
    Thursday, February 26, 2015 2:49 AM

  • ALTER TABLE Table1 ADD ID INT IDENTITY(1,1)

    SELECT *, CASE WHEN Datavalue <>''
                THEN Datavalue
                ELSE (SELECT max(Datavalue)
                      FROM Table1
                      WHERE ID <= t.ID)
           END AS Datavalue
           
    FROM Table1 t

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by czarvk Friday, February 27, 2015 8:01 PM
    Thursday, February 26, 2015 6:54 AM
    Answerer

All replies

  • CREATE  TABLE Table1 (Prod_Order int, Datettime datetime, Datavalue Decimal(18,7))
    
    INSERT INTO Table1 values('106', '2015-02-20 09:00:00.000' , 1010.3499756)
    INSERT INTO Table1 values('106', '2015-02-20 10:00:00.000',    null)
    INSERT INTO Table1 values('106', '2015-02-20 11:00:00.000' ,  3113.1999512)
    INSERT INTO Table1 values('106', '2015-02-20 12:00:00.000' ,           null)
    INSERT INTO Table1 values('106', '2015-02-20 13:00:00.000' ,           null)
    INSERT INTO Table1 values('106', '2015-02-20 14:00:00.000' ,            null)
    INSERT INTO Table1 values('106', '2015-02-20 15:00:00.000' ,           null)
    INSERT INTO Table1 values('106', '2015-02-20 16:00:00.000' ,  5219.0998536)
    
    
    --select * from Table1
     
    SELECT Prod_Order, /*Datavalue, */
    Datettime,
    CAST(SUBSTRING(MAX( CAST(Datettime AS BINARY(32)) + CAST(Datavalue AS BINARY(12)) )
    OVER( ORDER BY Datettime ASC ROWS UNBOUNDED PRECEDING ),33,12) AS Decimal(18,7) ) lastNonNullval
    
      
    FROM Table1
     
    
    drop table Table1
    /*
    
    
    
    */
    
    --Check this link about this solution:
    --Itzik Ben-Gan
    --http://sqlmag.com/t-sql/last-non-null-puzzle
    
    --
    /*
    From your question, you need to use right data type for table columns and I made some changes to provide the solution posted.
    
    1.Use the right data type for your table columns (your have datetime data not datetime2 and Datavalue is not varchar(200));
    2. An empty string '' is not the same as a null value and null is null and it is an important concept.
    
    By the way, thanks for providing your table structure and sample data.
    
    */

    • Marked as answer by czarvk Friday, February 27, 2015 8:01 PM
    Thursday, February 26, 2015 12:20 AM
  • Hi Czarvk,

    To achieve your requirement in SQL Server 2008, you may reference the below.

    CREATE  TABLE Table1 (Prod_Order int, Datetime datetime, Datavalue Decimal(18,7))
    
    INSERT INTO Table1 values('106', '2015-02-20 09:00:00.000' , 1010.3499756)
    INSERT INTO Table1 values('106', '2015-02-20 10:00:00.000',    null)
    INSERT INTO Table1 values('106', '2015-02-20 11:00:00.000' ,  3113.1999512)
    INSERT INTO Table1 values('106', '2015-02-20 12:00:00.000' ,           null)
    INSERT INTO Table1 values('106', '2015-02-20 13:00:00.000' ,           null)
    INSERT INTO Table1 values('106', '2015-02-20 14:00:00.000' ,            null)
    INSERT INTO Table1 values('106', '2015-02-20 15:00:00.000' ,           null)
    INSERT INTO Table1 values('106', '2015-02-20 16:00:00.000' ,  5219.0998536)
    
    ;WITH cte AS(
    SELECT t.Prod_Order,t.Datetime,t.Datavalue,t1.Datetime dt,t1.Datavalue dv
    FROM Table1 t
    JOIN
    Table1 t1
    ON t.DateTime > t1.DateTime
    WHERE t1.Datavalue IS NOT NULL),
    cte2 AS(
    SELECT *, ROW_NUMBER() OVER(PARTITION BY Prod_Order,Datetime ORDER BY DT DESC) AS rn FROM cte
    ) 
    SELECT t.prod_Order,t.Datetime,ISNULL(t.Datavalue,c.dv) Datavalue
    FROM Table1 t LEFT JOIN cte2 c
    		ON t.Prod_Order=c.Prod_Order AND t.datetime=c.datetime
    WHERE c.rn=1 or c.rn IS NULL



    If you have any feedback on our support, please click here.

    Eric Zhang
    TechNet Community Support


    • Marked as answer by czarvk Friday, February 27, 2015 8:01 PM
    Thursday, February 26, 2015 2:34 AM
  • SELECT t.Prod_Order,t.[datetime],COALESCE(t.[Datavalue],t1.[Datavalue]) AS [Datetime]
    FROM table1 t
    OUTER APPLY (SELECT TOP 1 Datavalue
    FROM Table1
    WHERE Prod_Order = t.Prod_Order
    AND [Datetime] < t.[Datetime]
    AND Datavalue IS NOT NULL
    ORDER BY [Datetime] DESC
    )t1

    or

    ;With CTE
    AS
    (
    SELECT *,
    ROW_NUMBER() OVER (PARTITION BY Prod_Order ORDER BY [Datetime]) AS Seq
    FROM Table1
    )
    
    SELECT c1.Prod_Order,c1.[Datetime],
    COALESCE(c1.[Datetime],c2.{datetime]) AS [Datetime]
    FROM CTE c1
    LEFT JOIN CTE c2
    ON c2.Prod_Order = c1.Prod_Order
    AND c2.[Datetime] < c1.[Datetime]
    WHERE ((c2.Datevalue IS NOT NULL
    AND NOT EXISTS (SELECT 1
    FROM CTE
    WHERE Prod_Order = c1.Prod_Order
    AND [Datetime] > c2.[Datetime]
    AND c2.Datevalue IS NOT NULL
    ))
    OR c2.Prod_Order IS NULL
    )


    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

    • Marked as answer by czarvk Friday, February 27, 2015 8:01 PM
    Thursday, February 26, 2015 2:49 AM

  • ALTER TABLE Table1 ADD ID INT IDENTITY(1,1)

    SELECT *, CASE WHEN Datavalue <>''
                THEN Datavalue
                ELSE (SELECT max(Datavalue)
                      FROM Table1
                      WHERE ID <= t.ID)
           END AS Datavalue
           
    FROM Table1 t

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by czarvk Friday, February 27, 2015 8:01 PM
    Thursday, February 26, 2015 6:54 AM
    Answerer