none
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

Answers

  • 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 , 
    	Reading NUMERIC(9,1),
    	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.*,
    		S.Reading - IR.Reading AS ReadingDifference
    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];
    
    /*
    ID  Date       Station Reading ReadingDifference
    --- ---------- ------- ------- -----------------
    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 Stefan Hoffmann Monday, February 11, 2019 5:54 AM
    • Marked as answer by Warren M 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 , 
    	Reading NUMERIC(9,1),
    	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.*,
    		S.Reading - IR.Reading AS ReadingDifference
    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];
    
    /*
    ID  Date       Station Reading ReadingDifference
    --- ---------- ------- ------- -----------------
    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 Stefan Hoffmann Monday, February 11, 2019 5:54 AM
    • Marked as answer by Warren M 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,
     Reading int,
     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],
    Reading,
    Station
    FROM testData001
    )
    SELECT 
    T.[Date],
    T.Reading-T1.Reading AS DIFF,
    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
    Moderator
  • sounds like this to me

    DECLARE @Sample TABLE
    (
    	ID INT , 
    	[Date] DATE , 
    	Reading INT , 
    	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 );
    
    	
    	SELECT Date,Reading - FirstReading AS ReadinfDiff, Station
    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
    ----------------------------------------------------
    Date	   ReadinfDiff	Station
    ---------------------------------
    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
    My Facebook Page


    • Edited by Visakh16MVP 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
    	 )
     select ID,Date,Reading,Station,delta
     from mycte where rn>1
     order by ID
    
    
    
     drop table mytable

    Monday, February 11, 2019 6:44 PM
    Moderator