473,385 Members | 2,210 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,385 software developers and data experts.

List when Changes occur


Hi I am a newbie to SQL.

I have a historical list of digatal points listed by time.ie: 3 fields
PointName;Date/Time;State.

I need to return a list of When a specific point chsnges state.

For example a list everytime Point A transitions to State 1.

Any help is appreciated.
--
Posted via http://dbforums.com
Jul 20 '05 #1
1 1551
Assuming your table looks like this:

CREATE TABLE PointStates (pointname CHAR(1), dt DATETIME, state INTEGER NOT
NULL, PRIMARY KEY (pointname,dt))

You can use this query:

SELECT DISTINCT
MIN(B.dt)
FROM PointStates AS A
JOIN PointStates AS B
ON A.pointname = B.pointname
AND A.dt < B.dt
AND A.state<>B.state
GROUP BY A.pointname, A.dt

--
David Portas
------------
Please reply only to the newsgroup
--
Jul 20 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: Jess Austin | last post by:
hi, I like the way that Python does lists, and I love the way it does iterators. But I've decided I don't like what it does with iterators of lists. Lists are supposed to be mutable sequences,...
42
by: Alan McIntyre | last post by:
Hi all, I have a list of items that has contiguous repetitions of values, but the number and location of the repetitions is not important, so I just need to strip them out. For example, if my...
32
by: Clunixchit | last post by:
How can i read lines of a file and place each line read in an array? for exemple; array=line1 array=line2 ...
30
by: Charles Law | last post by:
Here's one that should probably have the sub-heading "I'm sure I asked this once before, but ...". Two users are both looking at the same data, from a database. One user changes the data and...
27
by: Jason Doucette | last post by:
I'm getting an assertion fire from a list iterator being checked against NULL. This did not occur in VC++ 2003 (v7.1). Are there changes that have been made to the STL between these versions that...
7
by: DippyDog | last post by:
My problem is the subject line. Basically, my thinking was that, given class TestSpec1 inherits from class TestBase, since I can do this... Dim tst As TestBase = GetTestObj() Function...
10
by: chadsspameateremail | last post by:
I first should list my background and that is that I'm new to PHP. I would like to be able to have an HTML form where there is a list that you choose from. Depending on what item is chosen in the...
10
by: lpinho | last post by:
Hi all, I have a class (named for the example myObject) that can be of several types (int, string, float, etc), instead of using a object to define it's type I used a generic. public class...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
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...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
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...
0
marktang
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,...
0
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...
0
jinu1996
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 using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.