Solved

SQL Server 2008 Query

Posted on 2014-03-28
6
492 Views
Last Modified: 2014-03-30
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.

Here’s my first attempt to write a query. The result contains null values.

SELECT tblTimes.tDate, tblTimes.tTime, tblObjects.Object_1_Speed, tblObjects.Object_2_Speed,
tblObjects.Object_3_Speed, tblDurations.Duration
FROM tblTimes INNER JOIN
tblObjects ON tblTimes.TimeID = tblObjects.TimeID INNER JOIN
tblDurations ON tblObjects.DurationID = tblDurations.DurationID

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 write a query that does not contain any null values.
Desired_Report Database Diagrambu.txt
QueryResults.csv
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
6 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39962868
Since the durations are different, I think you'll have to use a CASE (or the equivalent in whatever db you're using), something like this:


SELECT
    tblTimes.tDate, tblTimes.tTime,
    MAX(tblObjects.Object_1_Speed) AS Object_1_Speed,
    MAX(tblObjects.Object_2_Speed) AS Object_2_Speed,
    MAX(tblObjects.Object_3_Speed) AS Object_3_Speed,
    MAX(CASE WHEN tblObjects.Object_1_Speed IS NULL THEN 0 ELSE tblDurations.Duration END) AS Duration_1,
    MAX(CASE WHEN tblObjects.Object_2_Speed IS NULL THEN 0 ELSE tblDurations.Duration END) AS Duration_2,
    MAX(CASE WHEN tblObjects.Object_3_Speed IS NULL THEN 0 ELSE tblDurations.Duration END) AS Duration_3    
FROM tblTimes INNER JOIN
tblObjects ON tblTimes.TimeID = tblObjects.TimeID INNER JOIN
tblDurations ON tblObjects.DurationID = tblDurations.DurationID
GROUP BY
    tblTimes.tDate, tblTimes.tTime
0
 

Author Comment

by:Mark01
ID: 39962955
I'm using SQL Server 2008. The screenshot shows the query results. The Object does not correspond to the correct duration.
Query Results
0
 
LVL 11

Accepted Solution

by:
John_Vidmar earned 250 total points
ID: 39964973
Assuming Duration is in minutes:
SELECT	a.tDate
,	a.tTime
,	Dist1		=	ISNULL( b.Object_1_Speed * c.Duration / 60.0, 0 )
,	Dist2		=	ISNULL( b.Object_2_Speed * c.Duration / 60.0, 0 )
,	Dist3		=	ISNULL( b.Object_3_Speed * c.Duration / 60.0, 0 )
FROM	tblTimes	a
JOIN	tblObjects	b	ON	a.TimeID = b.TimeID
JOIN	tblDurations	c	ON	b.DurationID = c.DurationID

Open in new window

0
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 

Author Comment

by:Mark01
ID: 39965448
John_Vidmar, thank you for including the calculation in the query. Before any calculations are performed, the query has to include the object and duration data. The screenshot shows the results of your query.

Here's my attempt to modify your query to include only the object and duration data. Can you correct the error(s) in my query?

SELECT	a.tDate
,	a.tTime
,	Dist1		=	ISNULL( b.Object_1_Speed )
,	Dist2		=	ISNULL( b.Object_2_Speed )
,	Dist3		=	ISNULL( b.Object_3_Speed )
FROM	tblTimes	a
JOIN	tblObjects	b	ON	a.TimeID = b.TimeID
JOIN	tblDurations	c	ON	b.DurationID = c.DurationID

Open in new window

--------------------------
Error:
Msg 174, Level 15, State 1, Line 3
The isnull function requires 2 argument(s).
Your Query Result
0
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 250 total points
ID: 39965647
Hi,

Try this:
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


Regards
  David
0
 

Author Comment

by:Mark01
ID: 39965722
Thank you, ScottPletcher, John_Vidmar and David Todd.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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…

623 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