I have a database that contains information about object vibrations. Each object vibrates a certain number of times per minute. I have a query that displays the correct data about each object. However, it does not display the duration for each object. I have a View that does not display the correct data about each object.
Both the query and View must display the date, time, duration and vibrations per second for each object.
, convert(time,a.tTime) tTime
, Dist1 = MAX(ISNULL( b.Object_1, 0 ))
, Dist2 = MAX(ISNULL( b.Object_2, 0 ))
, Dist3 = MAX(ISNULL( b.Object_3, 0 ))
FROM tblTimes a
JOIN tblObjects b ON a.TimeID = b.TimeID
JOIN tblDurations c ON b.DurationID = c.DurationID
GROUP BY a.tDate,convert(time,a.tTime)
MAX(ISNULL(b.Object_1, 0)) AS Dist1,
MAX(ISNULL(b.Object_1, 0)) AS Dist2,
MAX(ISNULL(b.Object_3, 0)) AS Dist3
dbo.tblTimes AS a
dbo.tblObjects AS b ON a.TimeID = b.TimeID
dbo.tblDurations AS c ON b.DurationID = c.DurationID
Object 1 vibrated 5 times in 1 second. Object 2 vibrated one time 2 seconds. Object 3 vibrated 9 times in 3 seconds. I’m running SQL Server 2008 R2. Screenshots of the results of the Query and the View are attached. Also attached are screenshots of the database diagram and tables.
The database backup is also attached. I had to change the extension of the backup file to .txt in order to upload it to EE. Just change the extension back to .bu.
Also attached is an image showing the data in the database.
Please help me rewrite the View so that it returns accurate data as well as the duration for each object.