Solved

Query Design

Posted on 2014-04-16
12
408 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
12 Comments
 
LVL 40

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 170 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 170 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
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.

 
LVL 11

Assisted Solution

by:John_Vidmar
John_Vidmar earned 160 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 40

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 40

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 40

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

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.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
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…

776 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