Take Bank Holidays out

[Former Member]

Dear all,

how can I count for Bank holidays when counting number of working days under my formula?

I have "Number of days" category in my report and I would like to count only working days, that is Monday to Friday ( Weekends and Bank holidays).

I can take the weekends off by using "dayofweek" formula but how can you actually take Bank holidays out?

Using Formula:

if {order_header.order_status}>= 77 then
if dayofweek({order_header.act_despatch})<9 then
1
else
2;
if dayofweek({order_header.act_despatch}) <= 8 then
totext(dayofweek({order_header.act_despatch}),0,'')
else
"9+";

many thanks

Regards

Jehanzeb

[Former Member · Accepted solution]

I think the only way to go about this would be to create another formula specifying the bank holidays and then to have the original formula substract them out if they fall during a weekday.

[Former Member]

don't know where this thread is going! so closing!

[Former Member]

sub select in report query:

(select count(table.date) from table where table.date in [#12/25/Year(CurrentDate), #01/01/Year(CurrentDate#, etc]) As BankHolidays

[former_member260594]

Jehanzeb,

There was an additional UFl available, U2LCOM.dll I believe, that would read a text file of holidays.

However I found that assigning Holidays to a date array to be just as easy.

You could even create a custom function in CR9 and above that would check if a date was equal to a list of Holidays.

[Former Member]

************

Please see my edited notes

************

Thanks Graham for your informative answer. Yes! I found a file U2LCOM.dll however, I am not sure how to go about setting the holidays.

I have tried Pandabear idea as well but I do not think I properly understood his theory.

Specially when he mentioned, (table.date) from table??

I thought it meant "Order_Date" field from table "Order" however that is not the case.

Is there any working example which I can follow? I wouldn't mind if it is from CR's own help file as I have searched it and I couldn't find any.

Many thanks

Kind Regards

Jehanzeb

Edited by: Jehanzeb Navid on Jul 22, 2008 11:36 AM

Ok discard what I said just up there.

I have found a table (from 3000 tables in our company database) named Bank Holidays which contains all the dates of bank holidays.

My next question is,

How can I use DayofWeek and Bank holiday field within Datediff formula?

I have a formula (Thanks to Graham)

if {order_progress.order_status}= 77 then
if datediff('d',{order_header.date_entered},{order_progress.date_created}) < 9 then
1
else
2;
if datediff('d',{order_header.date_entered},{order_header.act_despatch}) <= 8 then
totext(datediff('d',{order_header.date_entered},{order_header.act_despatch}),0,'')
else
"9+";

How can I integrate weekdays and bank holidays within this formula?

many thanks

Kind Regards

Jehanzeb

Edited by: Jehanzeb Navid on Jul 22, 2008 11:46 AM

A poor try I would say but it might make sense to some of you

if {order_progress.order_status}= 77 then
if datediff('d',{order_header.date_entered},{order_progress.date_created}) < 9 then
DayOfWeek ({order_header.date_entered},crMonday ) - day({bank_hol.holiday_date}) and
1
else
2;
if datediff('d',{order_header.date_entered},{order_header.act_despatch}) <= 8 then
totext(datediff('d',{order_header.date_entered},{order_header.act_despatch}),0,'')
else
"9+";

Edited by: Jehanzeb Navid on Jul 22, 2008 11:51 AM

another try and this time the error says, A boolean is required here (at the place of Bank holiday field).

if {order_progress.order_status}= 77 then
if datediff('d',{order_header.date_entered},{order_progress.date_created}) < 9 then
1
else
2;

if datediff('d',{order_header.date_entered},{order_header.act_despatch}) <= 8 then
totext(datediff('d',{order_header.date_entered},{order_header.act_despatch}),0,'')
else
"9+";

if DayOfWeek ({order_header.date_entered},crMonday ) then
datediff('d',{order_header.date_entered},{order_header.act_despatch})-date({bank_hol.holiday_date});

any ideas?

[Former Member]

I am trying number of ways however none seem to be working ( I know it is my lack of knowledge) but can someone please help!

Below is the code I am trying to take Bank holidays out of number of working days.

if Count ({bank_hol.holiday_date}) > 0 then
{order_header.date_entered}={order_header.date_entered}- {bank_hol.holiday_date}

Error Message:

Date is required.

Edited by: Jehanzeb Navid on Jul 22, 2008 12:47 PM

Ok this time there are no errors but it has messed up the total number of jobs and kept the number of days the same.

weekday({order_progress.date_created},crMonday)- day({bank_hol.holiday_date});
weekday({order_header.date_entered},crMonday)- day({bank_hol.holiday_date});
if {order_progress.order_status}= 77 then
if datediff('d',{order_header.date_entered},{order_progress.date_created}) < 9 then
1
else
2;

if datediff('d',{order_header.date_entered},{order_header.act_despatch}) <= 8 then
totext(datediff('d',{order_header.date_entered},{order_header.act_despatch}),0,'')
else
"9+";

[Former Member]

If you have all the bank holidays in a table, then just

-Count all the working days

-Count all the bank holidays ("distinct")

-Subtract Count(BankHolidays) from WorkingDays

[Former Member]

I found this on Microsoft's MSDN Library:

[http://msdn.microsoft.com/en-us/library/ms189794.aspx]

UDF to return the number of business days, including a check to a bank holidays table

--author paul langan
--contact pflangan at gmail dot commmmmm
--date 2008-07-09
--function to get the number of business days between to dates
--if you pass the same date, then 0 will be returned as the number of days

--to illustrate the example, I've added a temporary table to hold the holidaydates,
--in practice this holiday date should already exist in the database and be pre populated with the bank holiday dates
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)

I've never actually tried it but it seems to make sense. Hope it helps,

Jason

[Former Member]

Jason,

you have given a wealth of information there, however I don't even know where to start it.

I am just wondering how to implement his theory of Weekends and bank holidays into my coding.

I do have a table with all the bank holidays, and I do have parameters of start date and end date however, when I tried to create a function in the report, it didn't allow me to add those parameters into the function (I guess you cant really do that in normal way either).

I am wondering how to go about this now, I am sure I will be able to get somewhere with this for sure.

Many thanks

Kind Regards

Jehanzeb

[Former Member]

Jehanzeb,

Like I said, I haven't had the chance to try it out or test it myself. I was doing some research on the DATEDIFF function and came accross this. For whatever reason I remembered reading your post and figured it might help.

I'd start by simply stepping through the code and seeing what fields correlate with your own data. I'd also check all the details. I just glanced at the code and it looked like it was missing a couple of = signs.

A Google search my give you some other alternatives as well.

Jason

[Former Member]

Jason,

Once again you have done a great job (you might not be aware of this but you mentioned something which trigger something and I got my working day working...well sort of).

Here is the link which I think is much more better and easier to implement,

The number of "Work Days" between two dates:

[http://www.kenhamady.com/form01.shtml]

Adding/Subtracting a Number of Business Days:

[http://www.kenhamady.com/form17.shtml]

Now my working day is counting properly however, I have to find out how to utilize that database date field instead of using an array in the report.

I am working on it and learning as days pass by.

Many thanks

Kind Regards

Jehanzeb

Edited by: Jehanzeb Navid on Jul 22, 2008 4:14 PM

Edit Edit - Edit Edit -

---

It isn't working I thought it was working but no it isn't.

18/07/2008 is friday and 21/07/2008 is Monday, so in theory orders placed on the 18th shipped on the 21 is 1 day and not 2 days.

however, if i run the report with

25/06/2008 Wednesday - 27/06/2008 Friday, it counts it as 3 days!

What the..... I cannot believe this, one thing start to get working other gets messed up!!

arrgghhh!!!

Edited by: Jehanzeb Navid on Jul 22, 2008 5:12 PM

Ok now it is taking weekends in account and also taking order day as 1st day.

I am not sure how to set up so that it looks for my holidays table instead of an array of holidays entered manually.

I'll keep trying.

[Former Member]

Jehanzeb,

I should have some time tonight. I'll see if I can play with it and make something work.

Jason

[Former Member]

Jehanzeb,

I got a good working model in SQL. Here is how I setup my calendar table:

Calendar Table Fields

Date          Thee actual date... every day of the year listed out.
DOW         Numeric representation of the day of the week Sun = 1, Mon = 2,...
WeekEnd  1 or null... If DOW = 1or 7 then 1 else 0 
Holiday      1 or null...Hand entered from a list of holidays
DaysOff     1 or null... combines all the 1's from WeekEnd & Holiday

Using the following SQL code:

DECLARE @BEG DateTime, @END DateTime;
SET @BEG = '2008-11-01'
;
SET @END = '2008-11-30'
;

SELECT 
COUNT(Date) - COUNT(DaysOff) AS DaysWorked,
COUNT(DaysOff) AS DaysOff
FROM dbo.Calendar
WHERE Date >= @BEG AND Date <= @END

I was was able to generate the following results:

DaysWorked = 18	DaysOff = 12

You should be able to workout from this and find a solution that works for you.

If you want I can send you the .mdf & .ldf files if you want to try out the table (It only has 2008 info in it).

Anyway, I hope this helps,

Jason

[Former Member]

Morning Jason,

Many thanks for the informative and a very good answer, however, I have tried the method which worked almost right.

Here is what I did, I created a formula which calculates the working days, then created another formula which holds an array of holiday dates.

Now the first formula works out the number of days which excludes Saturday and Sunday (as they are weekends and we don't work on weekends) and Bank holidays (these holiday dates I have inserted into an array).

The problem which Graham rightly pointed out is,

The formula is only calculating the difference in days between the order and dispatch date excluding weekends and holidays. You still need a formula that counts how many jobs took 22 days and how many took 4

Example:

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 the method it doesn't reflect the right results.

Here is the code I have used:

 
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

// This is another edition today but didn't work out properly. If end, that is if dispatch date increases the date limit given then show the orders within that date limit.

If End > {?End Date} 
  then
        End := {?End Date} 
Else
    End:= {order_header.act_despatch};


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;

A pointer to the right direction will be greatly helpful.

I really appreciate your help in this instance, however, I am sure you would not mind helping me further knowing I am totally new to this Crystal Reports business.

Many thanks for your kind help all this time.

Kind Regards

Jehanzeb

[Former Member]

Jehanzeb,

I've never used arrays in any of my reports so if that's a direction you've decided to go with, I'm not going to be of much help.

Even if I did know how to make it work, I think I'd still go with the table based approach. I say that because I don't ever want to build a report that will require that go back, periodically, and update hard coded dates in a formula.

With the table approach you can setup a simple application that will allow a user to input new holidays as they become known.

The example I sent in my previous post was actually very easy to do.

Jason

[Former Member]

Jason,

I do understand that the approach of array is not the best way specially when we are counting bank holidays from it.

I do have a table of Bank holidays, though that table does not have anything other than Dates.

I have also struck another problem where I think, the example which you provided above (few posts back) would do the trick however I don't know how to set it up.

I mean the guy has said create a function, which I did then after, he carried on with the code which I don't know where to put.

The array example, the problem I am having is that it is counting 5 days regardless of weekends (Saturday and Sunday in particular). I think I'll scrap this approach and will start again with the db approach however, I will def be needing someone's help in it as I have no idea how things are declared in Crystal (example I tried to use SQL command in Crystal which didn't work out properly.

Can you please help me getting the following code in place? I mean where to put an appropriate section of the code. bearing in mind I don't have to create a table with Holidays dates because it is already created.

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)

Many thanks for your unlimited help in this instance, it is surely priceless.

Many thanks

Kind regards

Jehanzeb

[Former Member]

Tried this in the formula but when it comes CASE it says boolean is required.

datediff('d',{order_header.date_entered},{order_header.act_despatch})-
datediff('wk',{order_header.date_entered},{order_header.act_despatch})*2
- CASE 
WHEN DATENAME('dw', {order_header.date_entered}) <> 'Saturday' 
AND DATENAME('dw', {order_header.act_despatch}) = 'Saturday' 
THEN 
1
WHEN DATENAME('dw', {order_header.date_entered}) = 'Saturday' 
AND DATENAME('dw', {order_header.act_despatch}) <> 'Saturday' 
THEN 
-1
ELSE 
0
END - 
(SELECT COUNT(*) FROM holidays 
WHERE bankholiday 
BETWEEN {?Start Date} AND {?End Date}
AND 
DATENAME('dw', bankholiday) <> 'Saturday' 
AND 
DATENAME('dw', bankholiday) <> 'Sunday')

RETURN (@days)
END

What am I doing wrong? The code is in the formula.

Regards

Jehanzeb

[Former Member]

Jehanzeb,

I don't know about that either. Here is a way that WILL work.

The only hitch is that it involve creating a new table, populating it and the updating it...

So you'll have to have an SA account or have an SA run parts of this for you.

1) Make a new Calendar table:

CREATE TABLE Calendar
(
Dates	DateTime	NOT NULL,
Holidays	BIT		NULL
PRIMARY KEY (Dates)
);

Next you'll need to populate the table:

-- This will populate the table with dates ranging from 1/1/2008 - 12/31/2010
-- You can change the dates if you want more or less dates...

DECLARE @DATES DateTime
DECLARE @BEG DateTime
DECLARE @END DateTime

-- Sets the 1st date in the table.
SET @BEG = '2008-01-01'

-- Sets the Last date in the Table.
SET @END = '2010-12-31'

-- Fills all the dates in between...
SET @DATES = @BEG
WHILE @DATES <= @END
BEGIN
INSERT INTO Calendar
(Dates)
SELECT @DATES AS Dates
SET @DATES = @DATES + 1
END

Now we need to populate the actual Holidays...

This code comes complete with all 2008, 2009 & 2010 official US Bank Holidays:

UPDATE Calendar
SET Holidays = 1
WHERE Dates IN (
'2008-01-01','2009-01-01','2010-01-01',
'2008-01-21','2009-01-19','2010-01-18',
'2008-02-18','2009-02-16','2010-02-15',
'2008-05-26','2009-05-25','2010-05-31',
'2008-07-04','2009-07-04','2010-07-05',
'2008-09-01','2009-09-07','2010-09-06',
'2008-10-13','2009-10-12','2010-10-11',
'2008-11-11','2009-11-11','2010-11-11',
'2008-11-27','2009-11-26','2010-11-25',
'2008-12-25','2009-12-25','2010-12-25'
)

Since the table has only two columns (Dates & Holidays) and it would be nice to be able to factor in weekends...

We'll do this with a view:

CREATE VIEW vCalendar_DatesOff AS
SELECT
Dates,
CASE WHEN DatePart(dw,Dates) IN (1,7) THEN CAST(1 AS BIT) END AS Weekends,
Holidays,
CASE WHEN DatePart(dw,Dates) IN (1,7) OR Holidays = 1 THEN CAST(1 AS BIT) END AS DaysOff
FROM Calendar

Now you have 4 columns to work with: Dates, Weekends, Holidays & DaysOff.

So to make it all useful in a report... A little SQL that will fit nicely in a Command:

DECLARE @BEG DateTime, @END DateTime;
SET @BEG = '2008-11-01'
;
SET @END = '2008-11-30'
;
SELECT
COUNT(Dates) AS TotalDays,
COUNT(DaysOff) AS DaysOff,
COUNT(DATES) - COUNT(DaysOff) AS DaysWorked
FROM vCalendar_DatesOff
WHERE Dates BETWEEN @BEG AND @END

The results of this SQL:

TotalDays = 30    DaysOff = 12     DaysWorked = 18

Man... can't wait till November...

Hope this jets the job done for you. I've tested it. It works. Enjoy!!!

Jason

[Former Member]

Jehanzeb,

I played around with this idea a little over the weekend. I put a sample report in the Diamond Community thing.

If you want to take a look, here is the link: [https://boc.sdn.sap.com/node/19945]

Hope you like it,

Jason

[Former Member]

Morning Jason,

I can't see anything on the page. It says

"User account

3"

There is nothing on the page though.

Anyhow I have been playing around with the coding as well, and I managed to make it work however, on my crosstab it shows 0 on top of the cross tab with the percentage of 2.34%. This is well weird to be honest as I am not sure why it is showing that.

I have posted a new thread with Crosstab showing 0 - 2.34%, to see if someone else has the same issue with crosstabs.

Crosstabs instead of helping are making life misserable, I mean what else can I use to get things going. I need to link up a sub report with the Number of days, which I think I cannot achieve if using crosstabs.

Oh well my search carries on!

Regards

Jehanzeb

Edited by: Jehanzeb Navid on Jul 29, 2008 9:34 AM

I think I know the problem however, I don't know how to correct this.

When an order is placed obvious reasons the Dispatch date will be set to null as we don't know when it will be shipped.

I think there is a option in the report section where it says Convert null values to 0. Something like this I read it somewhere and I am wondering if that option is enabled.

Do you know where I can find that option?

[Former Member]

Jehanzeb,

I sent a copy of the .zip folder with the necessary files to the address on you business card.

[Former Member]

Thanks Jason,

I received it. I will look into it and will post any updates here.

Many thanks

Kind Regards

Jehanzeb

[Former Member]

Hello Jason,

I have started a new report with Customer Name as a group and used Working days as details along with Number of jobs and percentages.

Now the problem is that even though I have set up a parameter for customer account, the report is not being filtered according to the data I am inputting.

For example:

*Report Header:*
----------------------
Date: 01/06/2008 to 30/06/2008
Customer Account: 0010065

*Group Header*
---------------------
Date Range - Customer account number and Customer Name (Suppress if not hasvalue(customer account number)

*Group Detail*
------------------
Number of Days formula - Total Count of Orders(when status=77) - Percentage - Details (Details is a sub report link)

*Group Footer*
-------------------
n/a

*Report Footer*
--------------------
n/a

*Page footer*
-----------------
Page Number

The report when run shows all the customer names according to group. How can I make the group work with date and customer account range? So that it only shows the restrictive details.

Currently the report produces more than 30,000 records.

Regards

Jehanzeb

Edited by: Jehanzeb Navid on Jul 29, 2008 4:52 PM

Found it

It was because I didn't link the customer account with customer name table fields.

It is filtering now however the results are weird.

Like

Number of days -  Number of jobs
3                               0
2                               0
                                 0

It is in detail section. How can I use the same formula and put it in the report as Count(NumofDays). That is exactly what I use within Crosstab and it gives me Number of jobs.

[Former Member]

Jehanzeb,

I'm not sure where you are running into trouble. From what I'm seeing it looks like you be trying mix

aggregated and non-aggregated fields in the details section. For example you have the number of days formula and

COUNT(Orders) both in the same section.

You will want to make sure you are bringing in the number of days for each specific order. Once you feel comfortable

that you are betting the correct number of days for each individual order, you can move on to summarizing the data.

For example:

COUNT(Orders) Average(NumOfDays)

If I've got the problem wrong let me know. If necessary, you can a export a portion of your raw data to MS Access or Excel

and send it to me along with a copy of your report.

Jason

[Former Member]

Jehanzeb,

If you are still having issues, start a new thread.

Jason

[Former Member]

Jason I am going to do it now. I cannot understand this parameter filteration.

Will explain in the new thread with links to db, report layout etc.