# Number of jobs per day

Dear all,

I am creating a report where I have to calculate number of jobs per day and show the percentages of each job.

I have created a formula within a report which shows the number of days and also shows the number of jobs per day along with the percentage. However, the resulted jobs are not right.

The total number of jobs in a particular month are appearing right however, the numbers (per job in a particular day is not correct).

For example, There are 2 jobs which took 22 days to complete (shipped) to customer. One job order was placed on the 05/06/2008 and 06/06/2008 respectively. The jobs were shipped to the customer on the 04/07/2008 and 10/06/2008 respectively.

Now when I run a report with the date range of (01/06/2008 - 30/06/2008), the report shows two jobs took 22 days however, in theory it should show only 1 job took 22 days as we have limit it with the date range parameter.

This problem leads to all inaccurate results per job for the particular days, though end result comes up right.

Here is my code which I use to calculate the number of days, the similar code I use (Count(formula)) to get number of jobs on a particular day.

So to summarise it all up

Report appears with:

Parameters are:
Start Date:
End Date:
Customer Account number:

Report layout:

Total Jobs, Canceled jobs, Credit Jobs, In Stock Jobs

GroupName (NumofDays) - This calculates the number of days
Count(NumofDays)  - This gives me number of jobs per day
Count(NumofDays) % Total Jobs  - This gives me percentage

Fields used in Formula:

Order_header.date_entered = Start Date - This is a start date
Order_header.act_despatch = Shipped Date - When the order was shipped

Could you please help me sort this code out so that it should filter the records according to the date range?

Many thanks

Kind Regards

Jehanzeb

``````WhileReadingRecords;

Local DateVar Start := {order_header.date_entered};   // Starting Date
Local DateVar End := {order_header.act_despatch};  // Ending Date
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);

//Check for bank holidays
Local NumberVar i;
For i := 1 to Count (Holidays)
do (if DayOfWeek ( Holidays[i]) in 2 to 6 and
Holidays[i] in start to end then Hol:=Hol+1 );

Weeks + Days - Hol;
``````
Crystal Reports

Last Comment
jehanzebn
Mike McCracken

DO you want to show only completed jobs or should the incomplete job (as of 30 Jun 08 be counted but shown as fewer days?

If you want to count it you need to test the finish date in the days calculation
Something like
If End > {?EndDate} then
End := {?EndDate}

mlmcc
If you only want the jobs started and completed in the time frame then
Use the record selection as

and

mlmcc
jehanzebn

Morning mlmcc,

Many thanks for your quick and informative response. I have tried both methods however, the first one seems much more closer to what I am looking for instead of second one.

After trying the first method, I am still getting the wrong number of jobs, I thought it must be because I am putting the code in the wrong place. I tried putting the code before declaring Week: and Days:, tried putting it in between Weeks: and Days: and tried putting at the end of the code. In all methods the results were different (obvious thing).

The second method allows to restrict the total jobs which went through the system and that is the reason I didn't use it (Well I tried it but the results were wrong so I took it off).

I think I need to alter the first method in a way that if the job gets over the end date i.e. End of order date, it should not include the job. Currently, Day 22 has 4 jobs, 2 of which were shipped in July and not June month though the orders were placed in June. When I tried your method of End Date, it now shows Day 22 - 3 Jobs, it has in fact taken 23rd day's job and inserted it into the 22nd day.
I then moved the code at the end of the coding, it is now back to square one, showing 22 Days - 4 jobs.

Here are the Jobs and dates of those jobs (just to give a clear idea)

Date Range: 01/06/2008 - 30/06/2008 ( This range is setup using parameters of Start and End Date (dates used of Order date from the database to get all the orders within that month))

Day 22

1545066 Order Date : 05/06/2008 - Shipped Date: 04/07/2008
1545455 Order Date: 06/06/2008 - Shipped Date: 10/06/2008
1545456 Order Date: 06/06/2008 -  Shipped Date: 10/06/2008
6136603 Order Date: 05/06/2008 - Shipped Date: 04/07/2008

Now if you look at this, the two orders placed on the 5th June were shipped on the 4th July but in between them, the orders placed on the 6th June were shipped on the 10th of June. In theory it should only show 2 orders within that date range, however, using your method it doesn't reflect the right results.

Any ideas what I should be doing ( i am sure i am doing something wrong here).

Code:

``````WhileReadingRecords;

Local DateVar Start := {order_header.date_entered};   // Starting Date
Local DateVar End := {order_header.act_despatch};  // Ending Date
Local NumberVar Weeks;
Local NumberVar Days;
Local Numbervar Hol;
DateVar Array Holidays; //bank holidays dates array

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);

//Check for bank holidays
Local NumberVar i;
For i := 1 to Count (Holidays)
do (if DayOfWeek ( Holidays[i]) in 2 to 6 and
Holidays[i] in start to end then Hol:=Hol+1 );

If End > {?End Date} then
End := {?End Date};

Weeks + Days - Hol;
``````
jehanzebn

Another thing which I found out might give a clear idea (maybe) what I am doing and what is needed:

The formula is only calculating the difference in days between the order and dispatch date excluding weekends and holidays. I still need a formula that counts how many jobs took 22 days and how many took 4. Though I know this however, I don't know how to implement this.

Many thanks

Kind Regards

Jehanzeb
jehanzebn

Ok I have found out that the code I am using calculates the working days however does not take "Saturday" and "Sunday" particularly in account, instead it counts number of days as 5.

I didn't realize that Crystal takes first working day as Sunday!!!!....

anyhow, I don't think the approach is right either as with an array I won't be able to add dates later (unless or otherwise I have to hard code the dates again) instead using a table would be much more appropriate approach.

I have seen the following code:

I think this will suit me best however, I am not sure how to implement this code into my report.

any ideas how to do this?

many thanks

Regards

Jehanzeb
``````CREATE FUNCTION "fnGetBusinessDays"
(
@startdate datetime,
@enddate datetime
)
RETURNS integer
AS
BEGIN
DECLARE @days integer

SELECT @days =
DATEDIFF(d,@startdate,@enddate)
- DATEDIFF(wk,@startdate,@enddate) * 2
- CASE
WHEN DATENAME(dw, @startdate) <> 'Saturday' AND DATENAME(dw, @enddate) = 'Saturday' THEN 1
WHEN DATENAME(dw, @startdate) = 'Saturday' AND DATENAME(dw, @enddate) <> 'Saturday' THEN -1
ELSE 0
END
- (SELECT COUNT(*) FROM holidays WHERE bankholiday BETWEEN @startdate AND @enddate AND DATENAME(dw, bankholiday) <> 'Saturday' AND DATENAME(dw, bankholiday) <> 'Sunday')

RETURN (@days)

END
GO

/*
--HOLIDAYS TABLE CREATION SCRIPT
--YOU MAY NEED TO ALTER THIS TO SUIT YOUR NEEDS
--you will need to create a holidays table to store your holiday data in
CREATE TABLE holidays(
bankholiday datetime NOT NULL,
CONSTRAINT PK_holidays PRIMARY KEY CLUSTERED (bankholiday)
)
INSERT INTO holidays (bankholiday) SELECT '03/01/2008' UNION SELECT '05/01/2008' --05/01/2008 is a sunday
--end holiday table creation script
*/

--TEST DATA
DECLARE @start datetime, @end datetime
SET DATEFORMAT DMY --use this to temporarily set the date order to day month year

SET @start = '01/01/2008'
SET @end = '01/01/2008'

``````
Did you use the formula in the select expert

REPORT --> SELECTION FORMULA --> RECORD

mlmcc
jehanzebn

Yep! The formula was used in Report Selection Formula Record. However, that didn't work.

Regards

Jehanzeb
Is the report saved with data?

mlmcc
jehanzebn

Yes!  The data is saved with the report though the report is not yet completed I am working on it and this is a part of the work. I have used yet another formula in which I brought all sort of combinations to avoid the weekends however, it still doesn't work. It works fine on weekdays but when weekends comes in the middle it just doesn't like to work. for example if job starts on Thursday and finishes on the Tuesday, that is 4 days and not 6 days however the code works out it to be 6 days.

Its driving me crazy to be honest!, I also have to make sure that the holidays (bank holidays) given in a database table should be included but how can I move onto that side unless I fix these days problem.

Regards

Jehanzeb
Have you tried refreshing the data?

mlmcc
jehanzebn

Yes! manier times. I don't think it is to do with refreshing data. It is to setup the code correctly so that it counts the working days (Monday to Friday) and take the Sunday and Saturday out everytime it counts the days and the jobs.

The Crystal is coded in such a way that it counts Sunday as 1, Monday as 2 ..... Saturday as 7. I have made the code, so it skips the days however, it is still not working for some reason. I made the code so that if it lies to Saturday it counts 2 more to it and make it monday.

I will send you the updated code tomrow morning when  I get back to work.

Regards

Jehanzeb
jehanzebn

Closing this issue as it did not resolved on time. I have managed to work it out anyway.

Many thanks

Kind Regards

Jehanzeb
jehanzebn

The query is resolved.
What was the resolution?

mlmcc
jehanzebn

Evening Mlmcc,

Sorry for not posting the solution here I was quite busy and then suddenly I received email saying if you don't respond it will close, so I quickly wrote that it has been solved and then left it.

I will post the resolution tomorrow from work. Though the number of jobs are fixed I am struck with another problem, A problem of multiple optional parameters but I don't think I should be asking that question in the same thread or can I?

Regards

Jehanzeb
It would probably be better to start a new question since that would get other experts attention.  If asked here you would probably be stuck with the experts already engaged.

mlmcc
jehanzebn

Yes! that is why I am going to start a new one.

Many thanks

Regards

Jehanzeb
jehanzebn

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Crystal Reports

Crystal Reports is a business intelligence application from SAP SE. It is used to graphically design data connections and report layouts from a wide range of data sources including Excel spreadsheets, Oracle, SQL Server databases and Access databases, BusinessObjects Enterprise business views, and local file-system information. Report designers can place fields from these sources on the report design surface, and can also deploy them in custom formulas (using either BASIC or Crystal's own syntax), which are then placed on the design surface. Crystal Reports also supports subreports, graphing, and a limited amount of GIS functionality.

36K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts

TRUSTED BY