我如何能够从列到列显示预测年数据? [英] How do i can show forecast years data from row into column?
本文介绍了我如何能够从列到列显示预测年数据?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
--------------------
ITEM | YEAR |数量|
--------------------
ITM-A | 2013 | 100 |
ITM-B | 2013 | 200 |
--------------------
如果我想预测2015年的销售数据
----------- -------------------
项目| 2013 | 2014 | 2015 |
------------------------------
项目A | 100 | 110 | 121 | - 每年的数量增加了
项目B |的10% 200 | 220 | 242 | - 前一年数
------------------------------
解决方案
尝试这个,你必须使用动态sql
声明@toyear int = 2016
声明@forcast int = 10
声明@t表(ITEM varchar(50),年int,qty int)
插入到@t
中选择'TM-A'项目,2013年,100个数量
联合全部
选择'TM-B'项目,2013年,200个数量
; CTE1为
(
select * from @t
union all
select b.ITEM,b.years + 1,b.qty +(( @ atcast * b.qty)/ 100)从@ta
内部连接cte1 b在a.ITEM = b.ITEM
和b.years< @toyear
)
选择*从
(select * from cte1)t4
pivot(min(qty))([2013],[2014],[2015],[2016])pvt
Suppose if Item-A's Sale in 2013 is 100 Quantity and I'm expecting 10% of sales growth in next year i.e. in 2014
--------------------
ITEM | YEAR | QTY |
--------------------
ITM-A| 2013 | 100 |
ITM-B| 2013 | 200 |
--------------------
if I want to forecast sale data for up to year 2015
------------------------------
Item | 2013 | 2014 | 2015 |
------------------------------
Item-A | 100 | 110 | 121 |--each year qty incremented by 10% of its
Item-B | 200 | 220 | 242 |--previous year qty
------------------------------
解决方案
try this,you have to use dynamic sql
Declare @toyear int=2016
Declare @forcast int=10
Declare @t table (ITEM varchar(50), years int, qty int)
insert into @t
select 'TM-A' ITEM , 2013 years, 100 qty
union all
select 'TM-B' ITEM , 2013 years, 200 qty
;with CTE1 as
(
select * from @t
union all
select b.ITEM,b.years+1,b.qty+((@forcast*b.qty)/100) from @t a
inner join cte1 b on a.ITEM=b.ITEM
and b.years<@toyear
)
select * from
(select * from cte1 )t4
pivot(min(qty) for years in([2013],[2014],[2015],[2016]))pvt
这篇关于我如何能够从列到列显示预测年数据?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文