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
 General SQL Server Forums
 New to SQL Server Programming
 formula for variance in sql
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pchuen
Starting Member

Malaysia
11 Posts

Posted - 05/16/2013 :  23:27:15  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 05/16/2013 :  23:55:54  Show Profile  Reply with Quote
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
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
965 Posts

Posted - 05/17/2013 :  02:22:58  Show Profile  Reply with Quote
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)
Go to Top of Page

pchuen
Starting Member

Malaysia
11 Posts

Posted - 05/17/2013 :  04:37:04  Show Profile  Reply with Quote
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
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
965 Posts

Posted - 05/17/2013 :  05:47:44  Show Profile  Reply with Quote
please don tag me as expert....im not even a novice level in here!
Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
171 Posts

Posted - 05/18/2013 :  12:39:53  Show Profile  Reply with Quote
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.
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 05/19/2013 :  09:28:46  Show Profile  Reply with Quote
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


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;



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/20/2013 :  01:25:47  Show Profile  Reply with Quote
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
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.09 seconds. Powered By: Snitz Forums 2000