Splitting a date range into different fiscal years

Question

I have a dataset:

Quantity         Start date               End date
   1              2012-03-28              2012-05-30
   7              2012-08-15              2012-12-15

I would like to sum and group their quantity together using the fiscal year, which starts at the 1st of each April and end 31st of March next year. I.e.

 Fiscal Year    Quantity    
  2012            3000
  2013            1788

I attempt to create an attribute fiscal year but encounter the issue that the date range may fall between 2 fiscal year. ( First row of original dataset and for that instance, we would need to count the quantity twice, once in 2011 and the other in 2012.)

May I know if there are ways to perform such operations?

Thank You!

You may use your base table alone without an additional table like fiscalYear below. Do you need such a solution? — Mark Barinstein

Neeraj Agarwal Neeraj Agarwal · Accepted Answer · 2019-09-03T03:51:26

You can try the following by creating a fiscalYear table. The logic breaks if the quantity start date and end date span more than two fiscal years.

fiscalYear(Year, StartDate, EndDate)

insert fiscalYear values
(2011, "2011-04-01", "2012-03-31"),
(2012, "2012-04-01", "2013-03-31"),
(2013. "2013-04-01", "2014-03-31")

select t1.Year, sum(t2.Quantity)
from fiscalYear t1
inner join myTable t2 on (convert(datetime, t2.StartDate) >= convert(datetime, t1.StartDate)
and convert(datetime, t2.StartDate) <= convert(datetime, t1.EndDate) )
or
(convert(datetime, t2.EndDate) >= convert(datetime, t1.StartDate)
and convert(datetime, t2.EndDate) <= convert(datetime, t1.EndDate) )
group by t1.Year

Splitting a date range into different fiscal years

1 Answers