In the following scenario, the two run dates start from 2015-01-30 to 2014-11-28 as per the database records since I am searching for last 3 months record and then trying to get the difference between x month - latest data point value which is 6 for id = 2 .
But I want to modify my query such that it starts from 1 month back from today at takes the first date of that month (e.g. 1 feb 2015 :1st date of every month) even though the first data point in the database records starts from 2015-01-30 in which the difference should be 8 - 0 = 8 rather than 6 .
How can I tweak my query to do this?
CREATE TABLE #t (ID INT, V FLOAT, D DATE) INSERT INTO #t VALUES (1, 1.2, '2014-01-01'), (1, 1.33, '2014-01-02'), (1, 1.33, '2014-01-03'), (2, 7, '2014-10-31'), (2, 5, '2014-11-28'), (2, 8, '2014-12-31'), (2, 11, '2015-01-30'); DECLARE @DealClauseString NVARCHAR(MAX) SET @DealClauseString = ';WITH filter AS ( SELECT ID , D , V , ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY D DESC ) AS RN FROM #t ), cte AS ( SELECT ID , D , V , MIN(D) OVER ( PARTITION BY ID ORDER BY D ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS Min , MAX(D) OVER ( PARTITION BY ID ORDER BY D ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS Max FROM filter WHERE RN <= 2 ) SELECT c1.ID , c2.V - c1.V AS V FROM cte c1 JOIN cte c2 ON c1.ID = c2.ID AND c1.D < c2.D WHERE ( c1.D = c1.MIN OR c1.D = c1.MAX ) AND ( c2.D = c2.MIN OR c2.D = c2.MAX ) and c2.V - c1.V between 0.3 and 6 ' EXEC (@DealClauseString) drop table #t
Any help would be really helpful. Pleasse let me know if you have any questions.