Sum Accumulating Data

[sucaba.r@gmail.com]

I've got a table that contains a column of accumulating uptime data
that looks similar to this:

239.13
239.21
239.30
239.38
239.46
239.55
0.35
0.44
0.53
0.60
0.68
0.78
0.85
0.93

I need to SUM the data up the point where the data gets reset (the next
row is less than the preceeding row). Then I start the SUM again until
data gets reset.

Thanks in advance for help!

[Tom Moreau]

Re: Sum Accumulating Data

Without a primary key, little can be done. The data aren't stored in any
particular order, so as far as SQL Server is concerned, the list of values
you have given us is no different from

0.35
0.44
0.53
0.60
0.68
0.78
0.85
0.93
239.13
239.21
239.30
239.38
239.46
239.55


--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<sucaba.r@gmail .com> wrote in message
news:1145023509 .190804.201060@ g10g2000cwb.goo glegroups.com.. .
I've got a table that contains a column of accumulating uptime data
that looks similar to this:

239.13
239.21
239.30
239.38
239.46
239.55
0.35
0.44
0.53
0.60
0.68
0.78
0.85
0.93

I need to SUM the data up the point where the data gets reset (the next
row is less than the preceeding row). Then I start the SUM again until
data gets reset.

Thanks in advance for help!

[sucaba.r@gmail.com]

Re: Sum Accumulating Data

Sorry, there is a primary key, just didn't show it.

[Tom Moreau]

Re: Sum Accumulating Data

DO you also have date/time information? What determines the actual sequence
of the data? Without posting the DDL, we are left to guess.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<sucaba.r@gmail .com> wrote in message
news:1145041823 .515095.100340@ u72g2000cwu.goo glegroups.com.. .
Sorry, there is a primary key, just didn't show it.

[Erland Sommarskog]

Re: Sum Accumulating Data

(sucaba.r@gmail .com) writes:[color=blue]
> I've got a table that contains a column of accumulating uptime data
> that looks similar to this:
>
> 239.13
> 239.21
> 239.30
> 239.38
> 239.46
> 239.55
> 0.35
> 0.44
> 0.53
> 0.60
> 0.68
> 0.78
> 0.85
> 0.93
>
> I need to SUM the data up the point where the data gets reset (the next
> row is less than the preceeding row). Then I start the SUM again until
> data gets reset.[/color]

You want something like this:

239,13
239,21 478,34
239,3 717,64
239,38 957,02
239,46 1196,48
239,55 1436,03
0,35 0,35
0,44 0,79
0,53 1,32
0,6 1,92
0,68 2,6
0,78 3,38
0,85 4,23
0,93 5,16

If the values are accumulated sums, it seems funny to sum them again.

Since I don't want to solve the wrong problem, I suggest that you post

o The CREATE TABLE statement for your table (possibly simplified).
o INSERT statements with sample data.
o The desired output given the sample.

That is likely to give you a tested solution. Whereas from what you
have posted now, at best could give you guesses.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Greg D. Moore \(Strider\)]

Re: Sum Accumulating Data


<sucaba.r@gmail .com> wrote in message
news:1145023509 .190804.201060@ g10g2000cwb.goo glegroups.com.. .[color=blue]
> I've got a table that contains a column of accumulating uptime data
> that looks similar to this:
>
> 239.13
> 239.21
> 239.30
> 239.38
> 239.46
> 239.55
> 0.35
> 0.44
> 0.53
> 0.60
> 0.68
> 0.78
> 0.85
> 0.93[/color]

As others have suggested, DDL will help.

However, based on what I just saw for row_numbers in SQL 2005, this may be a
great application for their use.

So, what version of SQL are you running?

[color=blue]
>
> I need to SUM the data up the point where the data gets reset (the next
> row is less than the preceeding row). Then I start the SUM again until
> data gets reset.
>
> Thanks in advance for help!
>[/color]

[sucaba.r@gmail.com]

Re: Sum Accumulating Data

Sorry for the confusion guys. I was misinformed about what I was
looking for in the table. Given the same type of data:

1 - 239.21
2 - 239.55
3 - 240.30
4 - 0.35
5 - 0.44
6 - 0.53
7 - 1.20
8 - 2.40
9 - 0.25

I need to know how many rows before the following row is less than the
previous (in this example, a total of 5). I'm trying to find the number
of entries (could be days, weeks) before a computer is reset, and SUM
them for a total number of occurences (days, weeks, whatever). So for
this I'd sum rows 1,2,3,7,8 for a total of 5.
Does this help?

Thanks again.

[Tom Moreau]

Re: Sum Accumulating Data

Your spec is both confusing and inconsistent. The groups your data have
show progressions of:

3
5
1

The following code will show that:

declare @t table
(
PK int primary key
, Amount dec (6, 2) not null
)

insert @t values (1, 239.21)
insert @t values (2, 239.55)
insert @t values (3, 240.30)
insert @t values (4, 0.35)
insert @t values (5, 0.44)
insert @t values (6, 0.53)
insert @t values (7, 1.20)
insert @t values (8, 2.40)
insert @t values (9, 0.25)

select
FromPK
, count (*)
from
(
select
(
select top 1
b.PK
from
@t b
where
b.PK <= a.PK
and b.Amount <= a.Amount
order by
b.PK, b.Amount desc

) FromPK
from
@t a
) x
group by
FromPK

However, you seem to think that the progressions are:

3
2

Why is that?

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<sucaba.r@gmail .com> wrote in message
news:1145374926 .232395.309570@ e56g2000cwe.goo glegroups.com.. .
Sorry for the confusion guys. I was misinformed about what I was
looking for in the table. Given the same type of data:

1 - 239.21
2 - 239.55
3 - 240.30
4 - 0.35
5 - 0.44
6 - 0.53
7 - 1.20
8 - 2.40
9 - 0.25

I need to know how many rows before the following row is less than the
previous (in this example, a total of 5). I'm trying to find the number
of entries (could be days, weeks) before a computer is reset, and SUM
them for a total number of occurences (days, weeks, whatever). So for
this I'd sum rows 1,2,3,7,8 for a total of 5.
Does this help?

Thanks again.

[sucaba.r@gmail.com]

Re: Sum Accumulating Data

Hi Tom,
[color=blue]
>From looking at the table, the system ran for three days before it was[/color]
reset (rows 1-3). It then ran for 4 days (rows 4-7) for a total of 7
(not 5 as I indicated above). So I'd need to capture 7 as the total.

Thanks

[Erland Sommarskog]

Re: Sum Accumulating Data

(sucaba.r@gmail .com) writes:[color=blue]
> From looking at the table, the system ran for three days before it was
> reset (rows 1-3). It then ran for 4 days (rows 4-7) for a total of 7
> (not 5 as I indicated above). So I'd need to capture 7 as the total.[/color]

To be perfectly honest, you will never get any help with your queries,
if you don't have a grip of the business requirements.

Here is a query based on Tom's table, but it returns 6 and not 7, because
the first row is not counted:

select count (*)
from @t a
JOIN @t b ON a.PK = b.PK + 1
WHERE a.Amount > b.Amount




--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Tom Moreau]

Re: Sum Accumulating Data

So, does my code meet the requirement?

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<sucaba.r@gmail .com> wrote in message
news:1145448102 .311646.16480@g 10g2000cwb.goog legroups.com...
Hi Tom,
[color=blue]
>From looking at the table, the system ran for three days before it was[/color]
reset (rows 1-3). It then ran for 4 days (rows 4-7) for a total of 7
(not 5 as I indicated above). So I'd need to capture 7 as the total.

Thanks

[sucaba.r@gmail.com]

Re: Sum Accumulating Data

Thanks for the help (and lecture). Tom, forgive my ignorance, but your
code is producing 9 instead of 7.

[Alexander Kuznetsov]

Re: Sum Accumulating Data

> Here is a query based on Tom's table, but it returns 6 and not 7, because the first row is not counted

If there are gaps in PK, it gets slightly more exciting:

select count(*) from #t t1 join #t t2
on t1.PK < t2.PK
and t1. Amount < t2.amount
where not exists(
select 1 from #t t3
where (t1.PK < t3.PK) and (t3.PK < t2.PK)
)

[Tom Moreau]

Re: Sum Accumulating Data

Now, I am even more confused. What's wrong with entry #8? What
disqualifies it? You say that it ran from 4 - 7, though 8 is also part of
the same progression.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<sucaba.r@gmail .com> wrote in message
news:1145541923 .733297.75130@t 31g2000cwb.goog legroups.com...
Thanks for the help (and lecture). Tom, forgive my ignorance, but your
code is producing 9 instead of 7.

[sucaba.r@gmail.com]

Re: Sum Accumulating Data

I too am confused. :-) I'll try and explain what I mean. Here's the
table data again, notated on the right with asterisks that count as
legitimate data points:

1 - 239.21 *
2 - 239.55 *
3 - 240.30 *
4 - 0.35
5 - 0.44 *
6 - 0.53 *
7 - 1.20 *
8 - 2.40 *
9 - 0.25

So the uptime query should return 7, as the machine was reset at
location 4 and 9.

Sorry for the confusion.