SQL Server – Calculating target vs actual values per week for sales overview

I am new to SQL. I am trying to create an over view using these 2 tables

Actual sale

Week_Year

        Unit    34_2020 35_2020 36_2020 37_2020
        Unit 1  10      12      15      19
        Unit 2  10      12      15      19
        Unit 3  10      12      15      19

Target sale

        Unit    Total to be sold    Start Date  End Date
        Unit 1  50                  24-08-20    24-09-20
        Unit 2  1000                18-01-20    01-01-21
        Unit 3  1000                05-02-20    01-10-20

To combine into this resulting view with Targets and Actuals:

        Unit 1                  Unit 2                  Unit 3  
        Week    Target  Actual  Week    Target  Actual  Week    Target  Actual
        34_2020     11  10      3_2020  20      10      6_2020  20      10
        35_2020     24  12      4_2020  40      12      7_2020  40      12
        36_2020     36  15      5_2020  60      15      8_2020  60      15
        37_2020     50  19      6_2020  80      19      9_2020  80      19
                                .       100     .       .       100     .
                                36_2020 120     95      36_2020 700     650
                                37_2020 140     100     37_2020 800     700
                                .       .               38_2020 .
                                .       .               39_2020 .
                                .       .               40_2020 1000
                                1_2021  1000        

where column Target is ‘Total to be sold’ spread linearly between the available weeks.

How can I achieve this using SQL Server? Any inputs much appreciated. Thanks.

Answer

In order to make the week numbers (34, 35, 36, 37) correspond to system weeks the variable @start_wk_no sets the starting point. The actual sales needs to be unpivoted to join with projected sales. The query uses a tally (or numbers) function to generate the rows.

Data

drop table if exists dbo.test_actuals;
go
create table dbo.test_actuals(
  Unit          varchar(100) not null,
  [34_2020]         int not null,
  [35_2020]         int not null,
  [36_2020]         int not null,
  [37_2020]         int not null);
--select * from dbo.test_actuals

insert dbo.test_actuals values
('Unit 1', 10, 12, 15, 19),
('Unit 2', 10, 12, 15, 19),
('Unit 3', 10, 12, 15, 19);

drop table if exists dbo.test_target;
go
create table dbo.test_target(
  Unit          varchar(100) not null,
  TotalToSell   int not null,
  StartDate     date not null,
  EndDate       date not null)

insert dbo.test_target values
('Unit 1', 50, '08-24-2020', '09-24-2020'),
('Unit 2', 1000, '01-18-2020', '01-01-2021'),
('Unit 3', 1000, '02-05-2020', '10-01-20');

Query

/* based on system weeks, what is the start point */
declare
  @start_wk_no                  int=6250;

;with unpvt_actuals_cte as (
    select a.Unit, v.*
    from
      dbo.test_actuals a
     cross apply
      (values (34, [34_2020]), (35, [35_2020]), (36, [36_2020]), (36, [36_2020]), (37, [37_2020])) v([Week], act_sales))
select
  t.Unit,
  wd.wk_proj [Week],
  isnull(act.act_sales, 0) [Actual],
  TotalToSell/(wk_diff.wk_diff*1.0) [Target],
  sum(TotalToSell/(wk_diff.wk_diff*1.0)) over (partition by t.Unit order by wd.wk_proj) Cum_Target
from
  dbo.test_target t
 cross apply
  (select datediff(wk, t.StartDate, t.EndDate) wk_diff) wk_diff
 cross apply
  dbo.fnTally(0, wk_diff.wk_diff-1) f
 cross apply
  (select dateadd(wk, f.n, t.StartDate) wk_dt) wk
cross apply
  (select datediff(week, 0, wk.wk_dt)-@start_wk_no wk_proj) wd
left join
  unpvt_actuals_cte act on t.Unit=act.Unit
                           and  wd.wk_proj=act.[Week];