?
Solved

Query Design

Posted on 2014-04-02
4
Medium Priority
?
329 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
  • 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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

850 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