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! 20 3781
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
..
<su******@gmail.com> wrote in message
news:11**********************@g10g2000cwb.googlegr oups.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!
Sorry, there is a primary key, just didn't show it.
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
..
<su******@gmail.com> wrote in message
news:11**********************@u72g2000cwu.googlegr oups.com...
Sorry, there is a primary key, just didn't show it.
(su******@gmail.com) writes: 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.
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, es****@sommarskog.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
<su******@gmail.com> wrote in message
news:11**********************@g10g2000cwb.googlegr oups.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
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? 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!
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.
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
..
<su******@gmail.com> wrote in message
news:11**********************@e56g2000cwe.googlegr oups.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.
Hi Tom, 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.
Thanks
(su******@gmail.com) writes: 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.
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, es****@sommarskog.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
So, does my code meet the requirement?
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<su******@gmail.com> wrote in message
news:11*********************@g10g2000cwb.googlegro ups.com...
Hi Tom, 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.
Thanks
Thanks for the help (and lecture). Tom, forgive my ignorance, but your
code is producing 9 instead of 7.
> 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)
)
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
..
<su******@gmail.com> wrote in message
news:11*********************@t31g2000cwb.googlegro ups.com...
Thanks for the help (and lecture). Tom, forgive my ignorance, but your
code is producing 9 instead of 7.
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.
On 21 Apr 2006 06:12:15 -0700, su******@gmail.com wrote: 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.
Hi sucaba,
Slight modification of Erland's suggestion (it was thus far unclear if
the "first" data point had to be included, since there's no "previous"
value to compare to - Erland apparently chose to exclude it):
SELECT COUNT(*)
FROM @t AS a
LEFT OUTER JOIN @t AS b
ON a.PK = b.PK + 1
WHERE a.Amount > b.Amount
OR b.Amount IS NULL
--
Hugo Kornelis, SQL Server MVP
And what is wrong with entry 9?
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<su******@gmail.com> wrote in message
news:11**********************@i40g2000cwc.googlegr oups.com...
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.
The specs are confusing - and fluid. My guess is if he wants 7 so bad, then
this is the solution:
SELECT 7
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...
Tom Moreau (to*@dont.spam.me.cips.ca) writes: And what is wrong with entry 9?
0.25 < 2.40
or so I guess.
But it's difficult when sucaba.r does not really seem to have a grip
on it him/herself.
Maybe because it's some class assignment, and not a real-world case, who
knows.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
My apology for not making myself clear enough, and yes it's real-world.
Thanks again for all the help guys.
So why is item #1 to be considered, yet items #4 and #9 are bad? Item #1 is
the beginning of one of these reset sequences (since there are no data
before it), yet it is considered to be valid.
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<su******@gmail.com> wrote in message
news:11*********************@i39g2000cwa.googlegro ups.com...
My apology for not making myself clear enough, and yes it's real-world.
Thanks again for all the help guys. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: lawrence |
last post by:
I had some code that worked fine for several weeks, and then yesterday
it stopped working. I'm not sure what I did. Nor can I make out why it
isn't working. I'm running a query that should return 3...
|
by: Qiangning Hong |
last post by:
A class Collector, it spawns several threads to read from serial port.
Collector.get_data() will get all the data they have read since last
call. Who can tell me whether my implementation correct?...
|
by: NicK chlam via DotNetMonster.com |
last post by:
this is the error i get
System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.
at System.Data.Common.DbDataAdapter.Update(DataRow dataRows,
DataTableMapping tableMapping)
at...
|
by: bbernieb |
last post by:
Hi, All,
Is it possible to access a variable inside of a data binding, without the
variable being out of scope?
(Note: On the DataBinder line, I get an error message that says "Name 'i' is...
|
by: Gene |
last post by:
What can I do if I want to get the result using the sql command?
for example,
the select command is "select Name from Employee where StaffID=10"
How to get the "Name"???
dim Name as string
and...
|
by: DC Gringo |
last post by:
I am having a problem reading a simple update to the database. Basically
I'm testing a small change to the pubs database -- changing the price of the
Busy Executive's Database Guide from 19.99 to...
|
by: Joel Dudley |
last post by:
Hello,
I am about to write a set of C functions to be used in an aggregate
function in which the final function performs a calculation on an array
of accumulated text data types stored in a text...
|
by: Rolf Welskes |
last post by:
Hello,
I have an ObjectDataSource which has as business-object a simple array of
strings. No problem.
I have an own (custom) control to which I give the DataSourceId and in the
custom-control...
|
by: Winder |
last post by:
Computer Data Recovery Help 24/7
Data recovering tools and services is our focus. We will recover your
data in a cost effective and efficient manner. We recover all
operating systems and media....
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |