Previous one year and previous two years in sql

Question

Previous one year and previous two years in sql

soldout6000

Ten Centuries

Points: 1051
More actions
May 23, 2018 at 2:47 pm

#352567

report_Begin_date = 1/1/2017
report_End_date = 9/30/2017
Based on the begin and end date, I want previous one year from 12/1/2015 to 11/30/2016
and previous two years from 12/1/2014 to 11/30/2015
The following code is producing correct last year and last two years result, however, I don't know how to change the end date of last year and last two year.
This query is giving me the following results;
for last year= 12/1/2015 to 12/31/2016
for last 2 year = 12/1/2014 to 12/31/2015
Any help would be greatly appreciated!

with rpt_ly_date As
(select DATEADD(year,-1,report_begin_date) Report_LY_Begin_Date,
cast(dateadd(DD, -1, dateadd(YY,datediff(yy,0,Report_End_Date),0))as date) Report_LY_End_Date
from rpt_date_range_view
),
rpt_l2y_date As
(select DATEADD(year,-2,report_begin_date) Report_L2Y_Begin_Date,
cast(dateadd(DD, -1, dateadd(YY,datediff(yy,0,report_begin_date),0))as date) Report_L2Y_End_Date
from rpt_date_range_view
)
select *
from rpt_ly_date ly
inner join rpt_l2y_date l2y on (1=1)

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

soldout6000 - Wednesday, May 23, 2018 2:47 PM
report_Begin_date = 1/1/2017
report_End_date = 9/30/2017
Based on the begin and end date, I want previous one year from 12/1/2015 to 11/30/2016
and previous two years from 12/1/2014 to 11/30/2015
The following code is producing correct last year and last two years result, however, I don't know how to change the end date of last year and last two year.
This query is giving me the following results;
for last year= 12/1/2015 to 12/31/2016
for last 2 year = 12/1/2014 to 12/31/2015
Any help would be greatly appreciated!

with rpt_ly_date As
(select DATEADD(year,-1,report_begin_date) Report_LY_Begin_Date,
cast(dateadd(DD, -1, dateadd(YY,datediff(yy,0,Report_End_Date),0))as date) Report_LY_End_Date
from rpt_date_range_view
),
rpt_l2y_date As
(select DATEADD(year,-2,report_begin_date) Report_L2Y_Begin_Date,
cast(dateadd(DD, -1, dateadd(YY,datediff(yy,0,report_begin_date),0))as date) Report_L2Y_End_Date
from rpt_date_range_view
)
select *
from rpt_ly_date ly
inner join rpt_l2y_date l2y on (1=1)

Please explain why if your report start and end dates are 2017-01-01 to 2017-09-30 that you want the previous two years to be 2014-12-01 to 2015-11-30 and 2015-12-01 to 2016-11-30 respectively. Doesn't make sense you want to leave out 2016-12-01 to 2016-12-31.

below86 SSChampion Points: 11532 More actions · Answer 2

Based on the assumption that the report_begin_date will always be the first date of the year, this logic will get the correct dates. If you calc the 1 year back numbers first it's as simple as subtracting a year for the 2 year back numbers.
DECLARE @report_Begin_date AS DATE; SET @report_Begin_date = '1/1/2017';

DECLARE @report_End_date AS DATE; SET @report_End_date = '9/30/2017';

DECLARE @Begin_1_year_back AS DATE; DECLARE @Begin_2_year_back AS DATE;

SET @Begin_1_year_back = DATEADD(YY, -1, DATEADD(MM, -1, @report_Begin_date));

SET @Begin_2_year_back = DATEADD(YY, -1, @Begin_1_year_back);

DECLARE @End_1_year_back AS DATE; DECLARE @End_2_year_back AS DATE;

SET @End_1_year_back = DATEADD(DD, -1, DATEADD(YY, 1, @Begin_1_year_back));

SET @End_2_year_back = DATEADD(YY, -1, @End_1_year_back);

SELECT @Begin_1_year_back AS Begin_1_year_back, @End_1_year_back AS End_1_year_back;

SELECT @Begin_2_year_back AS Begin_2_year_back, @End_2_year_back AS End_2_year_back;

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

soldout6000 Ten Centuries Points: 1051 More actions · Answer 3

Below86, thank you so much! I like how clean and brief your formulas are, and they were perfectly fine.
I appreciate your help!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

soldout6000 - Thursday, May 24, 2018 8:39 AM
Below86, thank you so much! I like how clean and brief your formulas are, and they were perfectly fine.
I appreciate your help!

Still doesn't answer the question why you skip December 2016, in the case of reporting dates 2017-01-01 to 2017-09-30, when going back the previous 2 years.