Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server 2008 Query

Posted on 2014-03-28
6
Medium Priority
?
507 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 70

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 1000 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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 1000 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

More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

721 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