Solved

SQL Server 2008 Query

Posted on 2014-03-28
6
458 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
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…

820 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