I have a problem in the insurance space. We need to count the number of Claims each month that have a Payment registered within that month or the two previous months. The user may filter the results by Payment Type and it should only count Claims there the Payment has been of that type.
Pseudo SQL for the calculation is:
Count(distinct ClaimId) where exists (Payment where TransactionDate between [Month End - 3 months + 1 day] and [Month End] and PaymentType in (??))
Simplified sample data:
ClaimId | PaymentId | PaymentAmount | TransactionDate | PaymentType |
1 | 1 | 100 | 1 Jan 2013 | A |
1 | 2 | 150 | 1 Feb 2013 | A |
1 | 3 | 1000 | 1 Mar 2013 | B |
1 | 4 | 100 | 1 Mar 2013 | A |
1 | 5 | 100 | 1 Apr 2013 | B |
2 | 5 | 100 | 1 Feb 2013 | A |
2 | 6 | 100 | 1 Mar 2013 | A |
3 | 7 | 100 | 1 Dec 2012 | A |
If the user makes no selections in PaymentType, then the results should be:
Month | Count |
Jan 2013 | 2 |
Feb 2013 | 3 |
Mar 2013 | 2 |
April 2013 | 2 |
May 2013 | 2 |
Jun 2013 | 1 |
If the user selects PaymentType ‘B’, then the results should be:
Month | Count |
Jan 2013 | 0 |
Feb 2013 | 0 |
Mar 2013 | 1 |
April 2013 | 1 |
May 2013 | 1 |
Jun 2013 | 1 |
I do not think that RangeSum will work as it will calculate the first two months in the chart correctly.
