Is it possible by sql query?

[voiD]

Hi,

I have to count value changes in a recordset and, to tell the truth, I can not find out how to do it with sql query. Anyway, is it possible? Unfortunately, I have to not simply count that how many different values are in a field, but how many times has changed the value of that field. e.g.: 1,1,2,2,3,3,1,2,... - in this example there are three different values and four value changes - and that is what I would like to count with an sql query, if it is possible.
I could be very grateful for any small tips!

Many thanks in advance!

VoiD

 

[WayneRyan]

voiD,

You can very easily get the counts for each value:

Select SomeField, Count(*) From YourTable Group By SomeField.

To count how many changes, you'll have to traverse a recordset that you
have sorted by some criteria such as a date field.

Wayne

 

[SQL_Hell]

Hi there,

I think you need to create an additional table to store the column name and the number of changes, then use an update trigger on the main table to force an update or insert into the new changes table.

The trigger would look at bit like this

CREATE TRIGGER updater
ON mytable
FOR UPDATE
AS

update changes set numberofchanges = numberofchanges +1
where coumn = @column
GO

 

[pdx_man]

I don't know if I would do an additional table, but rather have a field in the same table that was incremented using a Trigger. If you had to capture different counts for different fields, you could use a field for each, but if it were for several, I would use one field and use a psuedo bit-mapping, if you will.

Field1 = 1st 5 digits
Field2 = next 5 digits
Field3 = next 5 digits
:
:

So you would then have:

000120000200523 ...

where Field1 had 12 updates, Field2 had 2 updates, Field3 had 523 updates ...

Then in your Trigger you would have:

IF UPDATE(Field1)
SET UpdtCntField = CONVERT(char(5),CONVERT(INT,LEFT(UpdtCntField,5) + 1)) + SUBSTRING(UpdtCntField, 6, 10)

IF UPDATE(Field2)
SET UpdtCntField = LEFT(UpdtCntField,5) + CONVERT(char(5),CONVERT(INT,SUBSTRING(UpdtCntField,6, 5) + 1)) + SUBSTRING(UpdtCntField, 10, 5)

IF UPDATE(Field3)
SET UpdtCntField = LEFT(UpdtCntField,10) + CONVERT(char(5),CONVERT(INT,SUBSTRING(UpdtCntField,11, 5) + 1))
:

You get the idea.

 

[suvarna]

You can use query inside query(Subquery) for calculating the number of distinct values like the following
select count(select distinct <fieldname> from <tablename>) from <tablename>
For the next task write a stored procedure
goto first record
copy the fieldvalue to a variable
move to next record
check the fieldvalue with the variable
if there is a change
increment the counter

 

[SQL_Hell]

I don't know if I would do an additional table, but rather have a field in the same table that was incremented using a Trigger. If you had to capture different counts for different fields, you could use a field for each, but if it were for several, I would use one field and use a psuedo bit-mapping, if you will.

Field1 = 1st 5 digits
Field2 = next 5 digits
Field3 = next 5 digits
:
:

So you would then have:

000120000200523 ...

where Field1 had 12 updates, Field2 had 2 updates, Field3 had 523 updates ...

Then in your Trigger you would have:

IF UPDATE(Field1)
SET UpdtCntField = CONVERT(char(5),CONVERT(INT,LEFT(UpdtCntField,5) + 1)) + SUBSTRING(UpdtCntField, 6, 10)

IF UPDATE(Field2)
SET UpdtCntField = LEFT(UpdtCntField,5) + CONVERT(char(5),CONVERT(INT,SUBSTRING(UpdtCntField,6, 5) + 1)) + SUBSTRING(UpdtCntField, 10, 5)

IF UPDATE(Field3)
SET UpdtCntField = LEFT(UpdtCntField,10) + CONVERT(char(5),CONVERT(INT,SUBSTRING(UpdtCntField,11, 5) + 1))
:

You get the idea.

I dont agree adding the fields to the existing table is a good idea, by doing that you are breaking the 3rd normal form rule in normalisation and ultimately you will have a table with many nulls in it because not all fields will be updated

 

[pdx_man]

I am assuming that not fields would need to be monitored for updates. ie table has 20 fileds and only 3 of them would be monitored for updates. The process should only update if a monitored field is updated. If you have them stored in a separate table you do run the risk of having the table locked with multiple users and processes making updates. There are valid reasons to break the rules of normalization, and for active transactions, this would be a good reason. Performance could be affected by pulling this into another table.

The UpdtCntField field should be set with a defualt value of 000000000000000... then use an update and insert trigger. It would then show an initial value 00001 when the row is initially inserted and then the subsequent updates.