?
Solved

Query Design

Posted on 2014-04-02
4
Medium Priority
?
326 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 200 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 1800 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

777 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