Remove weekends from a datediff calculation

[djieon]

Hi All,

I have tried to do this using crystal reports syntax but have failed so I am hoping there is some way I can do it using SQL...

I have this formula:-

datediff ("n",{RM_JOB.REQUIRED_START_DATE},{RM_JOB.ACTUAL_COMPLETION_DATE})

However I need to modify this to only include weekdays, is this possible?

Then once that is done I have a table called RM_CALENDAR_DAY which has a field in it call CALENDAR_DAY which is a date field and contains single dates for things like christmas day, boxing day etc...I also want these excluded from the datesdiff above...is that possible..

Thanks in advance

David.

 

[simian336]

You might consider a calender table.

http://sqlserver2000.databases.aspf...nsider-using-an-auxiliary-calendar-table.html

Simi

 

[djieon]

Unfortunatly I dont have access to creating tables in the server, so im hoping I can do something with a sql expression...

 

[gmmastros]

Take a look at this thread, especially the one from vongrunt.

http://tek-tips.com/viewthread.cfm?qid=1416255

This won't take your holidays in to account, but it is a good start.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djieon]

@gmmastros

i have looked at it but it does not mean a lot to me! how would i incorporate that into my fields?

Like i say, a real newbie to sql...

 

[simian336]

include your whold sql statement and let us take a look

simi

 

[markros]

There was a relatively recent thread in MSDN SSRS forum showing how to add a function in report to do such calculations. I assume you can implement something similar in Crystal Report.

Would you be able to find this thread?

PluralSight Learning Library

 

[djieon]

ok here is my current code...

//Main formula
WhileprintingRecords;
Local DateVar Start := (date ({RM_JOB.REQUIRED_START_DATE})); // place your Starting Date here
Local DateVar End := (date ({RM_JOB.ACTUAL_COMPLETION_DATE})); // place your Ending Date here
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);

Local NumberVar i;
For i := 1 to Count (Holidays)
do (if DayOfWeek ( Holidays ) in 2 to 6 and
Holidays in start to end then Hol:=Hol+1 );

weeks;

but I want the holidays part of this to look at RM_CALENDAR_DAY and look at a field called CALENDAR_DAY and exclude the dates it finds in there...

also the weekends still appear to be appearing in the code above so its not doing what I need it to do!

Thanks in advance.

David.