Extending date column for table

kanakanaka77 21 Reputation points
2021-02-09T02:59:04.207+00:00

Hi there,

I have a table that has run out of weeks.
I need to extend the time period out using the same data for all columns from the previous year, with the exception of updating the new week column to the current year. Any help appreciated, I'm a noob.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,666 questions
{count} votes

Accepted answer
  1. EchoLiu-MSFT 14,591 Reputation points
    2021-02-09T06:18:49.95+00:00

    Hi @kanakanaka77 ,

    Please check if the method below works:

        CREATE TABLE #test1(Week int, Data1 int, Data2 int)  
        INSERT INTO #test1 VALUES(202049,123,123),(202050,234,321),  
                                 (202051,456,242),(202052,654,987)  
          
        CREATE TABLE #test3(rr int,week int)  
        INSERT INTO #test3 VALUES(1,202101),(2,202102),(3,202103),(4,202104)  
          
        ;WITH cte  
        as(SELECT *,ROW_NUMBER() OVER(ORDER BY Week)rr FROM #test1)  
        ,cte2 as  
        (SELECT t.week,c.Data1,c.Data2  
        FROM cte c  
        JOIN #test3 t  
        ON c.rr=t.rr)  
        INSERT INTO #test1  
        SELECT * FROM cte2  
          
          
        SELECT * FROM #test1  
    

    Output:
    65647-image.png

    If you have any question, please feel free to let me know.
    If the response is helpful, please click "Accept Answer" and upvote it.

    Regards
    Echo


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


1 additional answer

Sort by: Most helpful
  1. kanakanaka77 21 Reputation points
    2021-02-09T03:30:18.207+00:00

    Hi there,

    concept would be

    taking data from below:

    Week Data 1 Data2
    202049 123 123
    202050 234 321
    202051 456 242
    202052 654 987

    and reinserting it into the same table with current weeks 202101,202102,202103,202104.

    Hope this helps.

    THanks!!!

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.