Hi,
I am looking to split each line item in a fairly large data set into annual quarters as each line item contains a date range spanning a few years.
I need to split them into quarterly data because I have quarterly rates available to me which I need subsequently multiply by the line item and sum them to get my total cost per line item.
Any help splitting the data into quarters based on the Start Date and End Date will be much appreciated.
Following is an example of the data.
| Item no. | Quantity | Receipt Date | Date Of Payment |
| 1 | 26 | 2015-10-21 | 2017-03-27 |
| 2 | 23 | 2009-11-01 | 2017-02-03 |
| 3 | 253 | 2016-11-29 | 2017-06-02 |
Following is the table with rates:
| Date Start | Date End | Rate | |
| April 1, 2019 | June 30, 2019 | 0.06 | Q2,2019 |
| January 1, 2019 | March 31, 2019 | 0.06 | Q1,2019 |
| October 1, 2018 | December 31, 2018 | 0.05 | Q4,2018 |
| July 1, 2018 | September 30, 2018 | 0.05 | Q3,2018 |
| April 1, 2018 | June 30, 2018 | 0.05 | Q2,2018 |
| January 1, 2018 | March 31, 2018 | 0.04 | Q1,2018 |
| October 1, 2017 | December 31, 2017 | 0.04 | Q4,2017 |
| July 1, 2017 | September 30, 2017 | 0.04 | Q3,2017 |
| April 1, 2017 | June 30, 2017 | 0.04 | Q2,2017 |
| January 1, 2017 | March 31, 2017 | 0.04 | Q1,2017 |
| October 1, 2016 | December 31, 2016 | 0.04 | Q4,2016 |
| July 1, 2016 | September 30, 2016 | 0.04 | Q3,2016 |
| April 1, 2016 | June 30, 2016 | 0.04 | Q2,2016 |
| January 1, 2016 | March 31, 2016 | 0.03 | Q1,2016 |
| October 1, 2015 | December 31, 2015 | 0.03 | Q4,2015 |
| July 1, 2015 | September 30, 2015 | 0.03 | Q3,2015 |
| April 1, 2015 | June 30, 2015 | 0.03 | Q2,2015 |
| January 1, 2015 | March 31, 2015 | 0.03 | Q1,2015 |
| October 1, 2014 | December 31, 2014 | 0.03 | Q4,2014 |
| July 1, 2014 | September 30, 2014 | 0.03 | Q3,2014 |
| April 1, 2014 | June 30, 2014 | 0.03 | Q2,2014 |
| January 1, 2014 | March 31, 2014 | 0.03 | Q1,2014 |
| October 1, 2013 | December 31, 2013 | 0.03 | Q4,2013 |
| July 1, 2013 | September 30, 2013 | 0.03 | Q3,2013 |
| April 1, 2013 | June 30, 2013 | 0.03 | Q2,2013 |
| January 1, 2013 | March 31, 2013 | 0.03 | Q1,2013 |
| October 1, 2012 | December 31, 2012 | 0.03 | Q4,2012 |
| July 1, 2012 | September 30, 2012 | 0.03 | Q3,2012 |
| April 1, 2012 | June 30, 2012 | 0.03 | Q2,2012 |
| January 1, 2012 | March 31, 2012 | 0.03 | Q1,2012 |
| October 1, 2011 | December 31, 2011 | 0.03 | Q4,2011 |
| July 1, 2011 | September 30, 2011 | 0.04 | Q3,2011 |
| April 1, 2011 | June 30, 2011 | 0.04 | Q2,2011 |
| January 1, 2011 | March 31, 2011 | 0.03 | Q1,2011 |
| October 1, 2010 | December 31, 2010 | 0.04 | Q4,2010 |
| July 1, 2010 | September 30, 2010 | 0.04 | Q3,2010 |
| April 1, 2010 | June 30, 2010 | 0.04 | Q2,2010 |
| January 1, 2010 | March 31, 2010 | 0.04 | Q1,2010 |
| October 1, 2009 | December 31, 2009 | 0.04 | Q4,2009 |
| July 1, 2009 | September 30, 2009 | 0.04 | Q3,2009 |
| April 1, 2009 | June 30, 2009 | 0.04 | Q2,2009 |
| January 1, 2009 | March 31, 2009 | 0.05 | Q1,2009 |
| October 1, 2008 | December 31, 2008 | 0.06 | Q4,2008 |
| July 1, 2008 | September 30, 2008 | 0.05 | Q3,2008 |
| April 1, 2008 | June 30, 2008 | 0.06 | Q2,2008 |
| January 1, 2008 | March 31, 2008 | 0.07 | Q1,2008 |
| October 1, 2007 | December 31, 2007 | 0.08 | Q4,2007 |
| July 1, 2007 | September 30, 2007 | 0.08 | Q3,2007 |
| April 1, 2007 | June 30, 2007 | 0.08 | Q2,2007 |
| January 1, 2007 | March 31, 2007 | 0.08 | Q1,2007 |
| October 1, 2006 | December 31. 2006 | 0.08 | Q4,2006 |
| July 1, 2006 | September 30, 2006 | 0.08 | Q3,2006 |
| April 1, 2006 | June 30, 2006 | 0.07 | Q2,2006 |
| January 1, 2006 | March 31, 2006 | 0.07 | Q1,2006 |
| October 1, 2005 | December 31, 2005 | 0.07 | Q4,2005 |
| July 1, 2005 | September 30, 2005 | 0.06 | Q3,2005 |
| April 1, 2005 | June 30, 2005 | 0.06 | Q2,2005 |
