I have to calculate the number of times character A appear in a string between 3 different date ranges. My data consists of contracts and each contract has string fields with a start and end date and each day contains a different character.
Contract: 123
Contract start date: 3/9/2013
Contract end date: 6/9/2013
subsidiary id: 11375
String start date: 3/9/2013
String end date: 20/12/2013
String field: AAAHHAAAHH.......
subsidiary id: 11954
String start date: 9/9/2013
String end date: 31/12/2013
String field: AAAAAHHHHAAAA......
Contract: 345
Contract start date: 9/9/2013
contract end date: 22/7/2014
subsidiary id: 11375
String start date: 3/9/2013
String end date: 20/12/2013
String field: AAAAAAAAAAAAA.....
When users run the report, they will enter a date range and the report is to show them the number of times A appears in each strings in each contract during the selected rates. So if users selected a date range of 1/9/2013 to 31/12/2013, the report should the following numbers of As:
Contract: 123
Contract start date: 3/9/2013
Contract end date: 6/9/2013
subsidiary id: 11375
String start date: 3/9/2013
String end date: 20/12/2013
String field: AAAHHAAAHH.......
**Result: 3As (from 3/9/2013 to 6/9/2013)
subsidiary id: 11954
String start date: 9/9/2013
String end date: 31/12/2013
String field: AAAAAHHHHAAAA......
Result: 0As (as the string start date is after the contract end date)
Contract: 345
Contract start date: 9/9/2013
contract end date: 22/7/2014
subsidiary id: 11375
String start date: 3/9/2013
String end date: 31/04/2014
String field: AAAAAAAAAAAAA.....
**Result: the number of As between 9/9/2013 to 31/12/2013
I can define the start position when the report needs to start calculating but I am struggling with defining where the report needs to stop calculating. Can anyone help me on this please? Any help is greatly appreciated.
Thank you.
Lin
Contract: 123
Contract start date: 3/9/2013
Contract end date: 6/9/2013
subsidiary id: 11375
String start date: 3/9/2013
String end date: 20/12/2013
String field: AAAHHAAAHH.......
subsidiary id: 11954
String start date: 9/9/2013
String end date: 31/12/2013
String field: AAAAAHHHHAAAA......
Contract: 345
Contract start date: 9/9/2013
contract end date: 22/7/2014
subsidiary id: 11375
String start date: 3/9/2013
String end date: 20/12/2013
String field: AAAAAAAAAAAAA.....
When users run the report, they will enter a date range and the report is to show them the number of times A appears in each strings in each contract during the selected rates. So if users selected a date range of 1/9/2013 to 31/12/2013, the report should the following numbers of As:
Contract: 123
Contract start date: 3/9/2013
Contract end date: 6/9/2013
subsidiary id: 11375
String start date: 3/9/2013
String end date: 20/12/2013
String field: AAAHHAAAHH.......
**Result: 3As (from 3/9/2013 to 6/9/2013)
subsidiary id: 11954
String start date: 9/9/2013
String end date: 31/12/2013
String field: AAAAAHHHHAAAA......
Result: 0As (as the string start date is after the contract end date)
Contract: 345
Contract start date: 9/9/2013
contract end date: 22/7/2014
subsidiary id: 11375
String start date: 3/9/2013
String end date: 31/04/2014
String field: AAAAAAAAAAAAA.....
**Result: the number of As between 9/9/2013 to 31/12/2013
I can define the start position when the report needs to start calculating but I am struggling with defining where the report needs to stop calculating. Can anyone help me on this please? Any help is greatly appreciated.
Thank you.
Lin