• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 432
  • Last Modified:

Query Design

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
Mark01
Asked:
Mark01
3 Solutions
 
Kyle AbrahamsSenior .Net DeveloperCommented:
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
 
magarityCommented:
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
 
Mark01Author Commented:
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
John_VidmarCommented:
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
 
Kyle AbrahamsSenior .Net DeveloperCommented:
You have 3 objects but you're only using 1 durationID.  Which durationID does it refer to?

You're better off restructuring now.
0
 
Mark01Author Commented:
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
 
Kyle AbrahamsSenior .Net DeveloperCommented:
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
 
Mark01Author Commented:
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
 
Kyle AbrahamsSenior .Net DeveloperCommented:
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
 
Mark01Author Commented:
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
 
Mark01Author Commented:
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
 
Mark01Author Commented:
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now