Hello all!
So I am working on a project and I've hit a roadblock.
What I want to do is find the number of days that a parcel (identified by 'PREM_CODE') had water service during a two year period.
However, the structure of the data is giving me some issues. Each row in this table represents a water meter reading and whether the water was turned on or off. If the water was turned on, the value in the field 'ACTN_CODE' would be 'IN,' if the meter was being turned off, the value would be 'OUT.'
There are a few assumptions that I need to put into a logic to deal with these that are giving me trouble. Those assumptions are:
If a premises's (PREM_CODE) first chronological event is a turn-on event (i.e. 'ACTN_CODE'='IN'), then use that date should be used as the starting date (Below Example 89 and 99).
If a premises's (PREM_CODE) first chronological event is a turn-off event (i.e. 'ACTN_CODE'='OUT'), then subtract the number of days between the period's first date (7/1/2015) to the date of the event (ACTION_DATE) (Below Example 50,76, and 152).
Then, after that first logic is implemented, I need to count the days between any subsequent 'IN' and 'OUT' events.
If a PREM_CODE's last entry is an 'IN' event, then I need to subtract that 'IN' event's date from the end date, which is 8/1/2017.
And lastly, if a PREM_CODE's last entry is an 'OUT,' then no further calculations should be made.
Here is a sample of what this could look like:
**Start date = 7/1/2015
**End date = 8/1/2017
| PREM_CODE | ACTN_CODE | ACTION_DATE |
|---|
| 50 | OUT | 7/30/2016 |
| 50 | IN | 9/21/2016 |
| 89 | IN | 10/10/2015 |
| 76 | OUT | 12/1/2015 |
| 76 | IN | 1/1/2016 |
| 76 | OUT | 9/1/2016 |
| 76 | IN | 5/5/2017 |
| 99 | IN | 7/30/2015 |
| 152 | OUT | 7/1/2017 |
| | |
Each of these has its own unique situation.
For PREM_CODE=50, the calulcation would be: (7/1/2015-7/30/2016)+(9/21/2016-8/1/2017)
For PREM_CODE=89, the calculation would be: (10/10/2015-8/1/2017)
For PREM_CODE=76, the calculation would be:(7/1/2015-12/1/2015)+(1/1/2016-9/1/2016)+(5/5/2017-8/1/2017)
For PREM_CODE=99, the calculation would be: (7/30/2015-8/1/2017)
For PREM_CODE=152, the calculation would be: (7/1/2015-7/1/2017)
Any and all help would be appreciated!
