Finding the number of Days of Weeks in a Month. 1

[jemsmom]

I'm using Oracle 10 g with Crystal XI.

I am trying to create a room utilization report by room and day of week. I've tried using the formula found in thread767-1387024 by LB to count the number of Mondays, but I'm not returning the desired results. I return 8 mondays in January.

whileprintingrecords;
datevar array Holidays;
datevar range x := date(DateTime({@Start Date})) to date(DateTime({@End Date}));
numbervar i;
numbervar j := day(maximum(x));
numbervar sun;
numbervar mon;
numbervar tue;
numbervar wed;
numbervar thu;
numbervar fri;
numbervar sat;

for i := 0 to j-1 do(
if not(minimum(x)+ i in Holidays) then
select dayofweek(minimum(x)+ i)
case 1 : sun := sun + 1
case 2 : mon := mon + 1
case 3 : tue := tue + 1
case 4 : wed := wed + 1
case 5 : thu := thu + 1
case 6 : fri := fri + 1
case 7 : sat := sat + 1
default : 0
);

I've used the formula below from Ken H. to find the number of business days excluding holidays and that works perfect to find the number of business days:
//Main formula
WhileReadingRecords;
Local DateVar Start := {@Start Date}; // place your Starting Date here
Local DateVar End := {@End Date}; // place your Ending Date here
Local NumberVar Weeks;
Local NumberVar Days;
Local Numbervar Hol;
DateVar Array Holidays;

Weeks:= (Truncate (End - dayofWeek(End) + 1
- (Start - dayofWeek(Start) + 1)) /7 ) * 5;
Days := DayOfWeek(End) - DayOfWeek(Start) + 1 +
(if DayOfWeek(Start) = 1 then -1 else 0) +
(if DayOfWeek(End) = 7 then -1 else 0);

Local NumberVar i;
For i := 1 to Count (Holidays)
do (if DayOfWeek ( Holidays ) in 2 to 6 and
Holidays in start to end then Hol:=Hol+1 );

Weeks + Days - Hol

I tried using this formula to find the number of Fridays, but I'm not sure If I can use this formula and exclude the holiday in January. Is there a way to modify this formula to exclude the holiday or what did I do wrong with the formula lb posted in thread767-1387024

DateDiff ( 'ww' , {@Start Date} - 1 , {@End Date} , 6 )


Thank you for your time.

 

[chelseatech]

Look up the help on DateDiff. It can count the number of days of the week between two dates:

DateDiff ("ww", {Table.StartDate}, {Table.EndDate}, crWednesday)

Will count the wednesdays.

As for public holidays. Crystal had a cool Holidays library they gave away for free. If you can find it on their web site, email me and I'll find a way to get it to you.

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[lbass]

My original formula did not use "whileprintingrecords;" Please remove that.

-LB

 

[jemsmom]

Removing while print records worked.

Thanks Again LB.

 

[jemsmom]

Now I need to exclude surgery dates that were holidays from my weekday total time used columns. I have a formula like this for each weekdayday of the week.

DayofWeek5:

If DayOfWeek({OR_LOG_SURG_DATES.SURGERY_DATES}) = 6
then {@TimeInRange}


DayofWeekCounter:

datevar array Holidays;
datevar range x := date(datetime({?StartDate})) to date(datetime({@End Date}));
numbervar i;
numbervar j := day(maximum(x));
numbervar sun;
numbervar mon;
numbervar tue;
numbervar wed;
numbervar thu;
numbervar fri;
numbervar sat;

for i := 0 to j-1 do(
if not(minimum(x)+ i in Holidays) then
select dayofweek(minimum(x)+ i)
case 1 : sun := sun + 1
case 2 : mon := mon + 1
case 3 : tue := tue + 1
case 4 : wed := wed + 1
case 5 : thu := thu + 1
case 6 : fri := fri + 1
case 7 : sat := sat + 1
default : 0
);

Thank you for your time.

 

[lbass]

Is {@TimeinRange} your name for the dayofweek counter formula? Where are you declaring the Holidays? In {@TimeinRange} you should specify the holidays like this:

datevar array Holidays := [date(2010,1,1),date(2010,7,4),date(2010,11,11)];

-LB

 

[jemsmom]

I apologize for an incomplete post.

The formula for holidays is in the report header:

//Holiday Listing formula to go into the report header of the report.
BeforeReadingRecords;
DateVar Array Holidays := [
Date (2010,01,01),
Date (2010,05,31),
Date (2010,07,05),
];
0

Time In Range Formula:
//Evaluates the amount of case time within a given range.
//Returns the number of minutes in range.

WhileReadingRecords;
local numberVar nTrimBef;
local numberVar nTrimAft;
local dateTimeVar beginRange;
local dateTimeVar endRange;

//The time range can be configured by changing the Time fields below.
beginRange:=DateTime(Date({@Surgery Dates}),{@Shift 1 Start}); //Set time to beginning of desired range. Ex. Time(16,00,00)=4:00 PM
endRange:=DateTime(Date({@Surgery Dates}),{@Shift 1 End}); //Set time to end of desired range. Ex. Time(17,00,00)=5:00 PM

if {OR_LOG_or_in.TRACKING_TIME_IN}>endRange then 0 //Start of case occurs after end of range. No minutes in range.
else if {@Time Out}<beginRange then 0 //End of case occurs before begin of range. No minutes in range.
else
( //Find the minutes not in range (trim amount) and subtract from total time.
nTrimBef:=DateDiff("n",{OR_LOG_or_in.TRACKING_TIME_IN},beginRange); //Time between start of case and begin of range.
nTrimBef:=IIF (nTrimBef>0,nTrimBef,0); //If start of case occurs after begin of range, set trim amount to 0.
nTrimAft:=DateDiff("n",endRange,{@Time Out}); //Time between end of range and end of case.
nTrimAft:=IIF (nTrimAft>0,nTrimAft,0); //If end of case occurs before end of range, set trim amount to 0.
{@Elapsed Time}-(nTrimBef+nTrimAft); //Otherwise, subtract the trim minutes (out of range minutes) from the total case time.
)

 

[jemsmom]

I've added this to the Time In Range formula and it works great.

if (date({@Surgery Dates}) in holidays) then 0

Thank you for your time.