Searching from the latest date in SQL Server

Question

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.

Answer 1

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 the ORDER 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 the filter CTE. So it becomes something like:

FROM #t
WHERE D <= DATEADD(MONTH,-1,SYSDATETIME())