| Author |
Topic |
|
mrtweaver
Yak Posting Veteran
67 Posts |
 Posted - 2009-02-28 : 11:21:14
|
| The company I work for is running SQL 2005. I have been reading my Dummies book to become more familiar with the product. I was asked to come up with a way to detect the changes in a column. The column is an INT and it changes from a 0(zero) to a 1(one) and back to 0(zero) at various points thru the day. As you may have guessed since I mentioned the Dummies book I am a real greenhorn when it comes to this. I can using the studio manage my way around and generate some general queries but am no where near the advanced level that I have read in some of these forum postings. So my question is, can a query be written that will check this column of data and will select the entire row of data where the column has changed state?Just so you know the data is in a table and not a view and there is an index to make the queries run more efficient. I just dont see any kind of change of state command within SQL 2005. SO if you could please help. Thanks and have a great day. |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2009-02-28 : 11:27:54
|
| Do you need to know if the state has changed within a given time period, for example , has the state changed in the last hour?Jack Vamvas--------------------http://www.ITjobfeed.com |
 |
|
|
mrtweaver
Yak Posting Veteran
67 Posts |
Posted - 2009-02-28 : 11:38:22
|
| I am not sure if it will get down to the hour duration mark. Basically all that is need is a way, for the duration of the shift, to go thru the data and at every cell in that column where the data changed state, pull that row of data. So if during the course of the shift the column data changed state from a 0 to 1 five time and from a 1 to a 0 five times it would create a view table that would have 10 rows of data in it. Hope this is clear. Like I said I am still a very greenhorn and am trying to learn all the proper terminology so I dont look like a complete moron. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-02 : 09:32:55
|
| what you need seems to be a trigger which captures the changes and populates the changes to an audit/history table. |
|
|
|
mrtweaver
Yak Posting Veteran
67 Posts |
Posted - 2009-03-02 : 15:53:11
|
Well as stated I am still very green when it comes to this Microsoft product. Since I am no where near the level of understanding these triggers and the only way in which I have worked with SQL2005 is thru the GUI Managment studio program, could you assist me with writing this?quote:
Originally posted by visakh16
what you need seems to be a trigger which captures the changes and populates the changes to an audit/history table.
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-03 : 09:02:13
|
| i will. but before that can you give some detail on what changes you want to track, with some sample data and reqd output? |
|
|
|
mrtweaver
Yak Posting Veteran
67 Posts |
Posted - 2009-03-04 : 10:42:28
|
I will try to do my best here to describe what I need.What I want to do is check the status of a column called MECHPG. This column is set as INT. It has two states a zero and a one. It can change a number of times throughout the shift. Where it changes state, either 1 to 0 or 0 to 1, I want to pull that row into my view.Lets say here is some data: MECHPG1 123456789 13333 20 0 0 1 0 0 0 0 0 12:00:01pm 1 123456789 13333 30 0 0 0 0 0 1 0 0 12:01:45pm1 123456789 13333 40 0 0 0 0 0 1 0 0 12:04:15pm1 123456789 13333 50 0 0 0 0 0 1 0 0 12:07:05pm1 123456789 13333 60 0 0 0 0 0 1 0 0 12:10:25pm1 123456789 13333 70 0 0 0 0 0 0 0 0 12:12:12pmNow if this query, proceedure, or trigger was run on this information the result would be:1 123456789 13333 30 0 0 0 0 0 1 0 0 12:01:45pm1 123456789 13333 70 0 0 0 0 0 0 0 0 12:12:12pmBecause these records are where the MECHPG changed state. Hope this helps clear things up.Thanks and have a great day.quote:
Originally posted by visakh16
i will. but before that can you give some detail on what changes you want to track, with some sample data and reqd output?
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-04 : 10:47:56
|
| what deterimes your slot to looked for the change tracking? why you chose start as 12:01:45pm and end as 12:12:12pm? |
|
|
|
mrtweaver
Yak Posting Veteran
67 Posts |
Posted - 2009-03-04 : 21:53:25
|
I do not understand your first question about the slot to look for change. So please define this a little beeter.As for the data choosen, I only choose those values because that is where the MECHPG value change state. I would not care if it choose 1 record back on the end one. I am just use to PLC's which are logic driven and if I did the code in a PLC it would choose the record I outlined. However not being as familiar with SQL I am not sure if it would choose in the same way.quote:
Originally posted by visakh16
what deterimes your slot to looked for the change tracking? why you chose start as 12:01:45pm and end as 12:12:12pm?
|
|
|
|
|
Need Help change of state detection ASAP