Number of jobs per day

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

{order_header.date_entered} in {?StartDate} to {?ENdDate}
and
{order_header.act_despatch} in {?StartDate} to {?ENdDate}

mlmcc

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;

Select allOpen in new window

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

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'
 
SELECT dbo.fnGetBusinessDays(@start, @end)

Select allOpen in new window

Did you use the formula in the select expert

REPORT --> SELECTION FORMULA --> RECORD

mlmcc

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

Regards

Jehanzeb

Is the report saved with data?

mlmcc

Thanks for the reply Mlmmcc,

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

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

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

Many thanks

Kind Regards

Jehanzeb

The query is resolved.

What was the resolution?

mlmcc

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

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

Many thanks

Regards

Jehanzeb