Solved

Query Design

Posted on 2014-04-02
4
325 Views
Last Modified: 2014-04-02
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
0
Comment
Question by:Mark01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 50 total points
ID: 39972756
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)
object_description

then a movements table:

movements:

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.
0
 

Author Comment

by:Mark01
ID: 39972816
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?
0
 
LVL 41

Accepted Solution

by:
Sharath earned 450 total points
ID: 39972949
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

0
 

Author Comment

by:Mark01
ID: 39973812
Thank you, Zberteoc and Sharath.
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question