Date Ranges 1

[cdennis321]

CR 8.5 Oracle

I am working on a report that counts the number of absences each operator has. The number of absences is dependent upon the number of working days between absences. If an operator has less than 30 working days between absences then the absence is count if more than 30 then the absence is not counted. My dilemma is that the absence date and the work date comes from 2 separate tables. If I group the absence days then all the work days fall under the group. I need to group the work days between the previous absence date to the next absence date so that I can count them. Does anyone have any idea of how to do this?

 

[synapsevampire]

Do a left outer join from your work date table.date to your absence date table.date.

Now you'll have all dates, with data for those with absences.

Now you can use a counter to determine which ones need to be displayed, as in:

//assumed grouping by the operator:

//Operator group header formula:
whileprintingrecords;
numbervar MyCount:= 0;
numbervar TotAbsences:=0;

//details formula:
whileprintingrecords;
numbervar MyCount:=MyCount+1;
numbervar TotAbsences;
If MyCount > 30 then
MyCount:= 0;
If not(absence.date) = null
and
MyCount <> 0 then
TotAbsences:=Totabsences+1;

//Group footer
whileprintingrecords;
numbervar TotAbsences

Should get you close.

-k

 

[lbass]

I would place the absences table in the main report, grouping on operator (GH#1) and then on date (GH#2). Go to database->check "Select distinct records". Then insert a second section for group#2 and insert a subreport which you will place in GH#2b. The subreport should contain the work dates table, with the work dates field displayed. Link the subreport to the main report on the operator field only.

In the main report, create the following formulas and place them in the noted sections:

//{@resetabs} to be placed in GH#1:
whileprintingrecords;
shared numbervar cntabs := 0;

//{@resetwkdates} to be placed in GF#2:
whileprintingrecords;
shared numbervar sumwkdates := 0;

//{@firstnextdates} to be placed in GH#2a:
whileprintingrecords;
shared datevar firstdate := {absence.date};
shared datevar nextdate := next({absence.date});

//{@absences} to be placed in the GF#1:
whileprintingrecords;
shared numbervar sumwkdates;
shared numbervar cntabs;

if sumwkdates < 30 then
cntabs := cntabs + 1;

In the subreport, create two formulas:

//{@wkdatesinrange} to be placed in the detail section:
whileprintingrecords;
shared datevar firstdate;
shared datevar nextdate;
shared numbervar sumwkdates;

if {work.date} >= firstdate and
{work.date} < nextdate then
sumwkdates := sumwkdates + 1;

//{@display} to be placed in the subreport footer:
whileprintingrecords;
shared numbervar sumwkdates;

You can suppress all sections within the subreport if you wish, and all formulas within the main report except {@absences}. In the section expert of the main report, format GH#2a to "underlay following sections" so that the space for the subreport doesn't show.

-LB

 

[cdennis321]

Thanks lbass. It worked!