Hi Mickey,
I understand what you mean but the narrow format allows the flexibility of adding new data points without having to modified the database schema. In our application we allow the users to select the datapoints to trend by Plant, Line, MCC … since the datapoint id is link to other tables allowing us populate tree controls showing the points available for them to select and safe as templates to load dynamically. We also interface to other databases to merge quality results with process data and the used of this datapoint ids are handy. I know with flexibility comes complexity.
I wish I could find an easier solutions, because yes narrow can be a pain since we have to dynamically generate crosstab queries to populate our trends.
[quote=“MickeyBob”]jdelgado,
The IA folks will probably correct me if I’m wrong but there is no way to guarantee an identical time stamp amongst different groups. The only way to guarantee the identical time stamp is to have the tags in the same standard group.
If you need to combine the logged values from multiple standard groups/tables you can use joins with “loose” time criteria such as this (assuming you’re using MS SQL Server):
SELECT t1.t_stamp, col1, col2, ..., colN FROM tablename1 AS t1 INNER JOIN tablename2 AS t2 ON DATEDIFF(ss, t1.t_stamp, t2.t_stamp) < 5
If you have to join many tables, the join is going to get very long and very cumbersome very quickly.
With your layout, you’ll have to do a self join, something along this route:
SELECT t1.t_stamp, t1.col1, t2.col1, t3.col1 FROM tablename AS t1 INNER JOIN tablename AS t2 ON DATEDIFF(ss, t1.t_stamp, t2.t_stamp) < 5 INNER JOIN tablename AS t3 ON DATEDIFF(ss, t1.t_stamp, t3.t_stamp) < 5
This obviously gets very cumbersome very quickly too. You may also need to add some additional indexes on t_stamp to have acceptable query times.
I know that you’re not using the standard group layout but you might want to consider changing your architecture to use the standard groups.
I had used some other competing products that use a similar database schema to yours before I started using FSQL and my natural inclination was to continue along that vein. However, I quickly made the switch over to a “wide” table structure and haven’t regretted it. For most of my purposes, it so much easier to work with the standard group schema than with the “narrow” schema.[/quote]