Detecting change in datastream

I want to run a simple query that  in theory looks like this;
create a temporary table
then
Select  firstlabno ,secondlabno, thirdlabno from sometable order by firstlabno ,secondlabno

then run through this dataset and detect when a change occurs in the value  firstlabno
 then do something to  thirdlabno

In crystal reports there is something like 'on change of data'

milkwoodAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ThommyCommented:
To detect data changes in a table field, you have to poll it with a TTimer.

Save the initial field value and compare it...
0
milkwoodAuthor Commented:
I didn't mean to be in the Delphi zone. I need the query to run in SQL Server 2008 R2
0
jimyXCommented:
Do you mean to create a temp table and then later on match the main table against the temp and detect the changes in the firstlabno column?
0
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

ThommyCommented:
Can you please describe your problem more detailed...
0
Geert GOracle dbaCommented:
why temporary table ? use lag
this you the lines where the thirdlabno changes from 1 to the next:

select * from (
  select  firstlabno ,secondlabno, thirdlabno, lag(thirdlabno) over (order by firstlabno, secondlabno) next_thirdlabbo
  from sometable
)
where thirdlabno <> next_thirdlabno
order by firstlabno ,secondlabno
0
LelikInsideCommented:
You should use SQL triggers for that
0
Martyn SpencerSoftware Developer / Linux System Administrator / Managing DirectorCommented:
You mention using Crystal reports and I imagine from this you want to report on some data and keep related information together. For this you should use report groups. Unfortunately I am not a Crystal report user so cannot give you the exact steps you need but you will ideally want to create a group for firstlabno and you may then need to perform some manipulation on thirdlabno.

I am not sure what the columns relate to but if you could describe your problem in a little more detail, I can probably help a little more.
0
Geert GOracle dbaCommented:
argh ... you're on mssql server 2008.

it's a pity microsoft is lagging so much in good functionality ... :(
0
ThommyCommented:
Use Update-Trigger to detect a field value change in a table on MSSQL server...

CREATE TRIGGER SomeTable_U on SomeTabe FOR UPDATE AS
    IF UPDATE(firstlabno)  --check if field firstlabno has changed
    BEGIN
        ...
    END

For more details on SQL server triggers please check following link:

Exploring SQL Server Triggers
http://msdn.microsoft.com/en-us/magazine/cc164047.aspx
0
Martyn SpencerSoftware Developer / Linux System Administrator / Managing DirectorCommented:
I think people are approaching answering in two ways. If you want to change the value of a column in the database, then using an update trigger in the database is most certainly the way to change one column based on the value of another.

If however, you are wanting to change the appearance of some data on a Crystal (or other) report, grouping is the way to do it.

It may help if you clarify which it is that you want to do. Do you want to update one column based on the value of another when a row of data is changed? Or, do you want to present related information together on a report?
0
milkwoodAuthor Commented:
There is something not getting through on this question

I have a table  records that I want to change to increment in groups
simply, I have  three fields Labno, timeinseconds, incrementno
I Have
labno,       timeinseconds,    incrementno
111111       12345                      null
111111       12346                      null
111111       12347                      null
22222       12345                      null
22222       12346                      null
22222       12347                      null
33333       12345                      null
33333       12346                      null
etc

I want

labno,       timeinseconds,    incrementno
111111       12345                    1
111111       12346                    2
111111       12347                    3
22222       12345                    1
22222       12346                    2
22222       12347                    3
33333       12345                    1
33333       12346                    2
0
Martyn SpencerSoftware Developer / Linux System Administrator / Managing DirectorCommented:
There are numerous approaches that you could take here. One would be to use a trigger to determine the highest incrementno that is already set for a set labno/timeinseconds pairing and then set the next incrementno to that value plus one. I am an Oracle guy, so cannot really advise on how to create the trigger in SQL server.

The problem with this approach is that it does not scale to multiple possible updates to the same labno/timeinseconds pair. If you have two processes trying to add a new record to the same pair, both could end up trying to update the incrementno to the same value if they happen truly concurrently. To avoid this, you would really need to consider locking the other related records before performing the calculation of the next value and then carrying out the update.

Oracle, for example, will try to help you if you start trying to update values based on other values that could be changed by another process and will raise an exception. I don't know if this would happen with SQL server, but I guess it would. The problem is that setting a value based on other values in other rows needs to be controlled using locks.

Another approach would be to create a stored procedure to handle the update. Here you could work around the potential update issue mentioned above.

Personally, I would set incrementno based on a sequence (probably an autoincrement field in SQL Server) so that it behaves nicely in a concurrent environment. This would mean that your table would look something like:

labno,       timeinseconds,    incrementno
111111       12345                    1
111111       12346                    2
111111       12347                    3
22222       12345                    4
22222       12346                    5
22222       12347                    6
33333       12345                    7
33333       12346                    8

Now, if you need to show the increment no as 1, 2, 3, etc for each pair, simply subtract min(incrementno) - 1 from the incrementno when it comes to displaying the information.

Again, this setting could be handled by a simple update trigger.
0
Martyn SpencerSoftware Developer / Linux System Administrator / Managing DirectorCommented:
One thing that has come to mind based on your comment is that you may be wanting to carry out the update in bulk after a series of records has been created. Is this the case? If so, I have some more comments to add.
0
milkwoodAuthor Commented:
This is once only thing, it is a bulk process
0
Ephraim WangoyaCommented:

Execute this sql statement


;WITH CTE
AS
(
  select labno, timeinseconds, ROW_NUMBER() OVER (PARTITION BY LABNO ORDER BY timeinseconds) ROWNO
  FROM YOURTABLE
)
update yourtable
set incrementno = (select ROWNO 
                   from CTE B
                   where B.labno = yourtable.labno
                   and B.timeinseconds = yourtable.timeinseconds)

Open in new window

0
Ephraim WangoyaCommented:

if you really want to use a temp table which I dont think is neccessary you can do the following
create #TEMP TABLE(labno INT, timeinseconds INT, incrementno INT)

;WITH CTE
AS
(
	select labno, timeinseconds, ROW_NUMBER() OVER (PARTITION BY LABNO ORDER BY timeinseconds) ROWNO
	FROM YOURTABLE
)

insert into #TEMP
select labno, timeinseconds, ROWNO

update yourtable
set incrementno = (select ROWNO 
                   from CTE B
                   where B.labno = yourtable.labno
                   and B.timeinseconds = yourtable.timeinseconds)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
milkwoodAuthor Commented:
Thanks, you got it
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.