使用日期将年销售额拆分为月销售额

Question

我是非常新的SQL，但希望你能帮助我在这个逻辑。我需要帮助在SQL Server (SQL Server Management Studio)中编写此功能。我有一个发布日期，以及以年为单位的销售汇总。

当前我的数据是这样的:

销售表

25000 250006000070000

你可以试试:

with ydata as (
select 'Project 1' as ProjectName, convert(date,'2021-01-01') as LaunchDate, 15000 as SalesY1,25000 as SalesY2 union all
select 'Project 2','2021-06-01',15000,25000 union all
select 'Project 3','2021-03-01',10000,60000 union all
select 'Project 4','2021-12-01',75000,70000
),
mnt as (
select 1 as n, convert(date,'20210101',112) as d
union all
select n + 1, dateadd(month,1,d)
from mnt
where n < 24
)
select 
ProjectName, d as SalesMonth, 
case 
when year(d) = 2021 then SalesY1/12
else SalesY2/12 
end as Sales
from mnt m
cross join ydata d
order by ProjectName, d

您可以在此数据库<>提琴

上进行测试。