Extending date column for table

Question

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.

Answer 1

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:

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.

Answer 2

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!!!