Table to Table with sum - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Table to Table with sum

Author

Topic

AlanPBates
Starting Member

34 Posts

Posted - 2010-04-27 : 15:38:29

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 shor the the output is the sum of number of Faults and the Sum of Duration.

I the values updated in the Table 2 as they are inserted row by row into the Table 1.

Any help would be welcome.

Thanks,

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2010-04-27 : 16:04:27

Please let me know if there's a better way:

select fault, count(fault), convert(time, cast(sum(datediff(hh, '0:00:00',duration)) as char(2))+':'+cast(sum(datediff(mi, '0:00:00',duration))%60 as char(2))) from @t1
group by fault

AlanPBates
Starting Member

34 Posts

Posted - 2010-04-28 : 09:09:17

Hanbingl,

Tks for your solution. Its good for 1 table. I neglected to say that there were 200 tables like Table 1. What I am trying to do is accumulate all the faults into 1 table - eg Table 2 and read the top 20 faults to report from that ONE table.

I simply gave 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 extraploate that code to the other tables. My guess is an After insert trigger.

Thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-28 : 10:36:07

do you mean write an after insert trigger on each of 20 tables? how frequently you want this aggregation to happen?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AlanPBates
Starting Member

34 Posts

Posted - 2010-04-28 : 10:43:24

visakh16,

There is data in about 200 tables [It was necessary to have 200] - Believe me. Data is input at a frequency of about 1 row every 5 secs for 6 hours. The aggregation is used for reportingon a Web Page.

Thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-28 : 10:55:52

nope. my question is how frequently you want the aggregation to happen. if its a typically datawarehousing type of reports, usually the summation will be done only once per day by tracking changes over a day

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AlanPBates
Starting Member

34 Posts

Posted - 2010-04-28 : 11:05:17

This being the backend of a reporting application - it will be typically viewed 3 times a day minimum.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-28 : 11:14:05

so i think it may be better for you do this periodically by means of sql job rather than putting 20 triggers

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AlanPBates
Starting Member

34 Posts

Posted - 2010-04-28 : 11:16:35

I get your question - I would want to view the aggregation instantly. Besdies being viewed in a report at a minimum of 3 times, the web forms also have current data being displayed. So, everytime a fault occurs, we trend some of the data and also have a visual counter for current faults.

That is why I was thinking on lines of a After Insert trigger.

AlanPBates
Starting Member

34 Posts

Posted - 2010-04-28 : 11:18:37

See my last post .. I added that we need a current visual on the screens with the numbers - so the aggregation will need to be instant.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-28 : 11:33:10

do you mean you show count values in webpage in realtime and need to change?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AlanPBates
Starting Member

34 Posts

Posted - 2010-04-28 : 11:44:09

Yes !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources