StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<pre><code>Select DL.Machine, DL.ShiftName, @MyDate, case when T.Time is null then P.Time else T.Time end as Time, T.Temperature, NULL as Pressure From DataLogging as DL inner join Temperature as T on "reading time is within this shift's timespan" Where DL.Date = @MyDate and DL.ShiftName = @MyShiftName UNION Select DL.Machine, DL.ShiftName, @MyDate, case when T.Time is null then P.Time else T.Time end as Time, NULL as Temperature, P.Pressure From DataLogging as DL inner join Pressure as P on "reading time is within this shift's timespan" Where DL.Date = @MyDate and DL.ShiftName = @MyShiftName </code></pre> <p><strong>Let me explain the method here...</strong></p> <ol> <li><p>Start off with DataLogging. You are concerned with the employee who had a particular shift. You define that employee in the <code>WHERE</code> clause by specifying an input DATE and input SHIFT.</p></li> <li><p>Join up with the <code>Temperature</code> table, because you want to see all the temperature readings from that person's shift. </p></li> <li><p>Join up with the <code>Pressure</code> table, because you want to see all the pressure readings from that person's shift.</p></li> <li><p>Why do we <code>UNION</code>? Well, mainly to prevent dupes (Dupes would exist if we joined with both readings-tables in the same Select statement). As you've noticed, it's hard to join Temperature readings and Pressure readings because they have different timestamps. If they are one second apart, then there is nothing to join on. Therefore, I think the best solution is to simply have a record per reading as I have done above. The above will list all Temperature readings first, followed by all Pressure Readings -- and combine them into the same Table that is returned via the <code>UNION</code>. </p></li> </ol> <p><strong>Note:</strong> You should probably be using a <code>datetime</code> field instead of a <code>Date</code> column and a Time column, because it will store both the date and the time in one field. </p> <p><strong>Note:</strong> You can combine those readings that have the same date & time by using a subquery and <a href="https://stackoverflow.com/questions/2702/how-do-i-use-t-sql-group-by">grouping</a> the <a href="http://msdn.microsoft.com/en-us/library/ms189575.aspx" rel="nofollow noreferrer">subquery</a> on the Time.</p> <p><strong>Note:</strong> <code>"reading time is within this shift's timespan"</code> is included above to indicate some code you need to fill in. You will probably want to use a <a href="http://msdn.microsoft.com/en-us/library/ms181765.aspx" rel="nofollow noreferrer">case</a> statement to adjust the <code>ON Clause</code> to only include those items with a particular shift</p> <p><strong>References</strong></p> <ul> <li><a href="http://msdn.microsoft.com/en-us/library/ms180026.aspx" rel="nofollow noreferrer">Union</a></li> </ul>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload