StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POJoin two tables together and condense multiple rows in a single row
text
Body
copied!<p>I have two tables as follows:</p> <p>TableOne<br> - RawDataId int (pk)<br> - TimeStamp DateTime<br> - BuildingID int </p> <p>TableTwo<br> - RawDataId int (pk/fk)<br> - MeterId int (pk)<br> - Value real </p> <p>The MeterId is not unique and repeats multiple times (but always in equal number). The two tables join together no problem. I am able to select the top 15 rows and order the by time stamp, giving me the latest value for each meter (15 in total, each with a time stamp). However, I also need to get the Value of each meter from a previous time (exactly 1440 and 1439 minutes earlier) - if that makes any sense.</p> <p>So after the query is run, I need a table with the columns from TableOne and TableTwo, but with two additional columns for ValueB and ValueC (B been the value 1440 minutes earlier, and C 1439 earlier). Ive been playing around with his all day and most of last night, and I'm slowly losing the plot.</p> <p>Any help would be appreciated. Thanks peeps.</p> <p>--- Update</p> <p>I've included the actual table schema below, together with some sample data.</p> <pre><code>CREATE TABLE [dbo].[TableOne]( [RawDataId] [bigint] IDENTITY(1,1) NOT NULL, [TimeStamp] [datetime] NOT NULL, [BuildingId] [int] NULL, CONSTRAINT [TableOne_PK] PRIMARY KEY CLUSTERED CREATE TABLE [dbo].[TableTwo]( [MeterId] [bigint] NOT NULL, [RawDataId] [bigint] NOT NULL, [Value] [real] NULL, CONSTRAINT [TableTwo_PK] PRIMARY KEY CLUSTERED </code></pre> <p>The sample data for the last 30 records from TableOne:</p> <pre><code>RawDataId, TimeStamp, BuildingId 21677 2012-05-16 00:03:00.000 1 21678 2012-05-16 00:03:00.000 1 21679 2012-05-16 00:03:00.000 1 21680 2012-05-16 00:03:00.000 1 21681 2012-05-16 00:03:00.000 1 21682 2012-05-16 00:03:00.000 1 21683 2012-05-16 00:03:00.000 1 21684 2012-05-16 00:03:00.000 1 21685 2012-05-16 00:03:00.000 1 21686 2012-05-16 00:03:00.000 1 21687 2012-05-16 00:03:00.000 1 21688 2012-05-16 00:03:00.000 1 21689 2012-05-16 00:03:00.000 1 21690 2012-05-16 00:03:00.000 1 21691 2012-05-16 00:03:00.000 1 21662 2012-05-16 00:02:00.000 1 21663 2012-05-16 00:02:00.000 1 21664 2012-05-16 00:02:00.000 1 21665 2012-05-16 00:02:00.000 1 21666 2012-05-16 00:02:00.000 1 21667 2012-05-16 00:02:00.000 1 21668 2012-05-16 00:02:00.000 1 21669 2012-05-16 00:02:00.000 1 21670 2012-05-16 00:02:00.000 1 21671 2012-05-16 00:02:00.000 1 21672 2012-05-16 00:02:00.000 1 21673 2012-05-16 00:02:00.000 1 21674 2012-05-16 00:02:00.000 1 21675 2012-05-16 00:02:00.000 1 21676 2012-05-16 00:02:00.000 1 </code></pre> <p>Sample for TableTwo:</p> <pre><code>MeterId, RawDataId, Value 15 21691 7722613 14 21690 908944 13 21689 4982947 12 21688 3821899 11 21687 6 10 21686 0 9 21685 0 8 21684 5761656 7 21683 4240048 6 21682 1541372 5 21681 283223 4 21680 1.298603E+07 3 21679 388137 2 21678 876121 1 21677 0 15 21676 7722615 14 21675 908944 13 21674 4982947 12 21673 3821899 11 21672 5 10 21671 0 9 21670 0 8 21669 5761656 7 21668 4240052 6 21667 1541372 5 21666 283223 4 21665 1.298604E+07 3 21664 388137 2 21663 876122 1 21662 0 </code></pre> <p>A meter reading is written to the tables every 1 (hence the time stamp). When select the top 15 records (sorted by TimeStamp, to give me the latest values), I also need to get the values of that meter 1440 and 1439 minutes ago (relative to the latest TimeStamp). I hope this makes it clearer. </p> <p>So far, my SQL query looks like this:</p> <pre><code>SELECT TOP 15 * FROM (Select TableOne.[RawDataId], [TimeStamp], BuildingId, MeterId, `enter code here`Value FROM [TableOne] INNER JOIN TableTwo ON TableOne = TableTwo) as PS ORDER BY [TimeStamp]; </code></pre> <p>The query gives me the follow, but I need the additional two columns with the value of the meter 1440 and 1439 minutes ago, relative to the TimeStamp:</p> <pre><code>RawDataId, TimeStamp, BuildingId, MeterId, Value 21677 2012-05-16 00:03:00.000 1 1 0 21678 2012-05-16 00:03:00.000 1 2 876121 21679 2012-05-16 00:03:00.000 1 3 388137 21680 2012-05-16 00:03:00.000 1 4 1.298603E+07 21681 2012-05-16 00:03:00.000 1 5 283223 21682 2012-05-16 00:03:00.000 1 6 1541372 21683 2012-05-16 00:03:00.000 1 7 4240048 21684 2012-05-16 00:03:00.000 1 8 5761656 21685 2012-05-16 00:03:00.000 1 9 0 21686 2012-05-16 00:03:00.000 1 10 0 21687 2012-05-16 00:03:00.000 1 11 6 21688 2012-05-16 00:03:00.000 1 12 3821899 21689 2012-05-16 00:03:00.000 1 13 4982947 21690 2012-05-16 00:03:00.000 1 14 908944 21691 2012-05-16 00:03:00.000 1 15 7722613 </code></pre>

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