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.