Solved

SQL Server 2008 Query

Posted on 2014-03-28
6
441 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:ScottPletcher
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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Read about achieving the basic levels of HRIS security in the workplace.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
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…

920 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

14 Experts available now in Live!

Get 1:1 Help Now