Complicated for a beginer please help

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Complicated for a beginer please help

Author

Topic

mrtweaver
Yak Posting Veteran

67 Posts

Posted - 2008-11-17 : 09:11:42

I have a historical database this database is 1M+ records. Off of this historical database I have a view that uses a where clause to only show the last 3 days of production. What I am looking to try to do is find an auto increment field in the historical database that I can pull over using a Query into the view. This way I could use the following type query to get the data from the live table where there has been a change in the pagemech column:

select t1.machine, t1.start, t1.mech from livedata t1 left join historical t2 on t1.{?} != t2.{?}

The t1.{?} is the column that is copied from the Historical table into the view

The t2.{?} is the auto incrementing column located in the historical table. This is the one I dont know where it is located or what to use. Someone in another type forum said about using an index, however is Microsoft SQL I can not seem to find out if this field is auto incrementing and how to access it so I can put it in a query to be placed in a view.

Can someone please help.

I am only somewhat familar with MS SQL and the only way I know of doing things is with Management Studio and that is very limited

Using MS SQL 2005.

Here is the query that creates the view:

SELECT * from historical where datediff(d,start,current_timestamp)<3

The data for the historical table is dumped in via automation thru a software product called KepServer and Factory SQL.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-17 : 09:23:50

use COLUMNPROPERTY() function to find out identity column in historical table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-17 : 09:31:32

something like

select COLUMN_NAME, TABLE_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'yourschema'
AND TABLE_NAME='Historical'
and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') =
1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-17 : 09:43:58

In SQL Server 2005

select name from sys.identity_columns
where object_name(object_id)='Historical'

Madhivanan

Failing to plan is Planning to fail

mrtweaver
Yak Posting Veteran

67 Posts

Posted - 2008-11-17 : 12:52:15

I noticed an error in my original posting. Here is what the query should have said:

select t1.machine, t1.start, t1.mech from livedata t1 left join historical t2 on t1.{?} != t2.{?}+1

This way it will compare the {?} from one table to the other with an offset of 1. So if the first ? has a value of 10 and that is from livedata table then the historical would actually be looking at 11. This way I can tell when the data changes in that column.

quote:

Originally posted by visakh16

something like

select COLUMN_NAME, TABLE_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'yourschema'
AND TABLE_NAME='Historical'
and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') =
1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-17 : 13:29:44

Why would you want to LEFT OUTER JOIN on a NOT EQUAL to comparison?

E 12°55'05.63"
N 56°04'39.26"

mrtweaver
Yak Posting Veteran

67 Posts

Posted - 2008-11-17 : 13:40:42

Mechpage is a int, it has two values, a value of 1 means it is active and a value of 0 means it is inactive. As stated the historical table is quite large. As also stated the livedata table only has in it the last 3 days of production. This livedata table sometime is around 10K rows. Now what I want to be able to do is everywhere in the table where the mechpage goes from a 1 to a 0 or where it goes from a 0 to a 1 I want to pull that row of information. So the new table created will contain all rows where a change occured to mechpage. Now if you look at the query I have what it will do is, since it has an offset when the mechpage goes from a 0 to a 1 on this query the data will not be equal and it will pull that row. Same thing on the other end where it goes from a 1 to a 0. My only problem is I dont know how to get some sort of incremental counter where it will number the rows in the Historical table and then copy that information over to the livedata table. There has been talk about using triggers and indexes but I dont have enough information on these. I just started doing databases and SCADA systems, have limited formal knowledge. That is why I ask at forums about my questions. maybe I am using the wrong terminology dont know just trying to get answers. Hope this helps.

quote:
Originally posted by Peso

Why would you want to LEFT OUTER JOIN on a NOT EQUAL to comparison?

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources