<div>
A copy/paste of some rows (just a few) that demonstrate what the source data looks like will greatly assist<br />
I regret that I don't understand by the &nbsp;words alone.<br />
<br />
Oh! and you will need to define what &quot;similar time stamp&quot; actually means. &nbsp;(+/- 1 minute? +/- 15 minutes? less? more?)<br />
<br />
Oh! (again) will also need to know what the actual &quot;data type&quot; of that column &quot;time stamp&quot;. is it datetime? time? (hopefully not varchar!)
</div>


<div>
Thank you experts for such a quick response, and apologies for not being clear here. I have tried to simulate data (as I can't paste the real data).<br />
<br />
<br />
In the attached image, I have a table having rows grouped by a number. Here, I will pick y value of a particular time interval and divide it by the x value of the &nbsp;matching time interval and compare it with the z value of the matching time interval. Z is calculated by the calculation server. If you see, the z values do not match which helps me in identifying easily that there is a problem. Please note each group number can contain several organizations and each organization can have only one value of x, y and z i.e. only one set of x1, y1 and z1, one set of x2, y2 and z2.....so on. The date is of type 'datetime'<br />
<br />
Please let me know if further details are required.<a href="https://filedb.experts-exchange.com/incoming/2017/11_w46/1210451/sample_data.PNG" target="_blank" title="Sample data (21 KB)"><img alt="Sample data" class="file-block lazyload" style="max-width: 800px;" data-src="https://filedb.experts-exchange.com/incoming/2017/11_w46/800_1210451/sample_data.PNG" /></a>
</div>


sample_data.PNG

<div>
looks a lot like this:<br />
<br />

<pre><code class="code-10 nolinks" id="code-20-42365157-1"><span>SELECT</span>
<span>      [Group No]</span>
<span>    , Organization</span>
<span>    , [Time Interval]</span>
<span>    , MIN (VALUES) [X Value]</span>
<span>    , MAX (VALUES) [Y Value]</span>
<span>    , MAX (VALUES) / MIN (VALUES) [Z Value]</span>
<span>FROM that_table</span>
<span>GROUP BY</span>
<span>      [Group No]</span>
<span>    , Organization</span>
<span>    , [Time Interval]</span>
</code></pre>
</div>


<div>
sorry missed the [Z Value] was a column<br />
<br />

<pre><code class="code-10 nolinks" id="code-20-42365158-1"><span>SELECT</span>
<span>      [Group No]</span>
<span>    , Organization</span>
<span>    , [Time Interval]</span>
<span>    , MIN (VALUES) [X Value]</span>
<span>    , MAX (VALUES) [Y Value]</span>
<span>    , MAX (VALUES) / MIN (VALUES)  [Result (Y/X)]</span>
<span>    , [Z Value]</span>
<span>FROM that_table</span>
<span>GROUP BY</span>
<span>      [Group No]</span>
<span>    , Organization</span>
<span>    , [Time Interval]</span>
<span>    , [Z Value]</span>
</code></pre>
</div>


<div>
Hi PortletPaul,<br />
Thanks for your response. Please note the values taken are just samples, the numbers can just be anything. I think, min and max would not be the right choice. X can be 13.5 and y can be 2, it is of type decimal. Is there any other way to identify the x value and y value please?
</div>


<div>
We need not consider all the time intervals. If we just consider the first time interval for each organization, should be sufficient for display in the output table.
</div>


<div>
I responded to what I see in that image. If you don't reveal details it is just a little impossible for us to guess what we don't see.<br />
<br />
&gt;&gt;&quot;<i>Is there any other way to identify the x value and y value please?</i>&quot;<br />
is &quot;x&quot; the &quot;first value&quot; and is y the &quot;last&quot; value<br />
The real point here is <b>you have to tell me </b>what the logic is, all can do is guess<br />
<br />
IF you provide a sample of data &nbsp;in a reusable format then perhaps we cold write a query. (nb:&quot;data&quot; is not an &quot;image of data&quot;)<br />
<br />
A simple copy/paste of the &quot;input&quot; section of your worksheet into a &quot;code block&quot; would help. <br />
<br />
But note the the sample needs to be &quot;representative&quot;, that is, you need to choose your sample carefully. But, the sample still needs to be relatively small. &nbsp;Don't include anything that might be private or sensitive.<br />
<br />
If the expected output you have shown so far doesn't cover all the items of the sample then please also provide the new expected output.
</div>


<div>
so for the &quot;time interval&quot; of 08:05 in your image, and the FIRST value = X, and the SECOND value = Y &nbsp;? (please onfirm)<br />
and we need only consider first and second for each &quot;time interval&quot;? (please confirm)
</div>


<div>
Sorry Paul, because I am working on a remote machine from which I can't copy data, I have recreated it. I have tried to arrange for a sample excel file with some random meaningful numbers with column names morphed.In my above statement, I meant something like this:<br />
<br />
- Org1 has x1, y1, z1 data for &nbsp;time intervals say starting at 8.00, 8.05, then 8.15, 8.20..... (for each x1, y1 and z1)<br />
- Org2 has x1, y1, z1 data for similar time intervals like above<br />
- Org3 has x1, y1, z1 data for similar time intervals like above<br />
...and so on.<br />
<br />
If I can just get the above mentioned output for just the top row when grouped as time interval for each of the Organization it would help me. I do have many other calculations that I will have to perform, but having this simple division as the base, I will modify it accordingly and do further operations. i.e. the output contains contains only the first row which will be the first time interval for each of the organizations. Also, the variable names x1, y1 and z1 can be considered always the same for each of the orgs. (please ignore x2, y2 and z2, etc in my previous sample).<br />
<a href="https://filedb.experts-exchange.com/incoming/2017/11_w46/1210473/sample-data.xlsx" target="_blank" class="file-inline" title="sample-data.xlsx (11 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>sample-data.xlsx</a>
</div>


sample-data.xlsx

<div>
Hi Paul,<br />
Thank you very much for your efforts, much appreciated and am really grateful to you. Your code pretty much does what I was expecting and this question can be considered closed. Just checking with you if there is a way to do the same thing using a simpler and probably a longer way using self-join. The reason I am asking is the query is little too complicated for an amateur like me to modify for some of the other scenarios I am verifying. I will raise a separate question if that can be done.<br />
<br />
Hi Olaf,<br />
Thanks for your inputs. One column in the table stores all the input variables like I have indicated for each of the organization. For example, Org1 will have X1 to X10, Y1 to Y10. Some calculations are performed on each of the variable, division is just a simple example I have given. The calculation can be something like (X1 + Y1)/ Y2 all belonging to the same time interval. I compare the expected output (z) with the result. I am trying to create a separate query for each such formulas which I am verifying. Yes, all operands and results are in the same column, but that is the way the table has been designed.
</div>


<div>
sukhoi35 using self-joins is going to make this more complicated.<br />
<br />
The brilliant thing about the &quot;window functions&quot; is they make life simpler. You just need to &quot;play with them a bit&quot; it won't take you long.<br />
<br />
the key is getting an understanding of this bit: <b>OVER( PARTITION BY ..... &nbsp;ORDER BY ....... )</b><br />
<br />
<a href="https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql" rel="ugc">https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql</a><br />
<a href="https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql" rel="ugc">https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql</a><br />
<a href="https://docs.microsoft.com/en-us/sql/t-sql/functions/lead-transact-sql" rel="ugc">https://docs.microsoft.com/en-us/sql/t-sql/functions/lead-transact-sql</a>
</div>


<div>
The outset is clear, sukhooi, the task to get out several values from multiple records is clear, your data itself also is identifying them now, I was just needing the real column names you still didn't tell. You seem to have come to a conclusion, so problem solved. But as said, you can't really have a &nbsp;table as you laid it out in your sample-data.xlsx, no database will allow same column name (values) twice!<br />
<br />
Paul simply assumed a &quot;Code&quot; column containing the value names &quot;X1&quot; etc and also choose to name your second values column &quot;nValues&quot;, as Values is a reserved keyword, you could still create a table with that column name put in square brackets or double quotes.<br />
<br />
I also wouldn't suggest ORDER BY (select 1), Code is neither used for sorting nor partitioning. So it's working on an instable basis and depends on data coming in the correct order. <br />
<br />
Paul, this never becomes a problem, but another one shows in your fiddle in Org2, where you have two Y1 rows, these should not give a result, as a Z value is missing, still you take the second Y1 value as Z value.<br />
<br />
A good solution needs to take more care about the Code to make use of nValus as X,Y and ZValue than just expecting them to come in order. Notice once more: Neither your partitioning nor ORDER BY (select1) care for the Code at all.<br />
<br />
Bye, Olaf.
</div>


<div>
@Olaf, <br />
<br />
Yes I made many assumptions (e.g. renaming values to nValues, removing spaces in column names etc.) &nbsp;But I was doing so to elicit further comment and information about the requirements.<br />
<br />
Sometimes folks have a very hard time stating their needs, and as already known, &nbsp;there are still more to come.
</div>


<div>
The point is, your solution is still instable and erroneous as shown because of double Y1 in Org2.<br />
<br />
And you don't even pick out the three rows from possibly many more by eg filtering Left(Code,1) IN ('X','Y','Z'). To me the question whether there really is a column like that is still open, not only what's its name.<br />
<br />
If you ask me, this whole problem is a candidate for pivoting the data, not for window functions.<br />
<br />
Bye, Olaf.
</div>


<div>
Hi Olaf, Paul,<br />
Thanks for the comments. I am sorry, I did not realize I had mistakenly repeated the column names. I haven't yet tried out the code shared by Paul on the real table which is pretty huge and I might have to customize it for each of the scenario I am verifying. My acceptance as solution was mostly on the sample data I had given. If I do face issues, I will surely recreate a better well organized actual data from the DB and seek your help as a new question. As always, I am very grateful for all your voluntary help.
</div>


<div>
<div class="content wysiwyg-content">
Hello Experts,<br />
Need your suggestion please as my knowledge with SQL is basic. I have a table having several thousands of rows. Each set of rows have a common id for example, the first 1000 rows belong to dataset no. 1 and so all have value 1 in the id column. Within these 1000 rows, I need to find the calculation of multiple two rows having same time stamp and compare with another row having similar time stamp.<br />
<br />
For example, pick the value contained in the row having field X and pick the value contained in the row having field y, divide them and compare it with the value in the row z by display it as output similar to the columns below. I am not sure if Joins would be the right way as they are all part of the same table.<br />
<br />
<br />
id | X Value | Y Value | Division Result | Z Value | Time Stamp<br />
1 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;20 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;23:59:59
</div>
</div>


Hello Experts,
Need your suggestion please as my knowledge with SQL is basic. I have a table having several thousands of rows. Each set of rows have a common id for example, the first 1000 rows belong to dataset no. 1 and so all have value 1 in the id column. Within these 1000 rows, I need to find the calculation of multiple two rows having same time stamp and compare with another row having similar time stamp.

For example, pick the value contained in the row having field X and pick the value contained in the row having field y, divide them and compare it with the value in the row z by display it as output similar to the columns below. I am not sure if Joins would be the right way as they are all part of the same table.


id | X Value | Y Value | Division Result | Z Value | Time Stamp
1	   20	           10	                  2                     2              23:59:59

Suggestions for writing a query

Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.

Databases

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.