Joining 2 tables to calculate the variance on period basis in stored procedure

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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

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

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

in addition provide the Select statement in T-SQL.

Thanks

Posted 3-Jan-15 6:51am

Member 10744248

Updated 3-Jan-15 7:18am

Richard MacCutchan

v3

Add a Solution

Comments

Richard MacCutchan 3-Jan-15 13:19pm

I would suggest reading http://mattgemmell.com/what-have-you-tried/, and showing what you have done to resolve this, and where you need help.

[no name] 3-Jan-15 13:59pm

Ok sir, will start to work on it asap. Any other work in Queue?

ZurdoDev 3-Jan-15 16:26pm

Where are you stuck?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Er. Puneet Goel · Answer 1 · 2015-01-06T01:27:00

Full Solution:

SQL

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

AngelMy · Answer 2 · 2015-01-06T01:56:00

try this!....

SQL

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')

SQL

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

Joining 2 tables to calculate the variance on period basis in stored procedure

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0