Rolling 6 Months

  • I have a table:  

    Date                    Value
    05/09/2017          88,688
    12/28/2016          66,777
    10/14/2016          55,444
    .
    .
    .

    I need to subtract the value for a date (05/09/2017) which is >= 6 months prior (so for the date 05/09/2017, I need to subtract 88,688 from the value for the date > = 6 months prior to 05/09/2017, so the value for the date 11/09/2016 or the most recent prior).  

    I have to do this calculation for every value in the table.  How do I write the code to run this calculation?

  • Here is a wild guess


    DECLARE @t table (d date not null, n int not NULL);

    INSERT INTO @t values ('05/09/2017', 88688), ('12/28/2016',66777), ('10/14/2016',55444)

    SELECT * FROM @t;

    SELECT
        t.d, t2.d as d2, t.n - t2.n as diff
    FROM @t t
    JOIN @t t2 on DATEADD(DAY,-180, t.d ) >= t2.d
    WHERE t2.d = (SELECT MAX(d) FROM @t t3 where t3.d < DATEADD(DAY,-180, t.d ))
    ;

  • Bill Talada - Tuesday, June 13, 2017 1:43 PM

    Here is a wild guess


    DECLARE @t table (d date not null, n int not NULL);

    INSERT INTO @t values ('05/09/2017', 88688), ('12/28/2016',66777), ('10/14/2016',55444)

    SELECT * FROM @t;

    SELECT
        t.d, t2.d as d2, t.n - t2.n as diff
    FROM @t t
    JOIN @t t2 on DATEADD(DAY,-180, t.d ) >= t2.d
    WHERE t2.d = (SELECT MAX(d) FROM @t t3 where t3.d < DATEADD(DAY,-180, t.d ))
    ;

    Great! It worked!  Thanks for a little help from my friends....new problem:

    Same table/different value and BOOLEAN calculation:

    Date             Value
    05/09/2017    Yes
    12/28/2016    Yes
    10/14/2016    No
    .
    .
    .

    We want to know if there was a NO value from six months of the Date for all Date's in the table.  Example1: For the row with Date 05/09/2017, did we have a NO value between 05/09/2017 and 11/09/2017.  Example2: For the row with Date 12/28/2016, did we have a NO value between 12/28/2016 and 06/28/2016.

  • wenger.noah - Thursday, June 15, 2017 8:11 AM

    Great! It worked!  Thanks for a little help from my friends....new problem:

    Same table/different value and BOOLEAN calculation:

    Date             Value
    05/09/2017    Yes
    12/28/2016    Yes
    10/14/2016    No
    .
    .
    .

    We want to know if there was a NO value from six months of the Date for all Date's in the table.  Example1: For the row with Date 05/09/2017, did we have a NO value between 05/09/2017 and 11/09/2017.  Example2: For the row with Date 12/28/2016, did we have a NO value between 12/28/2016 and 06/28/2016.

    me thinks you need to provide some sample data and expected results......what you have asked for doesnt make any sense to me

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • J Livingston SQL - Thursday, June 15, 2017 8:45 AM

    wenger.noah - Thursday, June 15, 2017 8:11 AM

    Great! It worked!  Thanks for a little help from my friends....new problem:

    Same table/different value and BOOLEAN calculation:

    Date             Value
    05/09/2017    Yes
    12/28/2016    Yes
    10/14/2016    No
    .
    .
    .

    We want to know if there was a NO value from six months of the Date for all Date's in the table.  Example1: For the row with Date 05/09/2017, did we have a NO value between 05/09/2017 and 11/09/2017.  Example2: For the row with Date 12/28/2016, did we have a NO value between 12/28/2016 and 06/28/2016.

    me thinks you need to provide some sample data and expected results......what you have asked for doesnt make any sense to me

  • wenger.noah - Thursday, June 15, 2017 9:43 AM

    J Livingston SQL - Thursday, June 15, 2017 8:45 AM

    wenger.noah - Thursday, June 15, 2017 8:11 AM

    Great! It worked!  Thanks for a little help from my friends....new problem:

    Same table/different value and BOOLEAN calculation:

    Date             Value
    05/09/2017    Yes
    12/28/2016    Yes
    10/14/2016    No
    .
    .
    .

    We want to know if there was a NO value from six months of the Date for all Date's in the table.  Example1: For the row with Date 05/09/2017, did we have a NO value between 05/09/2017 and 11/09/2017.  Example2: For the row with Date 12/28/2016, did we have a NO value between 12/28/2016 and 06/28/2016.

    me thinks you need to provide some sample data and expected results......what you have asked for doesnt make any sense to me

    See attachment and let me know if the question makes more sense - thank you!!!

  • you havent provided your expected results in the spreadsheet.......please clarify by reposting with expected results column

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • J Livingston SQL - Thursday, June 15, 2017 10:22 AM

    you havent provided your expected results in the spreadsheet.......please clarify by reposting with expected results column

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply