April 28, 2010 at 8:06 am
Hi,
I have a table - Table 1 which has the following fields -
[Fault] [varchar](20) NULL,
[Occurred] [datetime] NULL,
[Duration] [time](0) NULL,
[MCPID] [int] NULL
values inserted are something like -
Flt00, 04/27/2010 15:00, 2:10, 1
Flt01, 04/27/2010 15:01, 1:25, 1
Flt01, 04/27/2010 15:02, 2:05, 1
Flt00, 04/27/2010 15:03, 1:00, 1
Flt01, 04/27/2010 15:04, 2:10, 1
I need an output in another table - Table 2 which has the following fields -
[Fault] [varchar](20) NULL,
[Occurances] [int] NULL,
[DTDuration] [time](0) NULL
The output looks like -
Flt00, 2, 3:10, 1
Flt01, 3, 5:40, 1
In short the the output is the sum of number of Faults and the Sum of Duration.
The values should be updated in the Table 2 as they are inserted row by row into the Table 1.
There are 200 tables like Table 1. What I am trying to do is accumulate all the faults into Table 2 and read the top 20 faults to report from that ONE table.
I simply give an example of Table 1 & Table 2 because I was going into too many details. But if we can select or insert into the table 2 from Table 1, I would simply extrapolate that code to the other tables. My guess is an After insert trigger.
Any help would be welcome.
Thanks,
April 28, 2010 at 8:56 am
Alan, don't put the summary results into a table. Always create a VIEW, which gets the data you want on demand instead...the view would be correct at all times, and not need to be updated the way a summary table would if the details changed.
I'm not sure what you wanted to do with the duration; the TIME datatype cannot be summed, so you want the min/max?
something like this is what you want to do:
CREATE TABLE Table1(
[Fault] [varchar](20) NULL,
[Occurred] [datetime] NULL,
[Duration] [time](0) NULL,
[MCPID] [int] NULL )
--INSERT INTO Table1
SELECT 'Flt00','04/27/2010 15:00', '2:10', 1 UNION ALL
SELECT 'Flt01','04/27/2010 15:01', '1:25', 1 UNION ALL
SELECT 'Flt01','04/27/2010 15:02', '2:05', 1 UNION ALL
SELECT 'Flt00','04/27/2010 15:03', '1:00', 1 UNION ALL
SELECT 'Flt01','04/27/2010 15:04', '2:10', 1
GO
CREATE VIEW VW_Table1 AS
SELECT
[Fault],
COUNT([Occurred]) AS [Occurances],
MAX([Duration]) AS [DTDuration]
FROM Table1
GROUP BY [Fault]
GO
select * from VW_Table1
Lowell
April 28, 2010 at 9:08 am
can you change the "duration' column to be an integer with the number of minutes or seconds or something?
or do it inline int he view?:
CREATE VIEW vw_Table1 AS
SELECT
[Fault],
COUNT([Occurred]) AS [Occurances],
sum(datediff(ss,0,[Duration])) AS [DTDuration]
FROM Table1
GROUP BY [Fault]
Lowell
April 28, 2010 at 9:11 am
Lowell,
Tks for your reply. Table 1 is one of 200 similar tables which get data from different sources. This data I intend putting into a table - to get a report of the top 20 faults contained in the 200 tables. My intention was to get 1 table working with the appropriate code and then I will duplicate that code for the other 200 tables.
Thanks,
April 28, 2010 at 9:16 am
good; just create a superview then, that contains everything you need, and select from that;
CREATE VIEW vw_SuperView AS
SELECT
[Fault],
COUNT([Occurred]) AS [Occurances],
sum(datediff(ss,0,[Duration])) AS [DTDuration]
FROM Table1
GROUP BY [Fault]
UNION ALL
SELECT
[Fault],
COUNT([Occurred]) AS [Occurances],
sum(datediff(ss,0,[Duration])) AS [DTDuration]
FROM Table2
GROUP BY [Fault]
UNION ALL
SELECT
[Fault],
COUNT([Occurred]) AS [Occurances],
sum(datediff(ss,0,[Duration])) AS [DTDuration]
FROM Table3
GROUP BY [Fault]
Lowell
April 28, 2010 at 9:48 am
Lowel,,
So far it works. Now if there are updates to the table 1 - which we anticipate at about 1 row per 5 secs for upto 18 hours a day. How do I update the View ?
April 28, 2010 at 9:52 am
ahh...exactly my point... a view never needs to be updated. you might think of it as self-updating.
technically, it is a saved query, that is run on demand.
if you run it right now, it gives the results based on the underlying data.
changes can occur for hours or days, no problem.
when you actually query the view again, hours later, the query is rerun right then,and any updates are automatically reflected in the results. no manual updates are required, compared to the original way you were thinking, where a static table would need the new changes that occured every 5 seconds or something.
AlanPBates (4/28/2010)
Lowel,,So far it works. Now if there are updates to the table 1 - which we anticipate at about 1 row per 5 secs for upto 18 hours a day. How do I update the View ?
Lowell
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply