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.
| Author |
Topic |
|
AlanPBates
Starting Member
34 Posts |
 Posted - 2010-05-12 : 10:54:54
|
| I have 5 tables which contain various types of faults in the manufacturing facilty. I need to display the top 10 results in terms of faults from the 5 tables. Table structure - CREATE TABLE [dbo].[MCP1_CommFlt]( [Fault] [varchar](20) NULL, [Occurred] [datetime] NULL, [Duration] [time](0) NULL, [MCPID] [int] NULL) ON [PRIMARY]There exist 5 such tables. MCP1_PanelFlt, MCP1_MotorFlt, MCP1_Estop, MCP1_PEJam.I need to query all the tables and display results like -FAULT, No Of Occurances, Total Duration.Can someone please help with this code. Also, If I was to extrapolate this structure to 50 such structures - meaning 250 tables, It is necessary since they exist currently such, How will I be able to look for 10 ten faults in the structure -FAULT, No Of Occurances, Total Duration.from the 250 tables.My thought is to create a table to which all faults are inserted by trigger from the various 250 tables.Any ideas ?Thanks, |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-05-12 : 11:23:48
|
| you need to use UNION ALL to get queries from all the tables and then use GROUP BY on fault------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
AlanPBates
Starting Member
34 Posts |
Posted - 2010-05-12 : 11:47:59
|
| With a insertion rate of 10 rows every 5 secs for 24X7 and 360 days .. the queries will become slow .. |
|
|
|
|
|
|
|
|
Displaying query from 5 tables