formula for variance in sql - 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

General SQL Server Forums

New to SQL Server Programming

formula for variance in sql

Author

Topic

pchuen
Starting Member

11 Posts

Posted - 2013-05-16 : 23:27:15

Hi All,

I have the data(as per attached). Has anyone have any clue to the solution to get the difference(as shown in diff1 and diff2 column) in a separate column in sql with grouping?
basically,
diff 1= 6272 - 4196=2076
diff 2= 6272 - 3597=2675

year agency state swurrf concat janqty febqty diff1 diff2
2012 RTL JHR ACT RTLJHR 3597 5335
2013 RTL JHR BGT2013 RTLJHR 4196 3507
2013 RTL JHR ACT RTLJHR 6272 3101 2076 2675
2013 RTL MLK BGT2013 RTLMLK 1451 1210
2013 RTL MLK ACT RTLMLK 381 1663
2012 RTL PLS ACT RTLPLS 300 371
2012 RTL SGR ACT RTLSGR 2293 2793
2012 RTL KTN ACT RTLKTN 1808 2611
2012 RTL PHG ACT RTLPHG 720 1364

MUCH APPRECIATED.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-16 : 23:55:54

can you explain the rule behind this? Is it always last janqty - prevjanqty and lastjanqty - prevfebqty?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-05-17 : 02:22:58

this 1 looks ugly....


declare @foo table(
years int, agency varchar(3), state varchar(3), swurrf varchar(7), janqty int, febqty int)
insert into @foo
select
2012, 'RTL', 'JHR',	'ACT', 3597, 5335 union all select
2013, 'RTL', 'JHR',	'BGT2013', 4196, 3507 union all select
2013, 'RTL', 'JHR',	'ACT', 6272, 3101  union all select
2013, 'RTL', 'MLK',	'BGT2013', 1451, 1210 union all select
2013, 'RTL', 'MLK',	'ACT', 381, 1663 union all select
2012, 'RTL', 'PLS',	'ACT', 300, 371 union all select
2012, 'RTL', 'SGR',	'ACT', 2293, 2793 union all select
2012, 'RTL', 'KTN',	'ACT', 1808, 2611 union all select
2013, 'RTL', 'JHR', 'ACT', 123, 123 union all select
2012, 'RTL', 'PHG',	'ACT', 720, 1364;

select
	a.*
,	ABS(CAST(dbo.fnParseString(ROW_NUMBER() over (partition by agency, state, swurrf order by (select 1)), ',', jan.horizontal) AS INT) - CAST(dbo.fnParseString(ROW_NUMBER() over (partition by agency, state, swurrf order by (select 1)) + 1, ',', jan.horizontal) AS INT))
,	ABS(CAST(dbo.fnParseString(ROW_NUMBER() over (partition by agency, state, swurrf order by (select 1)), ',', feb.horizontal) AS INT) - CAST(dbo.fnParseString(ROW_NUMBER() over (partition by agency, state, swurrf order by (select 1)) + 1, ',', feb.horizontal) AS INT))
from @foo a
cross apply
(select ',' + cast(janqty as varchar(100)) from @foo b where a.agency = b.agency and a.state = b.state and a.swurrf = b.swurrf order by (select 1) desc for xml path(''))jan(horizontal)
cross apply
(select ',' + cast(febqty as varchar(100)) from @foo b where a.agency = b.agency and a.state = b.state and a.swurrf = b.swurrf order by (select 1) desc for xml path(''))feb(horizontal)

pchuen
Starting Member

11 Posts

Posted - 2013-05-17 : 04:37:04

dear waterduck,
you sound like an expert in SQL scripting. The above script looks very complicated to me :(..nevertheless, let me try if i can put this script into running and sees if anything comes out of it. thank you so much for taking the effort to answer my doubts . appreciate it. will update you again if i am successful :P

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-05-17 : 05:47:44

please don tag me as expert....im not even a novice level in here!

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2013-05-18 : 12:39:53

Msg 4121, Level 16, State 1, Line 16
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fnParseString", or the name is ambiguous.

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-19 : 09:28:46

Here is another way to get what you need if you have SQL Server 2012:
You may have to modify the grouping and ordering in the query as per your requirements
[CODE]

declare @foo table(
years int, agency varchar(3), state varchar(3), swurrf varchar(7), janqty int, febqty int)
insert into @foo
select
2012, 'RTL', 'JHR', 'ACT', 3597, 5335 union all select
2013, 'RTL', 'JHR', 'BGT2013', 4196, 3507 union all select
2013, 'RTL', 'JHR', 'ACT', 6272, 3101 union all select
2013, 'RTL', 'MLK', 'BGT2013', 1451, 1210 union all select
2013, 'RTL', 'MLK', 'ACT', 381, 1663 union all select
2012, 'RTL', 'PLS', 'ACT', 300, 371 union all select
2012, 'RTL', 'SGR', 'ACT', 2293, 2793 union all select
2012, 'RTL', 'KTN', 'ACT', 1808, 2611 union all select
2013, 'RTL', 'JHR', 'ACT', 123, 123 union all select
2012, 'RTL', 'PHG', 'ACT', 720, 1364;

SELECT [years], [agency], [state], [swurrf], janqty, febqty,
(CASE WHEN LAG(janqty, 1, 0) OVER(partition by years, agency, swurrf order by years, agency, swurrf) <> 0 THEN
(janqty - LAG(janqty, 1, 0) OVER(partition by years, agency, swurrf order by years, agency, swurrf)) ELSE 0 END) as Lagby1,
(CASE WHEN LAG(janqty, 2, 0) OVER(partition by years, agency, swurrf order by years, agency, swurrf) <> 0 THEN
(janqty - LAG(janqty, 2, 0) OVER(partition by years, agency, swurrf order by years, agency, swurrf)) ELSE 0 END) as Lagby2
from @foo order by years, agency, swurrf;

[/CODE]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-20 : 01:25:47

quote:
Originally posted by Rasta Pickles

Msg 4121, Level 16, State 1, Line 16
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fnParseString", or the name is ambiguous.

Thats because function was not created

you can find function definition here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

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