CODEWITHSUNDEEP
sqldb2date-range
Anthony
Anthony

Reputation: 169

Splitting a date range into different fiscal years

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!

Upvotes: 0

Views: 513

Answers (1)

Neeraj Agarwal
Neeraj Agarwal

Reputation: 1059

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

Upvotes: 1

Related Questions