Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Calculating daily totals

Status
Not open for further replies.

khoke

Programmer
Jul 8, 2005
20
US
I need to calculate daily totals of the number of employees for a beginning date to the current date.
So, if my start date is 08/01/05, and current date is 08/31/05, then I would need the counts for employees for 08/01, 08/02, .... .... 08/31. How to I get each date to print? The fields I am using in my table are Name, HireDate and TermDate. How do I calculate each day in my date range?
Thanks!
(I am using CR10.) Thanks!
 
Insert a group by the hiredate, create a date parameter, and in the Report->Selection Formulas->Record place something like:

(
{table.date} >= {?MyParameterrDate}
and
{table.date} <= currentdate
)

Place the employee in the details, right click it and select insert->summary->count and select for the Group Level

Now you can suppress the details.

-k
 
But, if I did not hire any employees on 08/02, I still need to count the number of employees working on that day, so if I group by my hire date, 08/02 will not print on the report, and I need every date in my range to print, wether or not I hired anyone on that date.
Does that make sense?
THANKS!
 
If you don't have a table that includes all dates, then you will have to create formulas, one for each day of the period, as in:

//{@startdate}:
if {table.hiredate} <= {?startdate} and
(
isnull({table.termdate}) or
{table.termdate} >= {?startdate}
)
then 1

//{@startdate+1}:
if {table.hiredate} <= {?startdate}+1 and
(
isnull({table.termdate}) or
{table.termdate} >= {?startdate}+1
)
then 1

//{@startdate+2}:
if {table.hiredate} <= {?startdate}+2 and
(
isnull({table.termdate}) or
{table.termdate} >= {?startdate}+2
)
then 1

//etc.

You would then right click on each formula and insert a summary (sum). You could also replace each column label with a formula as in:

//{@col1}:
{?startdate}

//{@col2}:
{?startdate}+1 //etc.

Your record selection formula would look like:

{table.hiredate} <= currentdate and
(
isnull({table.termdate}) or
{table.termdate} >= {?startdate}
)

-LB
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top