Displacement/Difference query

• Question

• I have a table that contains sensor readings that are read on a monthly basis at multiple locations/stations.  I need to build a 'displacement' or 'difference' query that calculates the difference between the current reading and first reading for each station. The result is basically a table that represents the displacement at each station over time.

Simplified data structure:

```ID, Date, Reading, Station
101, 1/1/2010, 10, 0.5 (initial reading)
102, 1/1/2010, 5, 1.0 (initial reading)
103, 2/1/2010, 12, 0.5
104, 2/1/2010, 11, 1.0
105, 3/1/2010, 13, 0.5
106, 3/1/2010, 14, 1.0
....Query Result
2/1/2010, 2, 0.5 (12-initial reading of 10)
2/1/2010, 6, 1.0 (11-initial reading of 5)
3/1/2010, 3, 0.5 (13-initial reading of 10)
3/1/2010, 9, 1.0 (14-initial reading of 5)
```
Any suggestions and/or nudges in the correct direction would be greatly appreciated.

....

Warren M

Monday, February 11, 2019 5:21 AM

• hmm, I'm not sure whether I understand your sample data. Seems like it does not match the description and the desired output.

E.g.

```DECLARE @Sample TABLE
(
ID INT ,
[Date] DATE ,
Station INT ,
PRIMARY KEY ( Station, [Date], ID )
);

INSERT INTO @Sample
VALUES	( 101, '20100101', 10, 0.5 ) ,
( 102, '20100101', 1, 1.0  ) ,
( 103, '20100201', 2, 0.5 ) ,
( 104, '20100201', 3, 1.0 ) ,
( 105, '20100301', 1, 0.5 ) ,
( 106, '20100301', 2, 1.0 );

WITH Ordered AS
(
SELECT	*,
ROW_NUMBER() OVER ( PARTITION BY S.Station ORDER BY S.[Date] ASC, S.ID ASC ) AS RN
FROM	@Sample S
)
SELECT	S.*,
FROM @Sample S
INNER JOIN ( SELECT * FROM Ordered I WHERE I.RN = 1) IR ON IR.Station = S.Station
ORDER BY S.Station,
S.[Date];

/*
--- ---------- ------- ------- -----------------
102 2010-01-01 1       1.0     0.0
105 2010-03-01 1       0.5     -0.5
103 2010-02-01 2       0.5     0.0
106 2010-03-01 2       1.0     0.5
104 2010-02-01 3       1.0     0.0
101 2010-01-01 10      0.5     0.0
*/```

• Edited by Monday, February 11, 2019 5:54 AM
• Marked as answer by Monday, February 11, 2019 6:21 PM
Monday, February 11, 2019 5:52 AM

All replies

• hmm, I'm not sure whether I understand your sample data. Seems like it does not match the description and the desired output.

E.g.

```DECLARE @Sample TABLE
(
ID INT ,
[Date] DATE ,
Station INT ,
PRIMARY KEY ( Station, [Date], ID )
);

INSERT INTO @Sample
VALUES	( 101, '20100101', 10, 0.5 ) ,
( 102, '20100101', 1, 1.0  ) ,
( 103, '20100201', 2, 0.5 ) ,
( 104, '20100201', 3, 1.0 ) ,
( 105, '20100301', 1, 0.5 ) ,
( 106, '20100301', 2, 1.0 );

WITH Ordered AS
(
SELECT	*,
ROW_NUMBER() OVER ( PARTITION BY S.Station ORDER BY S.[Date] ASC, S.ID ASC ) AS RN
FROM	@Sample S
)
SELECT	S.*,
FROM @Sample S
INNER JOIN ( SELECT * FROM Ordered I WHERE I.RN = 1) IR ON IR.Station = S.Station
ORDER BY S.Station,
S.[Date];

/*
--- ---------- ------- ------- -----------------
102 2010-01-01 1       1.0     0.0
105 2010-03-01 1       0.5     -0.5
103 2010-02-01 2       0.5     0.0
106 2010-03-01 2       1.0     0.5
104 2010-02-01 3       1.0     0.0
101 2010-01-01 10      0.5     0.0
*/```

• Edited by Monday, February 11, 2019 5:54 AM
• Marked as answer by Monday, February 11, 2019 6:21 PM
Monday, February 11, 2019 5:52 AM
• Hi Warren,

Maybe this query is exactly what you want.

```create table testData001
(
ID VARCHAR(30),
[Date] date,
Station varchar(64)
)

insert into testData001 values
('101','2010-1-1',10,'0.5'),
('102','2010-1-1',5,'1.0'),
('103','2010-2-1',12,'0.5'),
('104','2010-2-1',11,'1.0'),
('105','2010-3-1',13,'0.5'),
('106','2010-3-1',14,'1.0')

;WITH CTE AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY Station ORDER BY [Date]) AS RN,
ID,
[Date],
Station
FROM testData001
)
SELECT
T.[Date],
T.Station
FROM CTE T
LEFT JOIN CTE T1 ON T.Station=T1.Station AND T1.RN=1
WHERE T.RN>1
ORDER BY T.[Date],T.Station```

Best Regards,

Will

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Monday, February 11, 2019 5:59 AM
• sounds like this to me

```DECLARE @Sample TABLE
(
ID INT ,
[Date] DATE ,
Station NUMERIC(9,1),
PRIMARY KEY ( Station, [Date], ID )
);

INSERT INTO @Sample
VALUES	( 101, '20100101', 10, 0.5 ) ,
( 102, '20100101', 5, 1.0  ) ,
( 103, '20100201', 12, 0.5 ) ,
( 104, '20100201', 11, 1.0 ) ,
( 105, '20100301', 13, 0.5 ) ,
( 106, '20100301', 14, 1.0 );

FROM
(
SELECT *,
FIRST_VALUE(Reading) OVER (PARTITION BY
Station ORDER BY  Date RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS FirstReading,
MIN(Date) OVER (PARTITION BY
Station) AS FirstDate
FROM @Sample
)t
WHERE [Date] >  FirstDate

/*
Output
----------------------------------------------------
---------------------------------
2010-02-01	2	0.5
2010-03-01	3	0.5
2010-02-01	6	1.0
2010-03-01	9	1.0

*/```

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog

• Edited by Monday, February 11, 2019 6:42 AM
Monday, February 11, 2019 6:35 AM
• Thanks so much Stefan, this seems to work perfectly, and is quite quick.  Need to dig a little deeper and test against another larger dataset, but I think this it it.

Thanks for all of the suggestions.  Will's code below seems to work as well, but is substantially slower.

Warren M

Monday, February 11, 2019 6:27 PM
• ```CREATE TABLE mytable(
ID      INTEGER  NOT NULL
,Date    DATE  NOT NULL
,Reading INTEGER  NOT NULL
,Station NUMERIC(5,1) NOT NULL
);
INSERT INTO mytable(ID,Date,Reading,Station) VALUES
(101,'1/1/2010',10,0.5)
,(102,'1/1/2010',5,1.0)
,(103,'2/1/2010',12,0.5)
,(104,'2/1/2010',11,1.0)
,(105,'3/1/2010',13,0.5)
,(106,'3/1/2010',14,1.0);

;with mycte as (
select *
, Reading-first_value(Reading) Over(partition by Station Order by [Date] ) delta
,row_number()Over(partition by Station Order by [Date] )  rn
from mytable
)