Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Calcultae workings days, excluding holiday dates Post Reply Post New Topic
Author Message
martinjamesward
Newbie
Newbie


Joined: 22 Jun 2009
Location: United Kingdom
Online Status: Offline
Posts: 11
Quote martinjamesward Replybullet Topic: Calcultae workings days, excluding holiday dates
    Posted: 24 Jun 2009 at 6:33am
I have a formula that calculates working days between two dates and excludes holidays.  My problem is two fold..
 
  1. It contains a list of bank holidays since 2002 and the foumula is becoming massive
  2. I have lots of reports with the same fomula in them - maintaining them is a nightmare

I would like to create a database table of holiday dates, which I can then read into my formula so I only need to maintain the data in a single location and the formula becomes more managable

Does anyone have any idea on how I can acheive this? Confused
 
FYI I am using Crystal 10 & my formula is listed below (with the list of holidays cut down)...
 
 

// Start Date Time and Date

Local DateTimeVar dt1:= {@DateFSComplete}; //Date Time

Local DateTimeVar d1:= CDate({@DateFSComplete}); //Date

 

// End Date Time & Date

Local DateTimeVar dt2:= {@DateInvoiced}; // Date Time

Local DateTimeVar d2:= CDate({@DateInvoiced}); //Date

 

//Convert Days to Seconds

Local NumberVar ds:= (Date(dt2) - Date(dt1))*86400;

 

//Convert Hours to Seconds

Local NumberVar hs:= (Hour(dt2) - Hour(dt1))*3600;

 

//Convert Minuites to Seconds

Local NumberVar ms:= (minute(dt2) - Minute(dt1))*60;

 

//Seconds

Local NumberVar ss:= Second(dt2) - Second(dt1);

 

//Total Seconds

Local NumberVar ts:= ds+hs+ms+ss;

 

//Extract total days from total  seconds

ds:= Truncate(ts/86400);

 

//Extract total hours from total seconds

hs:= Truncate((Remainder(ts,86400))/3600);

 

//Extract total minuites from total seconds

ms:= Truncate((Remainder(ts,3600))/60);

 

//Extract remaining seconds

ss:= Truncate(Remainder(ts,60));

 

//Remove Weekends

Local NumberVar Sat:= 0;

Local NumberVar Sun:= 0;

Local NumberVar WD:= 0;

Sat := DateDiff ("ww", d1, d2, crSaturday) ;

Sun := DateDiff ("ww", d1, d2, crSunday);

WD := Sat + Sun;

 

// *** Remove Bank Holidays ***

 

// Set Bank Holidays to 0

Local Numbervar bankhols := 0;

 

//BANK HOLIDAYS HERE

 

//Loads of bank holidays removed

 

//2008 BANK HOLIDAYS

 

//New Years Day (01/01/2008)

if date(2008,01,01) in d1 to d2 then  bankhols := bankhols + 1;

//Good Friday (21/04/2008)

if date(2008,03,21) in d1 to d2 then  bankhols := bankhols + 1;

//Easter Monday (24/04/2008)

if date(2008,03,24) in d1 to d2 then  bankhols := bankhols + 1;

//May Bank Holiday (05/05/2008)

if date(2008,05,05) in d1 to d2 then  bankhols := bankhols + 1;

//Spring Bank Holiday (26/05/2008)

if date(2008,05,26) in d1 to d2 then  bankhols := bankhols + 1;

//August Bank Holiday (25/08/2008)

if date(2008,08,25) in d1 to d2 then  bankhols := bankhols + 1;

//Christmas Day (25/12/2008)

if date(2008,12,25) in d1 to d2 then  bankhols := bankhols + 1;

//Boxing Day (26/12/2008)

if date(2008,12,26) in d1 to d2 then  bankhols := bankhols + 1;

 

//Calculate days (days - holidays - Weekends) - Note Global variable used here to alllow other formulas to perform calcaulation on figure in FD (cont.)

//If more than one of these formulas, give each final days values a unique name

Global NumberVar FD;

FD:= (DS - bankhols - WD);

 

StringVar Display:= ToText(FD,0,"") + ":" + ToText(hs,0,"") + ":" + ToText(ms,0,"") + ":" + ToText(ss,0,"");

 

Display

IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 24 Jun 2009 at 6:49am
Do you use stored procs to get the data?  If so, creating a table would seem to be an option, you could mark the number of bankholidays on the SQL side.  Why I mention this is that I cannot think of how you check the data from a table in Crystal.
 
My first thought besides Stored Procs (because you localize it there and use a function or another stored proc to get the bank holidays) was a function, but it would probably need to be in the Repository, but I have never used this as I can't seem to figure out how to get access to it. 
 
Last idea, create a subreport that instead of fetching any data, just gets passed the dates and returns the result of the formula, then when ever you need to the calculation, you insert the subreport.  This gives you central location to maintain the formula, and all calculations are the same.  I can endorse this solution as it doesn't hit the database again (why I tend to dislike subreports)...so the dates are the values that 'link' the subreport to the main report, and either the subreport can display the value and/or return it as a shared variable.  If returned as a shared variable, you could access it in the existing formulas and the rest of the report should work as before.

HTH
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.