Date Calculation
I was given the following task and I am not sure if it is just the way it is written or what but I am struggling to understand what is being asked. I haven't done much with complex Date Formulas yet. Can someone steer me in the right direction please?
An app contains two tables of data. Each row in the [Customers] table represents a customer, who has had some unknown number of transactions within the stated period (see [Account Opened Date] and [Account Closed Date]). Only the total amount of these
transactions is known (see [Total Transaction Amount]).
Please spread the [Total Transaction Amount] for each customer evenly in each relevant period in the [Periods] table. By "evenly" we mean that you should allocate the transaction dollars into each period based on the number of days in that period, assuming that the period falls into the range from [Earliest Transaction Date] to [Latest Transaction Date] for that customer.
Additional Guidelines:
Customer Period Title Effective Start Effective End Allocated
Name Date Date Amount
Howard Period E 2017-06-05 2017-06-30 $25,240
Howard Period F 2017-07-01 2017-12-31 $178,620
As you can see in this sample for the first customer (Howard), the total of $332,000 has been allocated
to three periods: Period E, Period F, and then an extra row that accounts for any amount allocated to
dates after Period F. The allocated amounts total $332,000, and are spread into each period according
to the number of days of overlap between that period's start/end dates and the customer's transaction
start/end dates. In this example, only a small amount is allocated to Period E, since the Howard
customer's record has an earliest transaction date of 2017-06-05 and the period's end date is 2017-06-
30. Period F has an allocated amount that corresponds to the number of days in Period F, since there is
complete overlap between the Howard customer's transaction period and the start/end dates of Period
------------------------------
Chris Swirtz
------------------------------
An app contains two tables of data. Each row in the [Customers] table represents a customer, who has had some unknown number of transactions within the stated period (see [Account Opened Date] and [Account Closed Date]). Only the total amount of these
transactions is known (see [Total Transaction Amount]).
Please spread the [Total Transaction Amount] for each customer evenly in each relevant period in the [Periods] table. By "evenly" we mean that you should allocate the transaction dollars into each period based on the number of days in that period, assuming that the period falls into the range from [Earliest Transaction Date] to [Latest Transaction Date] for that customer.
Additional Guidelines:
- Your solution should return the customer name, period title, effective start date and end date, and a calculated [Allocated Amount] column representing the total transaction dollars allocated to that given period. By effective start date and end date, we mean the start and end dates used in the calculation, not necessarily the period's start and end dates. For example, if the period start date precedes the customer's first transaction, the customer's transaction start date should be displayed, otherwise the period start date should be displayed (see the Sample Output)
- Make sure to account for periods in which the overlap in days is incomplete. For example, in the first row of the [Customers] table (Howard), the first transaction occurred on 06/05/2017, which only overlaps the dates of Period E by several days. The amount spread to Period E should correctly reflect that overlap.
- Some customers may have transactions that fall outside of the defined Period A-F dates. You can allow these transactions to drop off since they fall outside of the date range of Periods A-F.
Customer Period Title Effective Start Effective End Allocated
Name Date Date Amount
Howard Period E 2017-06-05 2017-06-30 $25,240
Howard Period F 2017-07-01 2017-12-31 $178,620
As you can see in this sample for the first customer (Howard), the total of $332,000 has been allocated
to three periods: Period E, Period F, and then an extra row that accounts for any amount allocated to
dates after Period F. The allocated amounts total $332,000, and are spread into each period according
to the number of days of overlap between that period's start/end dates and the customer's transaction
start/end dates. In this example, only a small amount is allocated to Period E, since the Howard
customer's record has an earliest transaction date of 2017-06-05 and the period's end date is 2017-06-
30. Period F has an allocated amount that corresponds to the number of days in Period F, since there is
complete overlap between the Howard customer's transaction period and the start/end dates of Period
------------------------------
Chris Swirtz
------------------------------
