Query by Intervals within Date Range
Posted on 2001-08-08
Start Date, Stop Date
If a Stop Date is Null, the record is active still.
1. Report Period (week, quarter), for this example week.
2. The Interval (month, quarter, year), for this example month
3. The Range of Start Dates (ie. 1/1/00 to 6/1/00)
Output (could be by row or column):
Percent Active:PA |PA |PA |PA |PA |...PA
Where PA = the % of active records at the Interval.The PA % = (Count(Active Records)/Count(Active + Inactive Records)) FOR Starts withing the specified Week!
In this example:
Starts from 1/29/00 to 2/4/00 would be measured to find the PA at the 1 month mark. 2/5 to 2/11, 2/12 to 2/19 etc.
Active record is one where the stop date IS NULL or the (stop date - start date)>Interval (In this case one month).
An Inactive Record = (stop date - start date)<Interval