Solved

Query Design

Posted on 2014-04-16
12
404 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 39

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

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 39

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 39

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Read about achieving the basic levels of HRIS security in the workplace.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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…

757 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

16 Experts available now in Live!

Get 1:1 Help Now