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 2000 Forums
 Transact-SQL (2000)
 Sum of Variance

Author  Topic 

Ken Blum
Constraint Violating Yak Guru

383 Posts
Posted - 2005-06-16 : 07:39:54
I have the following data in a table...

DateTime Column Numeric Column
6/14/05 08:00:00 5
6/14/05 09:00:00 8
6/14/05 10:00:00 12
6/14/05 11:00:00 24
6/14/05 12:00:00 0
6/14/05 13:00:00 10
6/14/05 14:00:00 2
6/14/05 15:00:00 16
6/14/05 17:00:00 22
6/14/05 18:00:00 33

How can I get the sum of the variances? (24-5)+(10-0)+(33-2) = 60

TIA!

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2005-06-16 : 07:50:52
How did you define variances?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Ken Blum
Constraint Violating Yak Guru

383 Posts
Posted - 2005-06-16 : 09:42:31
This data comes from a PLC which is a counter which can be reset at any point by a user.
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2005-06-16 : 09:53:00
I asked you on what basis you calculated?


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2005-06-16 : 09:57:54
quote:
Originally posted by madhivanan

I asked you on what basis you calculated?


Madhivanan

Failing to plan is Planning to fail


more specifically: why do you show (24-5) and (10-0) what is the connection between each of the pair?

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."
Go to Top of Page

Ken Blum
Constraint Violating Yak Guru

383 Posts
Posted - 2005-06-16 : 09:59:55
I want to calculate the sum of the variances for June 14th. It starts out with 5 and goes up to 24, so that's a variance of 19. Then it starts out at 0 and goes up to 10, so that's a variance of 10. Then it starts out at 2 and goes up to 33, so that's a variance of 31. The total of the variances of 19+10+31 = 60. So the sum of the variances for June 14th is 60.
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2005-06-16 : 10:21:05
so the you are interested in the difference of a climb.

if a number is less than the one before, it restarts the counter (so to speak)


Create Table #myTable (rDate datetime, rVal int)
Insert Into #myTable
Select '6/14/05 08:00:00', 5 Union All 
Select '6/14/05 09:00:00', 8 Union All 
Select '6/14/05 10:00:00', 12 Union All 
Select '6/14/05 11:00:00', 24 Union All 
Select '6/14/05 12:00:00', 0 Union All 
Select '6/14/05 13:00:00', 10 Union All 
Select '6/14/05 14:00:00', 2 Union All 
Select '6/14/05 15:00:00', 16 Union All 
Select '6/14/05 17:00:00', 22 Union All 
Select '6/14/05 18:00:00', 33

Select
	ttlVariance = sum(vStop - vStart)
From
	(
	Select 
		rDate,
		vStart = rVal,
		vStop = (Select top 1 rVal from (Select rDate, rVal From #myTable A Where isnull((select top 1 rVal From #myTable Where rDate > A.rDate Order By rDate),rVal) <= rVal) Y where rDate > Z.rDate order by rDate)
	From
		(
		Select rDate, rVal
		From #myTable A
		Where isnull((select top 1 rVal From #myTable Where rDate < A.rDate Order By rDate desc),rVal) >= rVal
		) Z
	) A
	
Drop Table #myTable


Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2005-06-16 : 10:27:14
Seventhnight,

You proved that you are Genius

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Ken Blum
Constraint Violating Yak Guru

383 Posts
Posted - 2005-06-16 : 10:34:02
SeventhNight: Totally cool. Thanks.

So, just what happened on the Seventh Night anyway?
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2005-06-16 : 10:52:38
how bout this:

declare @t table (dt datetime, num int)
insert into @t
select '6/14/05 08:00:00', 5 union all
select '6/14/05 09:00:00', 8 union all
select '6/14/05 10:00:00', 12 union all
select '6/14/05 11:00:00', 24 union all
select '6/14/05 12:00:00', 0 union all
select '6/14/05 13:00:00', 10 union all
select '6/14/05 14:00:00', 2 union all
select '6/14/05 15:00:00', 16 union all
select '6/14/05 17:00:00', 22 union all
select '6/14/05 18:00:00', 33

select *
from @t

select sum(num)
from (
(
select 	num
from 	@t t1 
where 	num > isnull((select top 1 num from @t where dt > t1.dt), 0))
union all
(select num*-1
from 	@t t1 
where 	num <= isnull((select top 1 num from @t where dt <= t1.dt), 0))
) t


could't help myself


Go with the flow & have fun! Else fight the flow
Go to Top of Page

Ken Blum
Constraint Violating Yak Guru

383 Posts
Posted - 2005-06-16 : 10:58:01
Ooooh, I like that even better! Even my simple mind can understand this one!

Thanks.
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2005-06-16 : 10:59:46
Mladen, tell me Is sql programming your hobby?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2005-06-16 : 11:01:33
yes it is...
plus it looks weel on my resumé...
it's turning into an addiction nowadays...

Go with the flow & have fun! Else fight the flow
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2005-06-16 : 11:11:27
pbbbbt


Select ttlVariance = sum(Variance)
From
	(
	Select variance = rVal - isnull((Select top 1 rVal From #myTable where rDate < A.rDate Order BY rDate desc),rVal) 
	From #myTable A
	) Z
Where variance > 0



TAG!!! You're It!

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2005-06-16 : 11:19:10

ROTFLOL!!





i guess that's as far as it goes...

Go with the flow & have fun! Else fight the flow
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2005-06-16 : 11:29:57
quote:
could't help myself




Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2005-06-16 : 11:37:49
emmm something weird...
execution plans give me that your query is "costlier" than mine... weird...

Go with the flow & have fun! Else fight the flow
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2005-06-16 : 11:50:00
using table variable for both?? what if you indexed the temp table?

also, don't you need to specify order bys on your 'select top 1..' subqueries??

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2005-06-16 : 11:57:54
quote:
Originally posted by spirit1

emmm something weird...
execution plans give me that your query is "costlier" than mine... weird...

Go with the flow & have fun! Else fight the flow


This came out about 4x cheaper than your [pppbbbbt] again 


Create Table #myTable (rDate datetime, rVal int)
Insert Into #myTable
Select '6/14/05 08:00:00', 5 Union All 
Select '6/14/05 09:00:00', 8 Union All 
Select '6/14/05 10:00:00', 12 Union All 
Select '6/14/05 11:00:00', 24 Union All 
Select '6/14/05 12:00:00', 0 Union All 
Select '6/14/05 13:00:00', 10 Union All 
Select '6/14/05 14:00:00', 2 Union All 
Select '6/14/05 15:00:00', 16 Union All 
Select '6/14/05 17:00:00', 22 Union All 
Select '6/14/05 18:00:00', 33

Declare @ttlVariance int,
	@lastVal int

Select 
	@ttlVariance = isnull(@ttlVariance,0) + case when rVal > isnull(@lastVal,rVal) then rVal - @lastVal else 0 end,
	@lastVal = rVal
From #myTable

Select @ttlVariance

Drop Table #myTable


Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2005-06-16 : 11:58:52
this is what i run.
i forgot the order by before...
i get 10% batch cost for insert, 90% for yours and 0% for mine... do you get the same?

declare @t table (dt datetime, num int)
insert into @t
select '6/14/05 08:00:00', 5 union all
select '6/14/05 09:00:00', 8 union all
select '6/14/05 10:00:00', 12 union all
select '6/14/05 11:00:00', 24 union all
select '6/14/05 12:00:00', 0 union all
select '6/14/05 13:00:00', 10 union all
select '6/14/05 14:00:00', 2 union all
select '6/14/05 15:00:00', 16 union all
select '6/14/05 17:00:00', 22 union all
select '6/14/05 18:00:00', 33

Select ttlVariance = sum(Variance)
From
	(
	Select variance = num - isnull((Select top 1 num From @t where dt < A.dt Order BY dt desc), num)	
	From @t A
	) Z
Where variance > 0

select sum(num) as varianceSum
from (
(
select 	num
from 	@t t1 
where 	num > isnull((select top 1 num from @t where dt > t1.dt order by dt asc), 0))
union all
(select num*-1
from 	@t t1 
where 	num <= isnull((select top 1 num from @t where dt <= t1.dt order by dt asc), 0))
) t


Go with the flow & have fun! Else fight the flow
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2005-06-16 : 12:00:16
what a hell is a [pppbbbbt]????

Go with the flow & have fun! Else fight the flow
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site