Add date based on two column values and transpose other columns

I have the next two tables:

Table A

Table B

I would want a table like this, when the first week start in 01/01/2001, the second week would be 08/01/2001 ans so on, it could be any date the important thing is that the difference between dates have to be one week:

I try the next code to transpose the columns for the two columns:

CREATE TABLE TABLE_A1 AS (SELECT P.PRODUCT AS PRODUCT_1, 
                              P.PLANT AS PLANT_1, 
                              P.PRODUCTION AS PRODUCTION_1
                              FROM (SELECT PRODUCT, PLANT, ProductionWeek1 FROM TableA 
                                    UNION 
                                    SELECT PRODUCT, PLANT, ProductionWeek2 FROM TableA
                                    UNION 
                                    ...
                                    SELECT PRODUCT, PLANT, ProductionWeek11 FROM TableA) p;

CREATE TABLE TABLE_B1 AS (SELECT s.PRODUCT AS PRODUCT_2, 
                              s.PLANT AS PLANT_2, 
                              s.SALES AS SALES_2
                              FROM (SELECT PRODUCT, PLANT, SalesWeek1 FROM TableB 
                                    UNION 
                                    SELECT PRODUCT, PLANT, SalesWeek2 FROM TableB
                                    UNION 
                                    ...
                                    SELECT PRODUCT, PLANT, SalesWeek11 FROM TableB) s;

Then try to put all together

CREATE TABLE FINAL_TABLE AS ( 
                            SELECT PRODUCT_1 AS PRODUCT,
                            PLANT_1_CONSUMOS AS PLANT, 
                            PRODUCTION_1,
                            SALES_2
                            FROM TABLE_A1
                            INNER JOIN TABLE_B1 
                            ON TABLE_B1.PRODUCT_2=TABLE_A1.PRODUCT_1
                            );

But I have a table like this:

The table is in disorder due to in the creation from TABLE_A1 and TABLE_B1 the union was in disorder too, so for that reason is important the date, for example I expect an union like this:

But I got the next table

where the value for the last week is in other row (not necesarry the first). Any help?

The current tables can be described as being "pivoted" because you have data representing a single week per column. To make sense of this you first need to "unpivot" that data and while you can use unions to do this, it is more efficient to simply cross join the source table to a set of rows representing each week (i.e. 11 rows). Then once cross joined use case expressions to move the data into the needed number of columns. e.g. the unpivoted result should look like this (ignoring weeks 2 to 10):

product plant   week    type    value
10011   ABC32   1   production  6742
10011   DEF10   1   production  3579
45891   GHI22   1   production  8945
10011   ABC32   11  production  3645
10011   DEF10   11  production  8761
45891   GHI22   11  production  1556
10011   ABC32   1   sales   425
10011   DEF10   1   sales   879
45891   GHI22   1   sales   904
10011   ABC32   11  sales   364
10011   DEF10   11  sales   501
45891   GHI22   11  sales   395

Once the data is available in this form it is a relatively simple task to produce a combination of the source columns by week. Note also that in the 11 weekly rows to be used it is also possible to supply the needed dates.

nb as you have not specified which rdbms is being used the following is in Postgres syntax but aside from the ::date syntax used the rest is generic SQL.

with w as (
          select 1 as week, '2001-01-01'::date as week_date union all
          /*   other weeks */
          select 11 as week, '2001-06-18'::date as week_date
        )
, unpiv as (
        select t.Product, t.Plant, w.week, w.week_date, 'production' as type
            , case
               when w.week = 1 then t.ProductionWeek1
               /* Other weeks */
               when w.week = 11 then t.ProductionWeek11
            end as value
        from Production t
        cross join w
        union all
        select t.Product, t.Plant, w.week, w.week_dat, 'sales' as type
            , case
               when w.week = 1 then t.SalesWeek1
               /* Other weeks */
               when w.week = 11 then t.SalesWeek11
            end as value
        from Sales t
        cross join w
        )
select
      u.Product, u.Plant
    , max(case when type = 'production' then value end) as production
    , max(case when type = 'sales' then value end) as sales
    , week, week_date
from unpiv u
group by
      u.Product, u.Plant
    , week, week_date
order by
      u.Product, u.Plant
    , week, week_date

fiddle

and here is a second (only slightly different) approach: https://dbfiddle.uk/pC0wmedZ