连接2个表来计算存储过程中基于周期的方差 [英] Joining 2 tables to calculate the variance on period basis in stored procedure

问题描述

亲爱的专家，



需要通过将两个表格与以下细节相结合来计算差异。



表格预期& 实际



加入应该是期限。

Dear Expert,

Need to calculate variance by combining two tables with following details.

The tables are Expected & Actual

The join should be on period basis.

Table :: Expected

Idno   Amount    Month   Year
-----------------------------
001     500       08     2014
002     600       08     2014
003     400       08     2014

001     500       09     2014
002     600       09     2014
003     400       09     2014

001     500       10     2014
002     600       10     2014
003     400       10     2014

Table :: Actual

Idno   Amount    Month   Year
-----------------------------
001     500       08     2014
002     600       08     2014
003     400       08     2014

001     0         09     2014
002     450       09     2014
003     400       09     2014

001     250       10     2014
002     120       10     2014
003     400       10     2014

如果范围是From :: 08 2014 To :: 09 2014



预期产量：

Such that if the the range is From :: 08 2014 To:: 09 2014

Expected Output:

Idno   Month   Year ||  Expected_Amt  Actual_Amt  Variance
--------------------||------------------------------------
001     08     2014 ||      500         500          0
002     08     2014 ||      600         600          0
003     08     2014 ||      400         400          0

001     09     2014 ||      500          0          500
002     09     2014 ||      600         450         150
003     09     2014 ||      400         400          0

请在存储过程中提供Select语句以完成此操作。



另外在T-SQL中提供Select语句。





谢谢

Please provide the Select statement to accomplish this in a stored procedure.

in addition provide the Select statement in T-SQL.


Thanks

推荐答案

完整解决方案：

Full Solution:

CREATE table Expected
(
	Idno Int,
	Amount decimal, 
	Month int,
	Year int
)

GO

CREATE table Actual
(
	Idno Int,
	Amount decimal, 
	Month int,
	Year int
)

GO

insert into Expected values(001,500,08 ,2014)
insert into Expected values(002,600,08 ,2014)
insert into Expected values(003,400,08 ,2014)
insert into Expected values(001,500,09 ,2014)
insert into Expected values(002,600,09 ,2014)
insert into Expected values(003,400,09 ,2014)
insert into Expected values(001,500,10 ,2014)
insert into Expected values(002,600,10 ,2014)
insert into Expected values(003,400,10 ,2014) 
GO 

insert into Actual values(001,500,08,2014)
insert into Actual values(002,600,08,2014)
insert into Actual values(003,400,08,2014)
insert into Actual values(001,0  ,09,2014)
insert into Actual values(002,450,09,2014)
insert into Actual values(003,400,09,2014)
insert into Actual values(001,250,10,2014)
insert into Actual values(002,120,10,2014)
insert into Actual values(003,400,10,2014)
GO

SELECT e.Idno, e.Month, e.Year, e.Amount AS Exp_Amt, A.Amount AS Act_Amt, (e.Amount - a.Amount) As Variance  
	FROM Expected e INNER JOIN Actual a 
	ON e.Idno = a.Idno and e.Month = a.Month 
	ORDER BY  e.Month

试试这个！....

try this!....

create table #Expected (Idno int identity(1,1),Amount float,Month1 varchar(20),year1 varchar(20))

insert into #Expected values(500,'08','2014')
insert into #Expected values(600,'08','2014')
insert into #Expected values(400,'08','2014')
insert into #Expected values(500,'09','2014')
insert into #Expected values(600,'09','2014')
insert into #Expected values(400,'09','2014')
insert into #Expected values(500,'10','2014')
insert into #Expected values(600,'10','2014')
insert into #Expected values(400,'10','2014')

create table #Actual (Idno int identity(1,1),Amount float,Month2 varchar(20),year2 varchar(20))
insert into #Actual values(500,'08','2014')
insert into #Actual values(600,'08','2014')
insert into #Actual values(400,'08','2014') 
insert into #Actual values(0,'09','2014')
insert into #Actual values(450,'09','2014')
insert into #Actual values(400,'09','2014')
insert into #Actual values(250,'10','2014')
insert into #Actual values(120,'10','2014')
insert into #Actual values(400,'10','2014')

select a.idno,a.month1,a.year1,a.amount,b.amount,(a.amount-b.amount) from #Expected as a inner join #actual as b on a.idno=b.idno where month1 between 08 and 09 and year1 between 2014 and 2014

这篇关于连接2个表来计算存储过程中基于周期的方差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！