1

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.

2

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())

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.