SQL Server 2008 Query

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
Mark01Asked:
Who is Participating?
 
John_VidmarConnect With a Mentor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
Mark01Author Commented:
I'm using SQL Server 2008. The screenshot shows the query results. The Object does not correspond to the correct duration.
Query Results
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Mark01Author Commented:
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
 
David ToddConnect With a Mentor Senior DBACommented:
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
 
Mark01Author Commented:
Thank you, ScottPletcher, John_Vidmar and David Todd.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.