Posted by Tom Moreau on 10/02/79 11:45

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

[Back to original message]