Query Design

I have a database that contains information about the distance that three objects have travelled. I’m trying to write a query that displays the date and time and distance data for each object.

Object 1 moved at 5 mph for 1 minute. Object 2 moved at 1 mph for 2 minutes. Object 3 moved at 9 mph for 3 minutes. Mph stands for miles per hour.

The database backup and query results are attached. Also attached are screen shots of the database diagram and the desired results for the report.

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.

Please help me rewrite the query so that no row contains any zero values and the results are in a single row for each time period.
Desired ResultsQUERY:
SELECT	a.tDate
,	a.tTime
,	Dist1		=	ISNULL( b.Object_1_Speed, 0 )
,	Dist2		=	ISNULL( b.Object_2_Speed, 0 )
,	Dist3		=	ISNULL( b.Object_3_Speed, 0 )
FROM	tblTimes	a
JOIN	tblObjects	b	ON	a.TimeID = b.TimeID
JOIN	tblDurations	c	ON	b.DurationID = c.DurationID 

Open in new window

Query ResultsDatabase Diagrambu.txt
Who is Participating?
SharathConnect With a Mentor Data EngineerCommented:
try this.
SELECT	a.tDate
,	convert(time,a.tTime) tTime
,	Dist1		=	MAX(ISNULL( b.Object_1_Speed, 0 ))
,	Dist2		=	MAX(ISNULL( b.Object_2_Speed, 0 ))
,	Dist3		=	MAX(ISNULL( b.Object_3_Speed, 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)

Open in new window

ZberteocConnect With a Mentor Commented:
This design is not very good. Times for instance, you have a date column and a time column but both are of datetime type. If you want to keep them separately then you can use date and time types and not datetime. However separating them is not a good idea because you need to put them back if you want to compare 2 full dates or if you want  calculate difference.

It doesn't make sense to have a duration table and a time table. Like I said, if you have the times you can calculate the duration using difference between start and stop moments.

I would use an object table like:

objectID int,  (PK)

then a movements table:


movementID int, (PK)
objectID int, (FK to object table)
movement_start_time datetime,
movement_end_time datetime

Or you coul have movement_start and duration. Depending what is the parameter that you know/get.
Mark01Author Commented:
Zberteoc, I appreciate your comments about the design. Until I have time to redesign the tables, I have to prepare some reports.

Do you have any suggestions about how I can write some queries to prepare the reports?
Mark01Author Commented:
Thank you, Zberteoc and Sharath.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.