Report working for one table, not many...

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

Analysis Server and Reporting Services (2008)

Report working for one table, not many...

Author

Topic

Jasper
Starting Member

2 Posts

Posted - 2012-06-22 : 10:41:07

Hey all, brand new to the forums but I have a question!

Essentially, I have a table called "Log" in which there is a column called Date when a Log is received. The server SHOULD received a log every 5 minutes, but often does not due to communications errors/delay that are a necessary part of the system it is working with.

I am wanting to create a report to see what percentage of logs have arrived. This wasn't hard, using a datediff, and two calculated fields for the expected/percentage.

The problem I am having (correct me if this is in the wrong section!) is that there is a table for each different Sensor that is received a Log (Named Sensor1, Sensor2, Sensor3..etc), and I want to calculate this percentage for EACH sensor. I know I can extract the names of all the tables using something like select * from sys.objects where name like 'Sensor%', but is there an easy way to concatenate these results and have a report with received, expected, percentage for some 60 sensors?

Thanks for any and all advice!

Jasper
Starting Member

2 Posts

Posted - 2012-06-22 : 10:42:38

I should also mention that the number of Sensors is subject to change, up to an eventual maximum of 200 or so, but if it were possible to do this on a dynamic number of tables and not have to specify the number of Sensors, that would be ideal

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-22 : 23:37:17

I would do it like follows in procedure

1. Add a step to populate an intermediate table to capture details of all sensor log tables in db and store it along with sensor names

2. Aggregate the data from intermediate table based on sensor details to calculate percentage fields

ie something like


CREATE PROCEDURE GetSensorAggregate
AS

CREATE TABLE #SensorDetails
(
SensorName varchar(100),
.... other columns

)


INSERT #SensorDetails
EXEC sp_msforeachtable'
if ''?'' LIKE ''%Sensor%''
select ''?'',count(*)
from ?
'

SELECT SensorName,
... percentage calculations
FROM #SensorDetails
GO

GROUP BY SensorName

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources