Solved

Query Design

Posted on 2014-04-02
4
314 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 26

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 40

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now