SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 SQL Query Problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

The1Ash10
Starting Member

15 Posts

Posted - 09/12/2013 :  11:22:15  Show Profile  Reply with Quote
I know that I am probably not going about this query in the most efficient manner, but I'm working with my limited knowledge to try to accomplish this task, so if you know of a more simplified way of writing this, please let me know.


I have a bunch of income statement accounts that are coming in as year to date values, so I'm creating a temporary table that will sum the values for each account in the current period and and subtract the amounts (by account) in the prior periods to give me a periodic value for each month.

Example: The value for an account in March (3) would subtract the values of that account for February(2). For February (2) it would subtract the year to date value for February from the year to date value from January. You would get theses values by using the following query with a change to the last variable:


Select Time, Account, Entity, BusinessLine, Department, Value 
from dbo.TABLE_NAME
where time Like '201202' 


This is what I have so far:

Create table #TempTable
				(
				Time1 int,
				Account nvarchar(200),
				Entity nvarchar(80),
				BusinessLine nvarchar (80),
				Department nvarchar (200),
				Value decimal (20,5)
				)
				
Go


insert into  #TempTable
			(
			Time1,
			Account,
			Entity,
			Businessline,
			Department,
			Value
			)

Select Time, Account, Entity, BusinessLine, Department, Value 
from dbo.vw_Fact_Join_Writeback 
where time Like '2012%' and Currency='USD' and Value <> 0 and account between '430000' and '899999'

go

Alter Table #TempTable 
ADD "1" int, "2" int, "3" int, "4" int, "5" int,"6" int,
	"7" int, "8" int, "9" int, "10" int, "11" int, "12" int
Go

Update #TempTable
Set "1" = Value
where time1 like '201201'

Update #TempTable
Set "2" = 
(select Value
where time1 like '201202'
) - "1"


go


select * 
from #TempTable
go

Drop table #TempTable




Thanks in advance for the help! I know that I'm making this a lot more difficult than it needs to be.

Edited by - The1Ash10 on 09/12/2013 12:56:58

MuMu88
Aged Yak Warrior

547 Posts

Posted - 09/12/2013 :  12:35:50  Show Profile  Reply with Quote
You may want to try PIVOT()operation as described here:
http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 09/12/2013 :  12:35:50  Show Profile  Reply with Quote
You may want to try PIVOT()operation as described here:
http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/12/2013 :  16:06:10  Show Profile  Reply with Quote
why not calculate the variances by using a subquery approach usig APPLY

see scenario 2

http://visakhm.blogspot.in/2010/01/multipurpose-apply-operator.html

also if its 2012 you can even use LAST VALUE function to get prev month price and then find difference from current value

http://visakhm.blogspot.in/2013/04/demystifying-lastvalue-function-in-sql.html



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000