sql server bi_SQL Server和BI –创建收入预测查询

最新推荐文章于 2021-01-28 22:42:45 发布

culuo4781

最新推荐文章于 2021-01-28 22:42:45 发布

阅读量444

点赞数

文章标签：大数据 java 数据分析人工智能 sql

原文链接：https://www.sqlshack.com/sql-server-and-bi-creating-query-for-revenue-projection/

版权

sql server bi

A few days ago I received an interesting challenge from one of our clients. The lady was attempting to estimate her potential monthly revenue recognition for the fiscal year beginning January 1, 2015, through December 31, 2015. The lady named Linda sells goods and services (each class yielding differing sales margins).

几天前，我收到了一位客户的有趣挑战。这位女士正试图估计从2015年1月1日到2015年12月31日的财政年度她潜在的每月收入确认。这位名为琳达的女士销售商品和服务（每个类别产生不同的销售利润率）。

In the first portion of this two-part discussion, we shall be looking at the revenue projections for goods.

在这个分为两部分的讨论的第一部分中，我们将研究商品的收入预测。

Linda’s business rules are as follows.

琳达的业务规则如下。

In the month in which a client order for goods is booked (and this may be some time in the ‘future’ say for Christmas time), if the order is placed before the 21^st of that month then she ‘claims’ the total revenue for the booked month. Should the order date be greater than the 21^st of that month then she claims only 70% and the remaining 30% in the following month.

在预订客户的商品订单的月份（这可能是圣诞节的“未来”时间），如果订单在该月的21 ^日之前下达，则她“要求”总收入预定的月份。如果订购日期大于该月的21 ^日，则她仅要求70％，其余30％要求在下个月进行。

Our challenge for today’s get together is to create a query that will provide Linda the information that she requires based upon her corporate data.

对于今天的聚会，我们面临的挑战是创建一个查询，该查询将根据琳达的公司数据为琳达提供所需的信息。

Let’s get started!

让我们开始吧！

入门 ( Getting Started )

Perhaps the best manner in which to begin is to have a high-level glance at how the final data will appear AFTER applying Linda’s revenue projection rules.

也许最好的开始方式是，在应用Linda的收入预测规则后，对最终数据的显示方式有一个高度的了解。

The reader will note that on row 1 (in the screen dump above), the order date was ‘2015-02-12’ which was earlier than Linda’s ‘cut off’ date of the 21^st of the month. This said she will project the revenue from this row ‘in full’ for the month of February 2015. Further, because the order date was less than the 21^st of the month, the defined field ‘SplitYN’ has been set to ‘nosplit’. The implications of this will become apparent in a few minutes.

读者会注意到，在第1行（在上面的屏幕转储中），订购日期为'2015-02-12'，早于Linda每月的21 ^日为“截止日期”。这表示她将对该行的收入进行“ 全部 ”收入预测（2015年2月）。此外，由于订购日期少于该月的21 ^日，因此已定义字段“ SplitYN”已设置为“ nosplit” 。几分钟后，这种影响将变得显而易见。

Having a look at row 4 (see above) we note that the order date is ‘2015-02-27’ which is, in fact, GREATER than Linda’s 21^st cut-off date for recognizing the full revenue for the month of February 2015 and therefore she will only recognize 70% of the ‘Original Revenue’ (see the last column of the screenshot above) in February, and the remaining 30% in March 2015 (See row 5 above). In this case, the defined field ‘SplitYN’ is set to ‘split’.

在看看4行（见上文）我们注意到，订单日期是“2015年2月27日”，这是，事实上，大于琳达的21 ^日截止日期认识到2015年2月份全部收入因此，她只会在2月确认70％的“原始收入”（请参见上面的屏幕快照的最后一列），而在2015年3月将仅确认剩余的30％（请参见上面的第5行）。在这种情况下，已定义字段“ SplitYN”设置为“ split”。

With a high-level understanding of Linda’s business rules and having seen some of the expected results, let us begin constructing our business solution. The full code listing may be found in Addenda 1.

在对Linda的业务规则有较高的了解并看到一些预期的结果之后，让我们开始构建业务解决方案。完整的代码清单可以在附录1中找到。

We begin by declaring a few variables

我们首先声明一些变量

 
declare @yearIncoming varchar(4)
declare @yearr as Varchar(4)
declare @Lowyearr as Varchar(4)
set @Yearr = datepart(YEAR,Convert(date,Getdate()))
set @YearIncoming = @yearr
declare @kounter as int
Set @Kounter = 1
declare @kount as int
Set @Kount = 0
declare @beginFiscal datetime
declare @endFiscal datetime
declare @ActualBeginfiscal as datetime

The purpose of these variables will become apparent in a few seconds.

这些变量的目的将在几秒钟内变得显而易见。

Remembering that Linda wishes to look at the data for the 2015 calendar / fiscal year, then our start date must be December 1, 2014, running through and including December 2015.

记住Linda希望查看2015日历/会计年度的数据，那么我们的开始日期必须是2014年12月1日，一直持续到2015年12月（含）。

At this point, you are probably crying “foul”!! It is important to remember that Linda may have had split data booked in the month of December where she posted 70% in that December and 30% for January 2015. It is this 30% that must be added to our January 2015 projections.

此时，您可能正在哭“犯规”！要记住，琳达可能已经在12月，她贴出70％在十二月预订分割数据很重要， 2015 年 1月为30％。必须将这30％添加到我们2015年1月的预测中。

We set our data extraction dates to

我们将数据提取日期设置为

 
set @ActualBeginfiscal = convert(varchar(4),@Yearr) + '0101'
set @Endfiscal   = convert(varchar(4),@Yearr) + '1231'
set @Beginfiscal = convert(varchar(4),@Yearr-1) + '1201'

At this point, an explanation is required. @BeginFiscal will be the start date for our raw data extract. @EndFiscal will be the end date of data extract. The reader is reminded that some data may have been split in December 2014 and we, therefore, must extract this data. We shall rid ourselves of the December 2014 data that was not split plus the split data posted in December 2014, in our final extract (via the predicate logic) as we shall shortly see.

此时，需要进行解释。 @BeginFiscal将是原始数据提取的开始日期。 @EndFiscal将是数据提取的结束日期。提醒读者，某些数据可能已在2014年12月分割，因此，我们必须提取这些数据。我们将在最终的摘录中（通过谓词逻辑）摆脱未拆分的2014年12月数据以及 2014年12月发布的拆分数据，我们将很快看到。

Linda’s raw data (from the orders table) may be seen in the screen shot above.

琳达的原始数据（来自订单表）可以在上面的屏幕截图中看到。

创建和处理我们的查询 ( Creating and processing our query )

Our first task is to ascertain which orders must have their revenues split in order to allocate the projections correctly.

我们的首要任务是确定必须拆分哪些订单的收入才能正确分配预测。

 
Select 'Goods' as Type, 
 department, [year], 
 convert(date,orderDate) as orderDate,yearMth,
 case when Convert(date,OrderDate) < Convert(date,dbo.YearMth(convert(date,orderDate))+'21')  then 'nosplit' else 'split' end as SplitYN,
 revenue into #rawdata9 
 from (
  select Revenue, Department, OrderDate, Year, YearMth
 from dbo.Linda) a
Set @Kount = @@Rowcount

Parts of the code snippet above also require an explanation. In this snippet, we set the value of the field ‘SplitYN’. The business rules state that I must split my revenue if and only if the order date is beyond the 21^st of any given month. Many ‘moons’ ago I created a small user-defined function called YearMth. YearMth accepts a datetime field and returns a varchar(6) value containing YYYYMM. The listing for YearMth may be found in Addenda 2.

上面的代码片段的某些部分也需要说明。在此代码段中，我们设置字段“ SplitYN”的值。业务规则规定，当且仅当订单日期超过任何给定月份的21 ^日时，我才必须拆分我的收入。许多“月球”之前，我创建了一个名为YearMth的用户定义的小函数。 YearMth接受日期时间字段，并返回包含YYYYMM的varchar（6）值。 YearMth的清单可以在附录2中找到。

The results of executing this code snippet may be seen below:

执行此代码段的结果如下所示：

Note also that we have trapped the number of rows that have been extracted through the use of the following code. We shall use the number of rows shortly.

还要注意，我们已经捕获了通过使用以下代码提取的行数。我们将很快使用行数。

 
  Set @Kount = @@Rowcount

The avid reader will have by this time realized that in order for us to achieve our end goal, for any ‘split record’ that we must reduce the revenue of the original month to 70% and add an additional record (for the next calendar month) representing the remaining 30%, thus making up the total revenue over two calendar months as opposed to one. The “nosplit” records are not an issue.

到那时，狂热的读者将会意识到，为了实现最终目标，对于任何“拆分记录”，我们都必须将原始月份的收入减少到70％， 并增加一条记录 （下一个日历月））代表剩余的30％，因此占两个日历月的总收入（而不是一个）。 “未拆分”记录不是问题。

To achieve this we are going to create a temporary table called #rawdata10 which will have an identity as a key field. The reason for this will become apparent in a few seconds.

为此，我们将创建一个名为＃rawdata10的临时表，该表将具有一个身份作为键字段。其原因将在几秒钟内变得显而易见。

 
CREATE TABLE #rawdata10(
       [ID] [int] IDENTITY(1,1) NOT NULL,
	[Type] [varchar](9) NOT NULL,
	[Department] [varchar](15) NOT NULL,
	[Year] int null,
	[orderDate] [date] NULL,
	[YearMth] varchar(6) null,
	[SplitYN] Varchar(7) null,
	[revenue] [decimal](38, 2) NULL  
                       ) 
Insert into #rawdata10
 select 
	Type   ,
	[Department] , 
	[Year] ,
	[orderDate] ,
	[YearMth] ,
	[SplitYN] ,
	[revenue]   
	from #rawdata9

We now insert the records shown in the screen dump above into #rawdata10. The results of doing so may be seen in the screen shot below:

现在，我们将以上屏幕转储中显示的记录插入＃rawdata10中。可以在下面的屏幕截图中看到这样做的结果：

At this point, the astute reader will have noted two important facts pertaining only to records whose revenue must be split over two months

在这一点上，精明的读者将注意到两个重要事实，这些事实仅与收入必须在两个月内分配的记录有关

The revenue for first month must reduced to 70% (as discussed above).
第一个月的收入必须减少到70％（如上所述）。
An additional row must be added for the next month 一行 and， that row’s revenue must be 30% of the revenue of the previous month, i.e. to make up the full 100% (once again as discussed above). 该行的收入必须是前一个月收入的30％，即要弥补全部100％（再次如上所述）。

In order to achieve this goal and have an understanding of the purpose for creating the Identity column “Id”, we are going to parse each record within the #rawdata10 temporary table and should the “SplitYN” field has a value of “Split”, then we going to write that row (with only 70% of the revenue) to #rawData11.

为了实现此目标并了解创建Identity列“ Id”的目的，我们将分析＃rawdata10临时表中的每个记录，并且“ SplitYN”字段的值为“ Split”，那我们要写那行（仅占收入的70％）到＃rawData11。

This achieved we shall increment YearMth by one month, only this time we are going to write the same record with the incremented “Yearmth” and 30% of the original data to the same temporary data file, #rawdata11. All non-split rows are written as well to #rawdata11, however once and only once.

这实现了我们应有一名月份的递增YearMth，只是这一次，我们要具有增加的“Yearmth”和原始数据到同一个临时数据文件，＃rawdata11的30％写相同的 记录。所有未分割的行也都写入＃rawdata11，但是只有一次。

In order to achieve all of this, we need to utilize a loop. As we shall be looping and inserting rows into #rawdata11 as we go, the temporary table must have been created PRIOR to entering the loop.

为了实现所有这些，我们需要利用循环。由于我们将不断循环并将行插入＃rawdata11中，因此必须在进入循环之前创建临时表。

The ‘looping code’ may be seen below:

可以在下面看到“循环代码”：

 
While @Kounter <= @Kount
begin
--If [SplitYN] = 'split'
-- insert the 70% but not if the year is “last year” and this is the first part. First part was 
--recognized in December 2014 
      Insert into #rawdata11
      select Type,Department, [Year] ,[orderDate] ,[YearMth] ,[YearMth]  as NewYearMth,[SplitYN][SplitYN] ,[revenue] *.70 as Revenue, revenue as OrigRevenue
      from #rawdata10
--Predicate says only those 70% within the current fiscal year where revenue is to be split and
--Current Record = the record ID.
      where ID = @Kounter and SplitYN = 'split' and substring(YearMth,1,4) = convert(varchar(4),convert(date,getdate()))
      
      Insert into #rawdata11
      select Type,Department, [Year] ,[orderDate] ,[YearMth],
--We cannot have a 201513 for the second part of the split make it 201601
      case when right([YearMth],2) = '12' then Convert(Varchar(4),DatePart(Year,convert(Date,Getdate()))+1 )  +'01'  else  [YearMth] +1 end
      as NewYearMth ,[SplitYN] ,[revenue] *.30 as Revenue,revenue as OrigRevenue
      from #rawdata10
--Insert any ‘second parts’ as they will all fall into the calendar / fiscal year
--and the current year from getdate() = Year part from YearMTH i.e.2015 < 2016
      where (ID = @Kounter) and  (SplitYN = 'split') and (substring(YearMth,1,4) <=  
      Convert(Varchar(4),DatePart(Year,convert(Date,Getdate())) ) )
      
   
--If [SplitYN] = 'Nosplit'
      Insert into #rawdata11
      select Type,Department, [Year] ,[orderDate] ,[YearMth] ,[YearMth] as NewYearMth,[SplitYN]
      ,[revenue]
      ,revenue as OrigRevenue
      from #rawdata10 
--Insert all nosplits with yearMth within the current year 
      where (ID = @Kounter) and (SplitYN = 'nosplit') and (substring(YearMth,1,4) = convert(varchar(4),convert(date,getdate())))

We then increment the counter and re-iterate through within the loop

然后，我们增加计数器并在循环中重新迭代

 
  set @kounter = @Kounter +1

For the ‘non-believers’ amongst us, I have taken the liberty of extracting the splits before applying the predicate logic. Please see the screenshot below and then compare it to the result set following that screenshot.

对于我们当中的“非信徒”，我采取了在应用谓词逻辑之前提取拆分的自由。请查看下面的屏幕截图，然后将其与该屏幕截图之后的结果集进行比较。

Adding the “nosplits” we find the following for our extract (see below).

添加“ nosplits”后，我们可以找到以下摘录内容（请参见下文）。

It is important to remember that at this stage that the records have not been sorted by YearMth.

重要的是要记住，在这个阶段，该记录还没有 被YearMth排序是非常重要的。

格式化矩阵数据 ( Formating the data for a Matrix )

Linda has told us that she wishes to see the revenue for each month summarized by the department , by month.

琳达告诉我们，她希望看到部门每月汇总的每月收入。

In order to achieve this, we must define a few more variables and set their values accordingly (see below).

为了实现这一点，我们必须定义更多的变量并相应地设置它们的值（见下文）。

 
declare @month01 varchar(6)
declare @month02 varchar(6)
declare @month03 varchar(6)
declare @month04 varchar(6)
declare @month05 varchar(6)
declare @month06 varchar(6)
declare @month07 varchar(6)
declare @month08 varchar(6)
declare @month09 varchar(6)
declare @month10 varchar(6)
declare @month11 varchar(6)
declare @month12 varchar(6)
set @Month01 = Convert(varchar(4),DatePart(Year,Convert(date,getDate()))) + '01'
set @Month02 = Convert(varchar(4),DatePart(Year,Convert(date,getDate()))) + '02'
set @Month03 = Convert(varchar(4),DatePart(Year,Convert(date,getDate()))) + '03'
set @Month04 = Convert(varchar(4),DatePart(Year,Convert(date,getDate()))) + '04'
set @Month05 = Convert(varchar(4),DatePart(Year,Convert(date,getDate()))) + '05'
set @Month06 = Convert(varchar(4),DatePart(Year,Convert(date,getDate()))) + '06'
set @Month07 = Convert(varchar(4),DatePart(Year,Convert(date,getDate()))) + '07'
set @Month08 = Convert(varchar(4),DatePart(Year,Convert(date,getDate()))) + '08'
set @Month09 = Convert(varchar(4),DatePart(Year,Convert(date,getDate()))) + '09'
set @Month10 = Convert(varchar(4),DatePart(Year,Convert(date,getDate()))) + '10'
set @Month11 = Convert(varchar(4),DatePart(Year,Convert(date,getDate()))) + '11'
set @Month12 = Convert(varchar(4),DatePart(Year,Convert(date,getDate()))) + '12'

Running the code immediately above produces the following results. The reader will note that the @month variables run from 201501 through 201512.

直接在上面运行代码会产生以下结果。读者将注意到@month变量从201501到201512运行。

在我们的提取物中添加最后的修饰 ( Adding the final touch to our extract )

In order to give Linda the summary that she requires, we are now going to summarize the data by department and by month. This time, however, we shall be utilizing the value of “NewYearMth”. As a recap for split records, there will be two consecutive values for “NewYearMth” and for the non-split records, “NewYearMth” will be the same as “YearMth”.

为了提供Linda所需的摘要，我们现在将按部门和按月汇总数据。但是，这一次，我们将使用“ NewYearMth”的值。作为拆分记录的回顾，“ NewYearMth”将有两个连续的值，对于非拆分记录，“ NewYearMth”将与“ YearMth”相同。

The code to summarize the data may be seen below:

汇总数据的代码如下所示：

 
select Type,transactional,SolutionsArea 
,Sum(month01) as Month01
,Sum(month02) as Month02
,Sum(month03) as Month03
,Sum(month04) as Month04
,Sum(month05) as Month05
,Sum(month06) as Month06
,Sum(month07) as Month07
,Sum(month08) as Month08
,Sum(month09) as Month09
,Sum(month10) as Month10
,Sum(month11) as Month11
,Sum(month12) as Month12
 
from (
 
 
select Type,transactional,SolutionsArea 
,case when NewYearMth = @Month01 then Revenue else 0 end as Month01
,case when NewYearMth = @Month02 then Revenue else 0 end as Month02
,case when NewYearMth = @Month03 then Revenue else 0 end as Month03
,case when NewYearMth = @Month04 then Revenue else 0 end as Month04
,case when NewYearMth = @Month05 then Revenue else 0 end as Month05
,case when NewYearMth = @Month06 then Revenue else 0 end as Month06
,case when NewYearMth = @Month07 then Revenue else 0 end as Month07
,case when NewYearMth = @Month08 then Revenue else 0 end as Month08
,case when NewYearMth = @Month09 then Revenue else 0 end as Month09
,case when NewYearMth = @Month10 then Revenue else 0 end as Month10
,case when NewYearMth = @Month11 then Revenue else 0 end as Month11
,case when NewYearMth = @Month12 then Revenue else 0 end as Month12
from #rawdata11)a
 Group By Type,Department

The result from executing this code may be seen below:

执行此代码的结果如下所示：

The astute reader will note that not only are there results for the current months of this fiscal year, but there are also results for October and November 2015 (which at the time of writing this article are future dates).

精明的读者会注意到，不仅可以获得本会计年度当前月份的结果，而且还将获得2015年10月和2015年11月的结果（在撰写本文时为未来日期）。

瓦迪斯 ( Quo Vadis )

Using the SQL Server Reporting techniques that I have described in my previous articles, one may create a stored procedure from this code and utilize it to create a “matrix-based reports”.

使用我在之前的文章中介绍SQL Server Reporting技术，可以从此代码创建存储过程，并利用它来创建“基于矩阵的报告”。

Further, with the same code, one can extract the results to be placed in any one of the plethoras of charts available to Reporting Services. This exercise is left up to the reader.

此外，使用相同的代码，可以提取结果，以将其放置在Reporting Services可用的多种图表中。该练习留给读者。

结论 ( Conclusions )

In today’s get together we saw how we could help Linda project her revenue for any fiscal year. We also saw that we could split the projected revenue into two parts if the ‘deal’ was signed too late in the month to recognize the whole amount.

在今天的聚会中，我们看到了我们如何可以帮助琳达预测她在任何会计年度的收入。我们还看到，如果“交易”在本月签署得太晚而无法确认全部金额，则可以将预计收入分成两部分。

Linda also caters for parties which essentially is a service that she provides and, as such, she has different business rules to projecting this type of revenue. We shall have a look at this in our next get together.

琳达还迎合了聚会，而聚会本质上是她提供的一项服务，因此，她有不同的业务规则来预测此类收入。我们将在下一个聚会中看一下。

In the interim, happy programming!

在此期间，编程愉快！

附录1 ( Addenda 1 )

 
USE [LINDA]
GO
 
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID(N'tempdb..#rawdata10') IS NOT NULL
BEGIN
     DROP TABLE #rawdata10
END
IF OBJECT_ID(N'tempdb..#rawdata11') IS NOT NULL
BEGIN
     DROP TABLE #rawdata11
END
IF OBJECT_ID(N'tempdb..#rawdata9') IS NOT NULL
BEGIN
     DROP TABLE #rawdata9
END
IF OBJECT_ID(N'tempdb..#rawdata5') IS NOT NULL
BEGIN
     DROP TABLE #rawdata5
END
GO
   
--Declare variables and set start and end dates
--CREATE procedure [dbo].[LindasBookingsRecognition]             
--as
declare @yearIncoming varchar(4)
declare @decider as int
declare @yearr as Varchar(4)
declare @Lowyearr as Varchar(4)
set @Yearr = datepart(YEAR,Convert(date,Getdate()))
 
set @YearIncoming = @yearr
declare @username varchar(50)
set @username = '_All'
declare @period varchar(20)
set @Period = 'Month'
declare @kounter as int
Set @Kounter = 1
declare @kount as int
Set @Kount = 0
declare @beginFiscal datetime
declare @endFiscal datetime
declare @ActualBeginfiscal as datetime
declare @month01 varchar(6)
declare @month02 varchar(6)
declare @month03 varchar(6)
declare @month04 varchar(6)
declare @month05 varchar(6)
declare @month06 varchar(6)
declare @month07 varchar(6)
declare @month08 varchar(6)
declare @month09 varchar(6)
declare @month10 varchar(6)
declare @month11 varchar(6)
declare @month12 varchar(6) 
set @Month01 = Convert(varchar(4),DatePart(Year,Convert(date,getDate()))) + '01'
set @Month02 = Convert(varchar(4),DatePart(Year,Convert(date,getDate()))) + '02'
set @Month03 = Convert(varchar(4),DatePart(Year,Convert(date,getDate()))) + '03'
set @Month04 = Convert(varchar(4),DatePart(Year,Convert(date,getDate()))) + '04'
set @Month05 = Convert(varchar(4),DatePart(Year,Convert(date,getDate()))) + '05'
set @Month06 = Convert(varchar(4),DatePart(Year,Convert(date,getDate()))) + '06'
set @Month07 = Convert(varchar(4),DatePart(Year,Convert(date,getDate()))) + '07'
set @Month08 = Convert(varchar(4),DatePart(Year,Convert(date,getDate()))) + '08'
set @Month09 = Convert(varchar(4),DatePart(Year,Convert(date,getDate()))) + '09'
set @Month10 = Convert(varchar(4),DatePart(Year,Convert(date,getDate()))) + '10'
set @Month11 = Convert(varchar(4),DatePart(Year,Convert(date,getDate()))) + '11'
set @Month12 = Convert(varchar(4),DatePart(Year,Convert(date,getDate()))) + '12'
--select @month01,@Month02, @month03, @Month04 , @Month05 , @Month06
--select @month07,@Month08, @month09, @Month10 , @Month11 , @Month12
 
 
set @ActualBeginfiscal = convert(varchar(4),@Yearr) + '0101'
set @Endfiscal   = convert(varchar(4),@Yearr) + '1231'
set @Beginfiscal = convert(varchar(4),@Yearr-1) + '1201'
  
 
 Select 'Goods' as Type, 
 department, [year], 
 convert(date,orderDate) as orderDate,yearMth,
 case when Convert(date,OrderDate) < Convert(date,dbo.YearMth(convert(date,orderDate))+'21')  then 'nosplit' else 'split' end as SplitYN,
 revenue into #rawdata9  
 from (
  select Revenue, Department, OrderDate, Year, YearMth
 from dbo.Linda) a
 Set @Kount = @@Rowcount
 
 
 CREATE TABLE #rawdata10(
    [ID] [int] IDENTITY(1,1) NOT NULL,
	[Type] [varchar](9) NOT NULL,
	[Department] [varchar](15) NOT NULL,
	[Year] int null,
	[orderDate] [date] NULL,
	[YearMth] varchar(6) null,
	[SplitYN] Varchar(7) null,
	[revenue] [decimal](38, 2) NULL  
) 
 
 --select * from #rawdata10  
 Insert into #rawdata10
 select 
	Type   ,
	[Department] , 
	[Year] ,
	[orderDate] ,
	[YearMth] ,
	[SplitYN] ,
	[revenue]   
	from #rawdata9
	
  
CREATE TABLE #rawdata11(
   	[Type] [varchar](9) NOT NULL,
	[Department] [varchar](15) NOT NULL,
	[Year] int null,
	[orderDate] [date] NULL,
	[YearMth] varchar(6) null,
	[NewYearMth] varchar(6) null,
	[SplitYN] Varchar(7) null,
	[revenue] [decimal](38, 2) NULL ,
	[Origrevenue] [decimal](38, 2) NULL  
) 
 
declare @NextMonth as varchar(6)
set @kounter = 1
While @Kounter <= @Kount
 
begin
--If [SplitYN] = 'split'
      Insert into #rawdata11
      select Type,Department, [Year] ,[orderDate] ,[YearMth] ,[YearMth]  as NewYearMth,[SplitYN][SplitYN] ,[revenue] *.70 as Revenue, revenue as OrigRevenue
      from #rawdata10
      --Predicate says only those 70% within the current fiscal year where revenue is to be split and
      --Current Record = the record ID. and the year part of YearMth = current year
      -- There for  while yearmth may be 201412, the new yearMth would be 201501 which is what we want
      where (ID = @Kounter) and (SplitYN = 'split') 
      and (substring(YearMth,1,4) = convert(varchar(4),convert(date,getdate())))
      
      Insert into #rawdata11
      select Type,Department, [Year] ,[orderDate] ,[YearMth],
      case when right([YearMth],2) = '12' then Convert(Varchar(4),DatePart(Year,convert(Date,Getdate())) )  +'01'  else  [YearMth] +1 end
      as NewYearMth ,[SplitYN] ,[revenue] *.30 as Revenue,revenue as OrigRevenue
      from #rawdata10
      --Insert any ‘second parts’ as they will all fall into the calendar / fiscal year
      --and the current year from getdate() = Year part from YearMTH i.e.2015 < 2016
 
      where (ID = @Kounter) and  (SplitYN = 'split') and (substring(YearMth,1,4) <= Convert(Varchar(4),DatePart(Year,convert(Date,Getdate())) ) )
      
   
--If [SplitYN] = 'Nosplit'
      Insert into #rawdata11
      select Type,Department, [Year] ,[orderDate] ,[YearMth] ,[YearMth] as NewYearMth,[SplitYN] ,[revenue]
      ,revenue as OrigRevenue
      from #rawdata10  
      where (ID = @Kounter) and (SplitYN = 'nosplit') and (substring(YearMth,1,4) = convert(varchar(4),convert(date,getdate()))) 
   
set @kounter = @Kounter +1
end
 --select * from #rawdata11
 
select Type,Department
,Sum(month01) as Month01
,Sum(month02) as Month02
,Sum(month03) as Month03
,Sum(month04) as Month04
,Sum(month05) as Month05
,Sum(month06) as Month06
,Sum(month07) as Month07
,Sum(month08) as Month08
,Sum(month09) as Month09
,Sum(month10) as Month10
,Sum(month11) as Month11
,Sum(month12) as Month12
 
from (
 
 
select Type,Department 
,case when NewYearMth = @Month01 then Revenue else 0 end as Month01
,case when NewYearMth = @Month02 then Revenue else 0 end as Month02
,case when NewYearMth = @Month03 then Revenue else 0 end as Month03
,case when NewYearMth = @Month04 then Revenue else 0 end as Month04
,case when NewYearMth = @Month05 then Revenue else 0 end as Month05
,case when NewYearMth = @Month06 then Revenue else 0 end as Month06
,case when NewYearMth = @Month07 then Revenue else 0 end as Month07
,case when NewYearMth = @Month08 then Revenue else 0 end as Month08
,case when NewYearMth = @Month09 then Revenue else 0 end as Month09
,case when NewYearMth = @Month10 then Revenue else 0 end as Month10
,case when NewYearMth = @Month11 then Revenue else 0 end as Month11
,case when NewYearMth = @Month12 then Revenue else 0 end as Month12
 
 
 from #rawdata11)a
 Group By Type,Department

附录2 ( Addenda2 )

 
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
 
CREATE FUNCTION [dbo].[YearMth] ( @Datee date ) 
  RETURNS Varchar(6) 
  AS 
  BEGIN 
  declare @Return  varchar(6)
       select @Return  =   convert(varchar(4),DatePart(Year,@Datee)) + 
     (case when datepart(Month,@Datee) < 10 then '0' + convert(varchar(2),datepart(Month,@Datee)) else
      Convert(varchar(2),datepart(Month,@Datee)) end)  
    
      RETURN(@Return) 
  END 
 
GO

翻译自: https://www.sqlshack.com/sql-server-and-bi-creating-query-for-revenue-projection/

sql server bi

culuo4781

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
sql server bi_SQL Server和BI –创建收入预测查询

sql server bi A few days ago I received an interesting challenge from one of our clients. The lady was attempting to estimate her potential monthly revenue recognition for the fiscal year beginning...
复制链接

扫一扫