Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Query Design

Posted on 2014-04-16
12
Medium Priority
?
428 Views
Last Modified: 2014-04-19
I have a database that contains information about object vibrations. Each object vibrates a certain number of times per minute. I’m trying to write a query that displays the data about each object. The current query displays the date, time and vibrations per minute for each object. I’m trying to modify the query so that it also displays the duration (seconds). My attempt to modify the query returns erroneous data.

Object 1 vibrated 5 times in 1 second. Object 2 vibrated one time 2 seconds. Object 3 vibrated 9 times in 3 seconds. I’m running SQL Server 2008 R2. Screenshots of the results of both queries are attached. A screenshot of the database diagram is also attached.

QUERY WITHOUT ERRORS
SELECT
    t.tDate
,	t.tTime
,	max((60/d.Duration) * o.Object_1) AS vib_per_min_obj1
,	max((60/d.Duration) * o.Object_2) AS vib_per_min_obj2
,	max((60/d.Duration) * o.Object_3) AS vib_per_min_obj3
FROM	tblTimes	t
JOIN	tblObjects	o	ON	t.TimeID = o.TimeID
JOIN	tblDurations	d	ON	o.DurationID = d.DurationID
GROUP BY
    t.tDate
,	t.tTime
ORDER BY
    t.tDate
,	t.tTime

Open in new window



Query Without Error Result
QUERY WITH ERRORS
SELECT
    t.tDate
,	t.tTime
,	d.Duration
,	max((60/d.Duration) * o.Object_1) AS vib_per_min_obj1
,	max((60/d.Duration) * o.Object_2) AS vib_per_min_obj2
,	max((60/d.Duration) * o.Object_3) AS vib_per_min_obj3
FROM	tblTimes	t
JOIN	tblObjects	o	ON	t.TimeID = o.TimeID
JOIN	tblDurations	d	ON	o.DurationID = d.DurationID
GROUP BY
    t.tDate
,	t.tTime
,	d.Duration
ORDER BY
    t.tDate
,	t.tTime
,	d.Duration

Open in new window


Query With Errors Result
The database backup is also attached. 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.

Also attached is an image showing the data in the database.

Please help me modify the query so that it also displays the duration (seconds). The query must also be saved as a View.
Data in DatabaseDatabase Diagrambu.txt
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
12 Comments
 
LVL 41

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 680 total points
ID: 40004316
instead of grouping by duration try max(duration) and remove it in the group by.  If you're trying to get one for each object you may want to split duration into 3 seperate columns.
0
 
LVL 13

Accepted Solution

by:
magarity earned 680 total points
ID: 40004493
The hardest obstacle here is the denormalized table design.  Take table tblObjects and collapse the fields for Object1 Object2 Object3 and make them into ObjectID.  Something like this:

Date  Time Duration ObjectID
1/1   1203  1             1
1/2   1305  2             2

Getting rid of the matrix of NULLs for all the objects that aren't being referenced will be a HUGE help in writing your query.  Otherwise imagine the mess it will be when some day there are 25 objects.

The primary key on the new design is probably a combination of date, time, and object.

I would model this like a data warehouse fact table:
DIM_DATE (contains dates only)
DIM_TIME (contains 24 hours in 1 minute intervals)
DIM_OBJECT (contains descriptions of the objects)
FACT_VIBRATION (contains the 3 DIMs keys as PK and then vibration count and duration)

Querying this structure will be much easier.
0
 

Author Comment

by:Mark01
ID: 40004520
Kyle Abrahams and magarity, I appreciate your ideas. I don't have enough experience with SQL to make your recommended changes. Unfortunately, I don't have time to redesign the tables at this time.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Assisted Solution

by:John_Vidmar
John_Vidmar earned 640 total points
ID: 40004671
Careful about whole number division:
1/2 = 0
5/2 = 2
Your formula may fall into that trap if d.Duration is a whole number (int, smallint, bigint):
max((60/d.Duration) * o.Object_1) AS vib_per_min_obj1
Add a decimal to 60:
max((60.00/d.Duration) * o.Object_1) AS vib_per_min_obj1
0
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 40004768
You have 3 objects but you're only using 1 durationID.  Which durationID does it refer to?

You're better off restructuring now.
0
 

Author Comment

by:Mark01
ID: 40005491
Kyle, the attached screenshot show the data entry screen. The Data in Database screenshot above (ID: 28413995) shows the duration that corresponds to each entry. I don't understand your durationID question.
Data Entry Screen
0
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 40006689
I didn't realize only one of the objects would actually be filled in at a time.

If you have some default value for objects rather than null change is not null to != <value>

SELECT
    t.tDate
,	t.tTime
,	coalesce(d1.Duration, d2.Duration ,d3.Duration) Duration
,	max((60/d.Duration) * o.Object_1) AS vib_per_min_obj1
,	max((60/d.Duration) * o.Object_2) AS vib_per_min_obj2
,	max((60/d.Duration) * o.Object_3) AS vib_per_min_obj3
FROM	tblTimes	t
JOIN	tblObjects	o	ON	t.TimeID = o.TimeID
left JOIN	tblDurations	d1	ON	o.DurationID = d.DurationID and o.Object_1 is not null
left JOIN	tblDurations	d2	ON	o.DurationID = d.DurationID and o.Object_2 is not null
left JOIN	tblDurations	d3	ON	o.DurationID = d.DurationID and o.Object_3 is not null
GROUP BY
    t.tDate
,	t.tTime
,	d1.Duration, d2.duration, d3.duration
ORDER BY
    t.tDate
,	t.tTime
,	coalesce(d1.Duration, d2.Duration ,d3.Duration) Duration

Open in new window

0
 

Author Comment

by:Mark01
ID: 40007807
Kyle, I ran your query and received the error shown below. I don't have a default value for objects.

ERROR:

Msg 102, Level 15, State 1, Line 20
Incorrect syntax near 'Duration'.
0
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 40007963
Removed the column name from the order by . . . sorry.

ORDER BY
    t.tDate
,      t.tTime
,      coalesce(d1.Duration, d2.Duration ,d3.Duration)

And I'm not sure but try first

group by
coalesce(d1.Duration, d2.Duration ,d3.Duration)

if not leave it as you have it with d1.Duration, d2.Duration, d3.Duration
0
 

Author Comment

by:Mark01
ID: 40008134
Kyle, I tried the queries shown below and they generated the errors shown below.


QUERY
SELECT
    t.tDate
,	t.tTime
,	coalesce(d1.Duration, d2.Duration ,d3.Duration) Duration
,	max((60/d.Duration) * o.Object_1) AS vib_per_min_obj1
,	max((60/d.Duration) * o.Object_2) AS vib_per_min_obj2
,	max((60/d.Duration) * o.Object_3) AS vib_per_min_obj3
FROM	tblTimes	t
JOIN	tblObjects	o	ON	t.TimeID = o.TimeID
left JOIN	tblDurations	d1	ON	o.DurationID = d.DurationID and o.Object_1 is not null
left JOIN	tblDurations	d2	ON	o.DurationID = d.DurationID and o.Object_2 is not null
left JOIN	tblDurations	d3	ON	o.DurationID = d.DurationID and o.Object_3 is not null
GROUP BY
    t.tDate
,	t.tTime
,	d1.Duration, d2.duration, d3.duration
ORDER BY
    t.tDate
,      t.tTime
,      coalesce(d1.Duration, d2.Duration ,d3.Duration)

Open in new window

------------
ERROR

Msg 4104, Level 16, State 1, Line 10
The multi-part identifier "d.DurationID" could not be bound.
Msg 4104, Level 16, State 1, Line 11
The multi-part identifier "d.DurationID" could not be bound.
Msg 4104, Level 16, State 1, Line 12
The multi-part identifier "d.DurationID" could not be bound.
Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "d.Duration" could not be bound.
Msg 4104, Level 16, State 1, Line 6
The multi-part identifier "d.Duration" could not be bound.
Msg 4104, Level 16, State 1, Line 7
The multi-part identifier "d.Duration" could not be bound.
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
QUERY
SELECT
    t.tDate
,	t.tTime
,	coalesce(d1.Duration, d2.Duration ,d3.Duration) Duration
,	max((60/d.Duration) * o.Object_1) AS vib_per_min_obj1
,	max((60/d.Duration) * o.Object_2) AS vib_per_min_obj2
,	max((60/d.Duration) * o.Object_3) AS vib_per_min_obj3
FROM	tblTimes	t
JOIN	tblObjects	o	ON	t.TimeID = o.TimeID
left JOIN	tblDurations	d1	ON	o.DurationID = d.DurationID and o.Object_1 is not null
left JOIN	tblDurations	d2	ON	o.DurationID = d.DurationID and o.Object_2 is not null
left JOIN	tblDurations	d3	ON	o.DurationID = d.DurationID and o.Object_3 is not null
GROUP BY
    t.tDate
,	t.tTime
,	coalesce(d1.Duration, d2.Duration ,d3.Duration)
ORDER BY
    t.tDate
,      t.tTime
,      coalesce(d1.Duration, d2.Duration ,d3.Duration)

Open in new window

-----------
ERROR


Msg 4104, Level 16, State 1, Line 10
The multi-part identifier "d.DurationID" could not be bound.
Msg 4104, Level 16, State 1, Line 11
The multi-part identifier "d.DurationID" could not be bound.
Msg 4104, Level 16, State 1, Line 12
The multi-part identifier "d.DurationID" could not be bound.
Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "d.Duration" could not be bound.
Msg 4104, Level 16, State 1, Line 6
The multi-part identifier "d.Duration" could not be bound.
Msg 4104, Level 16, State 1, Line 7
The multi-part identifier "d.Duration" could not be bound.
0
 

Author Comment

by:Mark01
ID: 40009135
I forgot to add an important fact about this question.

I am going to use Crystal Reports to design a report that shows something to the effect of:

2-02-2014 at 9:00 a.m. Object 1: 60 vibrations per minute (Duration: 1 second)

The screenshot shows the desired result.

If it's too difficult to rewrite the query to return desired data, I'll close the question and award the points at this time.
Desired Result
0
 

Author Comment

by:Mark01
ID: 40010877
It will probably be easier to write this query using the result from the query in my other open question (ID: 40010225). Thank you, Kyle Abrahams, magarity and  John_Vidmar.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

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 …
In this article, we’ll look at how to deploy ProxySQL.
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…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

636 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