SQL Query Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

SQL Query Problem

Author

Topic

The1Ash10
Starting Member

15 Posts

Posted - 2013-09-12 : 11:22:15

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.

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-09-12 : 12:35:50

You may want to try PIVOT()operation as described here:
http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-09-12 : 12:35:50

You may want to try PIVOT()operation as described here:
http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-12 : 16:06:10

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources