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.
Best Answer
For starters, don't use
@DealClauseString
- just have your query.Also, remove
ORDER BY D ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
- when you're using the whole partition, there's no reason to have theORDER BY
clause at all.Anyway - for your particular question, if I understand it correctly, you want it to be like your
#t
table only has records that are at least a month old. So you should put a WHERE clause in thefilter
CTE. So it becomes something like: