Solved

Query Design

Posted on 2014-04-03
22
302 Views
Last Modified: 2014-04-10
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 query must display the date, time and vibrations per minute for each object.

Object 1 vibrated 5 times in 1 second. Object 2 vibrated one time 2 seconds. Object 3 vibrated 9 times in 3 seconds.

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 displays the date, time and vibrations per minute for each object.

QUERY:
SELECT	a.tDate
,	convert(time,a.tTime) Time
,	Object1		=	MAX(ISNULL( b.Object_1 * c.Duration / 60.0, 0 ))
,	Object2		=	MAX(ISNULL( b.Object_2 * c.Duration / 60.0, 0 ))
,	Object3		=	MAX(ISNULL( b.Object_3 * c.Duration / 60.0, 0 ))
FROM	tblTimes	a
JOIN	tblObjects	b	ON	a.TimeID = b.TimeID
JOIN	tblDurations	c	ON	b.DurationID = c.DurationID
GROUP BY a.tDate,convert(time,a.tTime)

Open in new window

Query ResultDatabase Diagrambu.txt
0
Comment
Question by:Mark01
  • 8
  • 5
  • 3
  • +3
22 Comments
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
What is the expected result?
0
 

Author Comment

by:Mark01
Comment Utility
The attached image shows the expected result. The expected result is for the Object1, Object2, and Object3 columns to display the vibrations per minute for each object.
Expected Result
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
What is the output of your query?
0
 

Author Comment

by:Mark01
Comment Utility
Hi Sharath,

Here's the query and the query output:

QUERY:

SELECT      a.tDate
,      convert(time,a.tTime) Time
,      Object1            =      MAX(ISNULL( b.Object_1 * c.Duration / 60.0, 0 ))
,      Object2            =      MAX(ISNULL( b.Object_2 * c.Duration / 60.0, 0 ))
,      Object3            =      MAX(ISNULL( b.Object_3 * c.Duration / 60.0, 0 ))
FROM      tblTimes      a
JOIN      tblObjects      b      ON      a.TimeID = b.TimeID
JOIN      tblDurations      c      ON      b.DurationID = c.DurationID
GROUP BY a.tDate,convert(time,a.tTime)
Query Output
Note that this query only contains a partial calculation. It does not calculate the vibrations per minute for each object.
0
 
LVL 26

Expert Comment

by:Alan Warren
Comment Utility
Hi mark01,

Not really sure this is what you are after, but there is a 63 minutes difference between the two timing samples. So you should be dividing by 63 not 60 to return Vibrations / Minute.

Perhaps something like this, which would currently only return one row but will return more rows when more time samples are added to the table, each row being calculated using the time difference in minutes since the previous time sampling
SELECT     
		 T1.tDate
		,CONVERT(time, T1.tTime) AS Time
		,MAX(ISNULL(O.Object_1 * D.Duration / convert(decimal(5,2),datediff(minute,CONVERT(time, T2.tTime),CONVERT(time, T1.tTime))), 0)) AS Object1
		,MAX(ISNULL(O.Object_2 * D.Duration / convert(decimal(5,2),datediff(minute,CONVERT(time, T2.tTime),CONVERT(time, T1.tTime))), 0)) AS Object2
		,MAX(ISNULL(O.Object_3 * D.Duration / convert(decimal(5,2),datediff(minute,CONVERT(time, T2.tTime),CONVERT(time, T1.tTime))), 0)) AS Object2
FROM         tblTimes AS T1 INNER JOIN
                      tblObjects AS O ON T1.TimeID = O.TimeID INNER JOIN
                      tblDurations AS D ON O.DurationID = D.DurationID INNER JOIN
                      tblTimes AS T2 ON T1.TimeID = T2.TimeID + 1

GROUP BY T1.tDate,convert(time,T1.tTime)

Open in new window

Returns: Q_28404589
Respectfully yours,
Alan
0
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 80 total points
Comment Utility
I think what Sharath was asking in the first post was what results you _want_ to see.  For example, your query apparently gives you .45 for Object3 on the first row.  That is presumably not the result that you want.  What should it be?

 Maybe it's just me, but your example seems a bit confusing.

 Object_1, Object_2 and Object_3 are presumably all in one row in tblObjects.  Otherwise, why have 3 columns?  But your example seems to be giving them 3 different durations, 1 second, 2 seconds and 3 seconds.

 If the values for Object_1, Object_2 and Object_3 are all in the same row, and the duration comes from tblDurations.Duration, how do you get a different duration for each column?

 It might help if you could post some sample data and the exact results that you want to get from that data.


 Now, having said all of that, if Duration is the duration in seconds, and you want to convert that to minutes, you'd theoretically want to multiply by (60 / Duration).  For example, (b.Object_1 * (60 / c.Duration))

 James
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
Unable to use the attached file.
What is needed is:
1- Few records of original data from an entity, which is either one table, or a join of few tables.
2- Expected output.
3- Your current query
4- The current output.
0
 

Author Comment

by:Mark01
Comment Utility
Here's some additional information.

Here's the 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 have to calculate the vibrations per minute for each object. Think of the above data as samples. In a period of 1 second, Object 1 vibrated 5 times. This was a 1 second sample. Since Object 1 vibrated 5 times in 1 second, the query has to be used to calculate how many times Object 1 would have vibrated in one minute if the sample was for 60 seconds. In other words, the query has to be used to calculate how many times Object 1 would have vibrated in one minute.

If the above explanation is not clear, please let me know.


Alan, do not assume that my calculation is correct. I'll test your query the first chance I get.

James0628, I'll respond to your questions the first chance I get.

hnasr, I'll upload another database backup the first chance I get.
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
If using MSSQL upload script to create the database. Not every setting may have the relevant tools.

Data sample example:
I have 3 tables, and when joined produce the following entity:
entity1 (f1, f2, f3, f4, f5)

1- Few records of original data from an entity.
f1 f2 f3 f4 f5
1  2   1  2  1
1  2  2  2  2
1  2  3  2  1

2- Expected output.
1  2  6  6  4
I want to add the values for each of the columns f3, f4, and f5

3- Your current query
Select f1, f2, sum(f3), count(4), count(f5)
4- The current output.
1  2  6  3  3

What's wrong with the query?
Here it is obvious to replace count by sum.
0
 

Author Comment

by:Mark01
Comment Utility
Here's another backup.
bu.txt
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:Mark01
Comment Utility
hnasr, I tested the query and it should return 60 vibrations per minute for the 1-02-14 data (in backup). It does not.
0
 

Author Comment

by:Mark01
Comment Utility
I've attached some images that assume the following facts.

Object 1 vibrated 1 time in 1 second. Object 2 vibrated 1 time in 1 second. Object 3 vibrated 1 time in 1 second. The query should calculate how many times all three objects would have vibrated in one minute. The answer for all three objects is 60.

My query from ID 28404589 above is shown below. The attached image (Query Results) shows two query results. The top result shows the current result. The bottom image shows the result that should occur after the calculation is performed.

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

QUERY:
SELECT	a.tDate
,	convert(time,a.tTime) Time
,	Object1		=	MAX(ISNULL( b.Object_1 * c.Duration / 60.0, 0 ))
,	Object2		=	MAX(ISNULL( b.Object_2 * c.Duration / 60.0, 0 ))
,	Object3		=	MAX(ISNULL( b.Object_3 * c.Duration / 60.0, 0 ))
FROM	tblTimes	a
JOIN	tblObjects	b	ON	a.TimeID = b.TimeID
JOIN	tblDurations	c	ON	b.DurationID = c.DurationID
GROUP BY a.tDate,convert(time,a.tTime)

Open in new window

Query ResultsData in Database
0
 

Author Comment

by:Mark01
Comment Utility
Alan, your query in ID: 39987825 does not return 60 vibrations per minute for the 1-02-14 (9:00 a.m.) data. James0628, does my example in ID: 39990431 make sense?
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
OK, it looks like your 3 objects (Object_1, Object_2 and Object_3) are actually in separate rows.  FWIW, that would imply that you don't need 3 separate columns (you could have one Object column with an object # column), but whatever.

 Did you try the suggestion from my previous post, to change your calculations and replace (c.Duration / 60.0) with (60 / c.Duration) ?

 James
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
Managed to read the database.
This info was expected from you: The following is the entity records from joining the 3 tables, using this query:
use EE_040914_Calc_2;
go
SELECT	a.tDate
,	a.tTime, c.Duration
,	Object_1	
,	Object_2	
,	Object_3	
FROM	tblTimes	a
JOIN	tblObjects	b	ON	a.TimeID = b.TimeID
JOIN	tblDurations	c	ON	b.DurationID = c.DurationID

Open in new window

tDate      tTime      Duration      Object_1      Object_2      Object_3
2014-01-01 00:00:00.000      1899-12-30 12:03:00.000      1      5      NULL      NULL
2014-01-01 00:00:00.000      1899-12-30 12:03:00.000      2      NULL      1      NULL
2014-01-01 00:00:00.000      1899-12-30 12:03:00.000      3      NULL      NULL      9
2014-01-01 00:00:00.000      1899-12-30 13:06:00.000      3      2      NULL      NULL
2014-01-01 00:00:00.000      1899-12-30 13:06:00.000      2      NULL      3      NULL
2014-01-01 00:00:00.000      1899-12-30 13:06:00.000      3      NULL      NULL      7
2014-01-02 00:00:00.000      1899-12-30 09:00:00.000      1      1      NULL      NULL
2014-01-02 00:00:00.000      1899-12-30 09:00:00.000      1      NULL      1      NULL
2014-01-02 00:00:00.000      1899-12-30 09:00:00.000      1      NULL      NULL      1

Now complete:
The required output. Text, not image.
How to calculate this output? Explain how to achieve that manually.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
>>"OK, it looks like your 3 objects (Object_1, Object_2 and Object_3) are actually in separate rows"

the issue of table design has been raised on earlier questions;
currently the tables are not designed well and this latest image reinforces that viewpoint
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 300 total points
Comment Utility
despite the poor table design, if I understand the question, you are seeking "vibrations per minute" for each of objects.

>>"Object 1 vibrated 5 times in 1 second. Object 2 vibrated one time 2 seconds. Object 3 vibrated 9 times in 3 seconds."
From that then:

    5 vibrations in 1 second = ((60/1) * 5) = 300 vibrations per minute
   
    1 vibration in 2 seconds = ((60/2) * 1) = 30 vibrations per minute
   
    9 vibrations in 3 seconds = ((60/3) * 9) = 180  vibrations per minute

*** the second row of the results below matches  these calculations
CREATE TABLE tblObjects	
    ([TimeID] int, [DurationID] int, [Object_1] int, [Object_2] int, [Object_3] int)
;
    
INSERT INTO tblObjects	
    ([TimeID], [DurationID], [Object_1], [Object_2], [Object_3])
VALUES
    (1, 1, 5,  null, 0),
    (2, 2,  null, 1, 0),
    (3, 3,  null,  null, 9),
    (4, 4, 2,  null, 0),
    (5, 5,  null, 3, 0),
    (6, 6,  null,  null, 7),
    (7, 7, 1,  null, 0),
    (8, 8,  null, 1, 0),
    (9, 9,  null,  null, 1)
;

CREATE TABLE tblDurations
    ([DurationID] int, [Duration] int)
;
    
INSERT INTO tblDurations
    ([DurationID], [Duration])
VALUES
    (1, 1),
    (2, 2),
    (3, 3),
    (4, 3),
    (5, 2),
    (6, 3),
    (7, 1),
    (8, 1),
    (9, 1)
;

CREATE TABLE tblTimes	
    ([TimeID] int, [tDate] date, [tTime] time)
;
    
INSERT INTO tblTimes	
    ([TimeID], [tDate], [tTime])
VALUES
    (1, '2014-01-01 00:00:00', '12:03:00'),
    (2, '2014-01-01 00:00:00', '12:03:00'),
    (3, '2014-01-01 00:00:00', '12:03:00'),
    (4, '2014-01-01 00:00:00', '1:06:00'),
    (5, '2014-01-01 00:00:00', '1:06:00'),
    (6, '2014-01-01 00:00:00', '1:06:00'),
    (7, '2014-01-02 00:00:00', '9:00:00'),
    (8, '2014-01-02 00:00:00', '9:00:00'),
    (9, '2014-01-02 00:00:00', '9:00:00')
;

**Query 1**:

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
    
    


**[Results][2]**:

|      TDATE |     TTIME | VIB_PER_MIN_OBJ1 | VIB_PER_MIN_OBJ2 | VIB_PER_MIN_OBJ3 |
|------------|-----------|------------------|------------------|------------------|
| 2014-01-01 | 01:06:00. |               40 |               90 |              140 |
| 2014-01-01 | 12:03:00. |              300 |               30 |              180 |***
| 2014-01-02 | 09:00:00. |               60 |               60 |               60 |



  [1]: http://sqlfiddle.com/#!3/40666/9

Open in new window

0
 
LVL 30

Assisted Solution

by:hnasr
hnasr earned 120 total points
Comment Utility
PortletPaul's comment helped me understand the process.

This is your query with modified part in bold.
SELECT      a.tDate
,      convert(time,a.tTime) Time
,      Object1            =      MAX(ISNULL( b.Object_1 / (c.Duration / 60.0), 0 ))
,      Object2            =      MAX(ISNULL( b.Object_2 / (c.Duration / 60.0), 0 ))
,      Object3            =      MAX(ISNULL( b.Object_3 / (c.Duration / 60.0), 0 ))
FROM      tblTimes      a
JOIN      tblObjects      b      ON      a.TimeID = b.TimeID
JOIN      tblDurations      c      ON      b.DurationID = c.DurationID
GROUP BY a.tDate,convert(time,a.tTime)

Result:
tDate      Time      Object1      Object2      Object3
2014-01-02 00:00:00.000      09:00:00.0000000      60.00240009600      60.00240009600      60.00240009600
2014-01-01 00:00:00.000      12:03:00.0000000      300.01200048001      30.00030000300      180.00000000000
2014-01-01 00:00:00.000      13:06:00.0000000      40.00000000000      90.00090000900      140.00000000000
0
 

Author Comment

by:Mark01
Comment Utility
PortletPaul, your query in ID: 39991241 did execute without an error. I did not get a View error. After I pasted the code from the query window into the new View window, I clicked on the table diagram in the View window. SQL Server had changed the code. The View
did execute without any errors.

hnasr, your query in ID: 39991485 did execute without an error. However, I did get a View error. After I pasted the code from the query window into the new View window, I clicked on the table diagram in the View window. SQL Server had changed the code.The View will not execute and I get an error.

Thank you, Alan Warren, hnasr, James0628, PortletPaul and Sharath.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
perhaps:
don't paste into that window, you can create/modify views from the query window anyway
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
Welcome!
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
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…

743 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