如何计算累积值 [英] how to calculate Cumulative value
问题描述
我想计算累积值
日期Prod_Type Chainage Material_Id QTY_FTD Last_Updated
2013-02-13 1 192 2 120.00 2013- 02-13 13:27:21.660
2013-02-13 1 247 2 560.00 2013-02-13 13:27:21.500
2013-02-13 1 192 2 400.00 2013-02-13 13:27:21.400
2013-02-12 1 192 2 150.00 2013-02-13 13:27:21.320
2013-02 -14 1 192 2 200.00 2013-02-13 13:27:21.100
来自上表192,247的
是Chainage',2'是material_Id',1'是Production_Type',120.00,400.00'是QTY_FTD's
i想要计算QTY_Cumilative即,我将传递Material_Id,Production_Type并且我想要计算来自特定Chainage的Last Last_Updated日期的SUM(QTY_FTD)
所需输出:
Prod_Type Chainage Material_Id QTY_Cumilative
1 192 2 1270
1 247 2 560
create table #cumm_avg
( [日期] datetime ,Prod_Type int , Chainage Int ,Material_Id int ,Qty_Ftd float ,Last_Updated datetime )
insert into #cumm_avg
值
(' 2013-02-13', 1 , 192 , 2 , 120 。 00 ,' 2013-02-13 13:27:21.660'),
( 跨度> 2013-02-13', 1 , 247 , 2 , 560 。 00 ,' 2013-02-13 13:27:21.500'),
(< span class =code-string>' 2013-02-13', 1 , 192 , 2 , 400 。< span class =code-digit> 00 ,' 2013-02-13 13:27:21.400 '),
(' 2013-02-12', 1 , 192 , 2 , 150 。 00 ,' 2013-02-13 13:27:21.320'),
(' 2013-02-14', 1 , 192 , 2 , 200 。 00 ,' 2013-02-13 13:27:21.100')
选择 Prod_Type,Chainage,Material_Id,SUM(Qty_Ftd) Qty_Cummulative
来自 #cumm_avg
group by Prod_Type,Chainage,Material_Id
你好SK,
请查看下面的累积计算示例。
创建 表# tt1(sno int identity ,Amt int )
insert into #tt1 值( 100 )
insert into #tt1 值( 200 )
insert into #tt1 values ( 300 )
插入 进入 #tt1 值( 400 )
选择 * 来自 #tt1
选择 a.sno,a.Amt,Sum (b.Amt) as Cum_Amt 来自#tt1 a cross join #tt1 b
其中 a.sno> = b.sno group by a.sno,a.Amt
order by a.sno,a.Amt
i want to calculate Cumulative value
Date Prod_Type Chainage Material_Id QTY_FTD Last_Updated
2013-02-13 1 192 2 120.00 2013-02-13 13:27:21.660
2013-02-13 1 247 2 560.00 2013-02-13 13:27:21.500
2013-02-13 1 192 2 400.00 2013-02-13 13:27:21.400
2013-02-12 1 192 2 150.00 2013-02-13 13:27:21.320
2013-02-14 1 192 2 200.00 2013-02-13 13:27:21.100
from the above table 192,247 are Chainage''s , 2''s are material_Id''s, 1''s are Production_Type''s , 120.00 , 400.00 ''s are QTY_FTD ''s
i want to calculate the QTY_Cumilative ie,i will pass Material_Id , Production_Type and i want to calculate the SUM(QTY_FTD) from the Last Last_Updated date for a particular Chainage
output required:
Prod_Type Chainage Material_Id QTY_Cumilative
1 192 2 1270
1 247 2 560
create table #cumm_avg ([Date] datetime,Prod_Type int,Chainage Int,Material_Id int,Qty_Ftd float,Last_Updated datetime) insert into #cumm_avg values ('2013-02-13', 1, 192, 2, 120.00,'2013-02-13 13:27:21.660'), ('2013-02-13', 1, 247, 2, 560.00 ,'2013-02-13 13:27:21.500'), ('2013-02-13', 1, 192, 2, 400.00, '2013-02-13 13:27:21.400'), ('2013-02-12', 1, 192, 2, 150.00, '2013-02-13 13:27:21.320'), ('2013-02-14', 1, 192, 2, 200.00, '2013-02-13 13:27:21.100') select Prod_Type,Chainage,Material_Id,SUM(Qty_Ftd) as Qty_Cummulative from #cumm_avg group by Prod_Type,Chainage,Material_Id
Hi SK,
Please find the below sample example for Cumulative Calculation.
Create table #tt1 (sno int identity,Amt int) insert into #tt1 values (100) insert into #tt1 values (200) insert into #tt1 values (300) insert into #tt1 values (400) select * from #tt1 select a.sno,a.Amt,Sum(b.Amt) as Cum_Amt from #tt1 a cross join #tt1 b where a.sno>=b.sno group by a.sno,a.Amt order by a.sno,a.Amt
这篇关于如何计算累积值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!