SQL Query With Nearest Date for BackFilling

Question

I have two tables.

Comments
  - CommentId
  - Comment
  - CreateDate
  - SomeCompositeKey

Exceptions
  - ExceptionId
  - Color
  - CreateDate
  - SomeCompositeKey
  - ...and so on...

My Comments table is getting a new Color column added to it with a default value of "White".

I will need to backfill and update the new Color field on the Comments table. I need to set the Comments.Color column to equal the Exception.Color based on the nearest CreateDate of the Comment and the matching Exception (matched based on the SomeCompositeKey).

This is using SQL Server Management Studio.

How many different colours are there? if there arent that many you could just use an update with a case statement on the createdate field. — Owain Esau, Jul 26 '17 at 23:43

Jason A. Long · Answer 1 · 2017-07-27T02:28:46.993

This should do the trick...

IF OBJECT_ID('tempdb..#Comments', 'U') IS NULL 
BEGIN   -- DROP TABLE #Comments;
    CREATE TABLE #Comments (
        CommentId INT NOT NULL PRIMARY KEY CLUSTERED,
        Comment VARCHAR(100) NOT NULL,
        CreatedDate DATETIME NOT NULL,
        Color VARCHAR(10) NOT NULL DEFAULT('White')
        );

    INSERT #Comments (CommentId, Comment, CreatedDate)
    SELECT 
        t.n,
        'Blah Blah Blah',
        DATEADD(hh, t.n, GETDATE())
    FROM
        dbo.tfn_Tally(1500, 1) t;
END;

IF OBJECT_ID('tempdb..#Exceptions', 'U') IS NULL 
BEGIN   -- DROP TABLE #Exceptions;
    CREATE TABLE #Exceptions (
        ExceptionId INT NOT NULL PRIMARY KEY CLUSTERED,
        Color VARCHAR(10) NOT NULL,
        CreatedDate DATETIME NOT NULL
        );

    INSERT #Exceptions (ExceptionId, Color, CreatedDate)
    SELECT 
        t.n,
        CASE t.n % 9
            WHEN 0 THEN 'Red'
            WHEN 1 THEN 'Blue'
            WHEN 2 THEN 'Green'
            WHEN 3 THEN 'Yellow'
            WHEN 4 THEN 'Purple'
            WHEN 5 THEN 'Orance'
            WHEN 6 THEN 'Gray'
            WHEN 7 THEN 'White'
            WHEN 8 THEN 'Black'
        END,
        DATEADD(hh, t.n * 13, GETDATE())
    FROM
        dbo.tfn_Tally(100, 1) t;

    -- Add a unique nci on the CreatedDate column to improve performance.
    CREATE UNIQUE NONCLUSTERED INDEX uix_Exceptions_CreatedDate ON #Exceptions 
        (CreatedDate) INCLUDE (Color);
END;

--=========================================================
-- option 1 (faster when there is an index on e.CreatedDate)
UPDATE c SET
    c.Color = ex.Color
FROM
    #Comments c
    CROSS APPLY (
                SELECT TOP 1
                    e.Color
                FROM
                    #Exceptions e
                WHERE 
                    c.CreatedDate >= e.CreatedDate
                ORDER BY 
                    e.CreatedDate DESC
                ) ex;

--=========================================================
-- option 2 (faster when there is not index on e.CreatedDate)
WITH 
    cte_ExceptionRange AS (
        SELECT 
            e.Color, 
            BegData = e.CreatedDate,
            EndDate = LEAD(e.CreatedDate, 1, '9999-12-31') OVER (ORDER BY e.CreatedDate)
        FROM
            #Exceptions e
        )
UPDATE c SET
    c.Color = er.Color
FROM
    #Comments c
    JOIN cte_ExceptionRange er
        ON c.CreatedDate >= er.BegData
        AND c.CreatedDate <er.EndDate;

HTH, Jason

score 0 · Answer 2 · answered Jul 27 '17 at 00:31

Check this query , it may helps you to achieve what you need

;with data 
as
(
  Select ExceptionId,Color, SomeCompositeKey
  ,row_number() over  (partition by SomeCompositeKey order by CreateDate desc) rowNumber 
  from   Exceptions
)
  update Comments set newColor=d.Color 
  from data d where d.rowNumber=1 
  and Comments.SomeCompositeKey=d.SomeCompositeKey

SQL Query With Nearest Date for BackFilling

2 Answers2