Counting Once for Each Day a Record Spans

[MrBillSC]

Counting Once for Each Day a Record Spans

For any given Month/Year, report how many records were open for each day of the month. Each record has an Open and Close date.

Data:
PO # Opened Date Closed Date
1234 1/10/06 1/14/06
1278 1/13/06 1/18/06
4567 1/13/06 NULL

Resolution:
Accumulate open POs for each day of the month in the detail section and report the results in the report footer.

This will require quite a few formulas to accommodate all the days of the month. In this example the report prompts the user for the year and month to report.

Create a formula for the beginning day of the month to report based on user prompts.
// formula: {@PromptBegDate}
Date({?Year},{?Month},01)

Create a formula for the ending day of the month to report based on user prompts.
// formula: {@PromptEndDate}
Date(DateAdd("m",1,{@PromptBegDate}) - 1)

The record select formula will select records with an open date <= the last day of the month being reported and a close date >= the first day of the month being reported.

// Record Select Formula:
{POTable.Open_Date} <= {@PromptEndDate} and
(IsNull({POTable.CloseDate}) or
{POTable.CloseDate} >= {@PromptSTDate})

Create a formula to extract the open Year of the PO record.
// formula {@PoOpenYr}
Year(POTable.Open_Date)

Create a formula to extract the open Month of the PO record.
// formula {@PoOpenMo}
Month(POTable.Open_Date)

Create a formula to extract the open Day of the PO record.
// formula {@PoOpenDay}
Day(POTable.Open_Date)

Create a formula to extract the close Year of the PO record. If IsNull then add 1 to {?Year} as PO is still open.
// formula {@PoCloseYr}
If IsNull({POTable.CloseDate}) then {?Year} + 1 else
Year(POTable.Close_Date)


Create a formula to extract the close Month of the PO record.
// formula {@PoCloseMo}
If IsNull({POTable.CloseDate}) then {?Month} else
Month(POTable.Close_Date)

Create a formula to extract the close Day of the PO record.
// formula {@PoCloseDay}
If IsNull({POTable.CloseDate}) then 1 else
Day(POTable.Close_Date)

The following formula accumulates the count for each day of the month (31) based on the open and close date of the record.

WhilePrintingRecords;

NumberVar Day01;
NumberVar Day02;
NumberVar Day03;
NumberVar Day04;
NumberVar Day05;
NumberVar Day06;
NumberVar Day07;
NumberVar Day08;
NumberVar Day09;
NumberVar Day10;
NumberVar Day11;
.
.
.
NumberVar Day28;
NumberVar Day29;
NumberVar Day30;
NumberVar Day31;

If {@PoOpenDay} = 1 or {@PoOpenMo} < {?Month} or {@PoOpenYr} < {?Year} then
Day01:= Day01 + 1 else Day01;

If ({@PoOpenDay} <= 2 or {@PoOpenMo} < {?Month} or {@PoOpenYr} < {?Year}) and
({@PoCloseDay} >= 2 or {@PoCloseMo} > {?Month} or {@PoCloseYr} > {?Year}) then
Day02:= Day02 + 1 else Day02;

If ({@PoOpenDay} <= 3 or {@PoOpenMo} < {?Month} or {@PoOpenYr} < {?Year}) and
({@PoCloseDay} >= 3 or {@PoCloseMo} > {?Month} or {@PoCloseYr} > {?Year}) then
Day03:= Day03 + 1 else Day03;

If ({@PoOpenDay} <= 4 or {@PoOpenMo} < {?Month} or {@PoOpenYr} < {?Year}) and
({@PoCloseDay} >= 4 or {@PoCloseMo} > {?Month} or {@PoCloseYr} > {?Year}) then
Day04:= Day04 + 1 else Day04;

If ({@PoOpenDay} <= 5 or {@PoOpenMo} < {?Month} or {@PoOpenYr} < {?Year}) and
({@PoCloseDay} >= 5 or {@PoCloseMo} > {?Month} or {@PoCloseYr} > {?Year}) then
Day05:= Day05 + 1 else Day05;

If ({@PoOpenDay} <= 6 or {@PoOpenMo} < {?Month} or {@PoOpenYr} < {?Year}) and
({@PoCloseDay} >= 6 or {@PoCloseMo} > {?Month} or {@PoCloseYr} > {?Year}) then
Day06:= Day06 + 1 else Day06;
.
.
.

If ({@PoCloseDay} = 31 or {@PoCloseMo} > {?Month} or {@PoCloseYr} > {?Year}) then
Day31:= Day31 + 1 else Day31;


Create 31 formulas to display the data.
// formula Display01
WhilePrintingRecords;
NumverVar Day01;
Day01
.
.
.
// formula Display31
WhilePrintingRecords;
NumverVar Day31;
Day31

In the report footer place the data for the display formulas 01/28. Add 3 subsections to the report footer. In subsetion 'b' place the display formula for day 29. In subsection 'c' place the display formula for day 30, and in subsection 'd' place the display formula for day 31. Conditionally suppress subsection b - d depending on the number of days in the month reported.

MrBill