June 13, 2017 at 12:54 pm
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?
June 13, 2017 at 1:24 pm
Please post DDL, inserts, expected result and what you've tried so far.
Thanks.
June 13, 2017 at 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 ))
;
June 15, 2017 at 8:11 am
Bill Talada - Tuesday, June 13, 2017 1:43 PMHere 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.
June 15, 2017 at 8:45 am
wenger.noah - Thursday, June 15, 2017 8:11 AMGreat! 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
June 15, 2017 at 9:43 am
J Livingston SQL - Thursday, June 15, 2017 8:45 AMwenger.noah - Thursday, June 15, 2017 8:11 AMGreat! 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
June 15, 2017 at 9:44 am
wenger.noah - Thursday, June 15, 2017 9:43 AMJ Livingston SQL - Thursday, June 15, 2017 8:45 AMwenger.noah - Thursday, June 15, 2017 8:11 AMGreat! 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!!!
June 15, 2017 at 10:22 am
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
June 15, 2017 at 11:21 am
J Livingston SQL - Thursday, June 15, 2017 10:22 AMyou 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