SQL Server-计算每周的目标值与实际值，以了解销售概况

Question

我是SQL的新手。我正在尝试使用这两个表创建一个Overview

实际销售

周_年

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

目标销售

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

将目标和实际值合并到该结果视图中：

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        

其中，Target列为"待销售总额"，在可用周之间线性分布。

如何使用SQL Server实现这一点？非常感谢任何意见。谢谢

Answer 1

为了使周数(34、35、36、37(与系统周相对应，变量@start_wk_no设置起点。需要取消对实际销售额的拆分，才能将其与预计销售额合并。查询使用计数(或数字(函数来生成行。

数据

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');

查询

/* 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];