Allocating date ranges to specific months

I have a very large table of data that includes, among other columns, start date, end date, number of days and a cost associated to each day covered.  My goal is to allocate each range to the individual months they were covered and then total those months.  For example:

ABC.  4/21/15.   5/6/15  16 days   $.67

XYZ   5/5/15.     8/28/15. 116 days.  $.72

ABC was covered in April and May.  10 days in April and 6 in may

XYZ was covered in may (26), june (30),july (31) and August (28)

I have 1 in April, 2 in may, 1 in June, 1 in July, 1 in august.

 

I have 120,000+ records from early 2015 through March of 2020.  Goal is to track covered lives by month using the ranges and/or days.  I know I am overthinking this and can’t get out of my own way.  Any help is very appreciated! 

By: Brian

Leave a Reply

Your email address will not be published. Required fields are marked *