years between two dates in sql server with starting and end date of each of them in sql server

i want to get years between two dates with their starting and end dates.Suppose if i enter startdate as “07/06/2017” and enddate as “18/09/2019”, i want list the years in between with their starting and end date respectively.Kindly suggest me how it can be achieved.

Input Table:

BusinessRefId   Period  GoalType                Amount  StartDateUtc    EndDateUtc  Currency
Business B      Year    CommittedTransactionFee 18000   07/06/17 00:00              USD

​x
 
BusinessRefId   Period  GoalType                Amount  StartDateUtc    EndDateUtc  CurrencyBusiness B      Year    CommittedTransactionFee 18000   07/06/17 00:00              USD​​

Output Table:

BusinessRefId   Period      GoalType                Amount  StartDateUtc    EndDateUtc  Currency
Business B      2019-2020   CommittedTransactionFee 18000   07/06/2019      07/06/2020  USD
Business B      2018-2019   CommittedTransactionFee 18000   07/06/2018      07/06/2019  USD
Business B      2017-2018   CommittedTransactionFee 18000   07/06/2017      07/06/2018  USD

 
BusinessRefId   Period      GoalType                Amount  StartDateUtc    EndDateUtc  CurrencyBusiness B      2019-2020   CommittedTransactionFee 18000   07/06/2019      07/06/2020  USDBusiness B      2018-2019   CommittedTransactionFee 18000   07/06/2018      07/06/2019  USDBusiness B      2017-2018   CommittedTransactionFee 18000   07/06/2017      07/06/2018  USD​​

I need to split the dates into months i.e. if BusinessGoal.Period = ‘Month’ and i need a single query that splits the dates according to the Period mentioned in the BusinessGoal table and given in the input and output table for both “Year” and “Month” respectively. Input Table (BusinessGoal):

BusinessRefId   Period  GoalType                Amount  StartDateUtc      EndDateUtc         Currency
Business C      Year    CommittedTransactionFee 18000   05/07/19 00:00:00 19/09/19 00:00:00  USD

 
BusinessRefId   Period  GoalType                Amount  StartDateUtc      EndDateUtc         CurrencyBusiness C      Year    CommittedTransactionFee 18000   05/07/19 00:00:00 19/09/19 00:00:00  USD​​

Output Table (BusinessGoal):

BusinessRefId   Period              GoalType                Amount  StartDateUtc         EndDateUtc           Currency
Business C      2019-07 - 2019-08   CommittedTransactionFee 18000   05/07/2019 00:00:00  05/08/2019 00:00:00    USD
Business C      2019-08 - 2019-09   CommittedTransactionFee 18000   05/08/2019 00:00:00  05/09/2019 00:00:00    USD
Business C      2019-09 - 2019-10   CommittedTransactionFee 18000   05/09/2019 00:00:00  05/10/2019 00:00:00    USD

 
BusinessRefId   Period              GoalType                Amount  StartDateUtc         EndDateUtc           CurrencyBusiness C      2019-07 - 2019-08   CommittedTransactionFee 18000   05/07/2019 00:00:00  05/08/2019 00:00:00    USDBusiness C      2019-08 - 2019-09   CommittedTransactionFee 18000   05/08/2019 00:00:00  05/09/2019 00:00:00    USDBusiness C      2019-09 - 2019-10   CommittedTransactionFee 18000   05/09/2019 00:00:00  05/10/2019 00:00:00    USD​​

Answer

You need a table of numbers, here i use the simplest one

select BusinessRefId 
  , cast(ys.y as varchar(4)) + '-' + cast(ys.y + 1 as varchar(4))  Period
  , GoalType, Amount
  , dateadd(year, nmbs.n, tbl.StartDateUtc) StartDateUtc
  , dateadd(year, nmbs.n + 1,tbl.StartDateUtc) EndDateUtc
  , Currency
from 
-- your real table here
(values (1, 'year', 'CommittedTransactionFee', 1800, cast('20160607' as date),  cast (null as date), 'USD')) 
tbl(BusinessRefId, Period, GoalType, Amount, StartDateUtc, EndDateUtc, Currency)
join 
-- table of numbers
(values (0),(1),(2) --,..
 ) nmbs(n)
on dateadd(year, nmbs.n + 1, tbl.StartDateUtc) <= getdate()
cross apply (select Year(tbl.StartDateUtc) + nmbs.n y ) ys

 
select BusinessRefId   , cast(ys.y as varchar(4)) + '-' + cast(ys.y + 1 as varchar(4))  Period  , GoalType, Amount  , dateadd(year, nmbs.n, tbl.StartDateUtc) StartDateUtc  , dateadd(year, nmbs.n + 1,tbl.StartDateUtc) EndDateUtc  , Currencyfrom -- your real table here(values (1, 'year', 'CommittedTransactionFee', 1800, cast('20160607' as date),  cast (null as date), 'USD')) tbl(BusinessRefId, Period, GoalType, Amount, StartDateUtc, EndDateUtc, Currency)join -- table of numbers(values (0),(1),(2) --,.. ) nmbs(n)on dateadd(year, nmbs.n + 1, tbl.StartDateUtc) <= getdate()cross apply (select Year(tbl.StartDateUtc) + nmbs.n y ) ys​

EDIT

See the fiddle This is the version of my query with more conditions regarding which rows needed (note differencies in ON clause) and how to compute end date. It uses Soundappan’s ddl and data (extended) which are allegebly satifactory close to the real ddl and data. The main idea is the same, use a table of numbers. You may want to have instantiated table in your DB to use it in other similar queries.

Advertisement

Answer