get working days between two dates

Question

get working days between two dates

Nomvula

Hall of Fame

Points: 3778
More actions
May 4, 2009 at 3:42 am

#220819

hi experts
i have a built in calendar view which calculates the holidays and weekends and field IsWorkingDays which stores 1 if it's working day and 0 if it's holidays/weekends.
and now i want to calculate the number of working days between two dates. here's the syntax i wrote but it's working:
f {vw_astCalendarDeviations.IsWorkingDay} = 1 and date(CurrentDate) to date({vw_wrkWorkOrders.RequiredByDate}) then 1 else 0
it's in Crystal reports

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

Since you already have a working calendar table, all you need is a COUNT query to get the no. of working days between two dates. For e.g.

DECLARE @Date1 DATETIME, @Date2 DATETIME

SELECT @Date1 = '01-May-09', @Date2 = '31-May-09'

SELECT COUNT(*) FROM dbo.Calendar

WHERE CalendarDate >= @Date1 AND CalendarDate <= @Date2 AND IsWorkingDay = 1

--Ramesh

Nomvula Hall of Fame Points: 3778 More actions · Answer 2

thanks for quick response;

i was looking at something like this:

DateTimeVar StartDate:= CurrentDate;

DateTimeVar EndDate := {vw_wrkWorkOrders.RequiredByDate};

//Find out the difference in days and subtract the weekends

NumberVar DaysDiff := DateDiff("d",StartDate,EndDate) - DateDiff("ww",StartDate,EndDate,crsaturday) - DateDiff("ww",StartDate,EndDate,crsunday);

if Not({vw_astCalendarDeviations.IsWorkingDay}) in [1] and {vw_astCalendarDeviations.IsWorkingDay} in StartDate to EndDate then DaysDiff := DaysDiff - 1;

DaysDiff;

but i'm getting a boolean error in the If Not line

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 3

Firstly, you should have posted this in the correct forum. Since you posted in SQL forum, you get responses related SQL.

Secondly, I am not crystal report designer but you probably need a looping constraint such as FOR, WHILE or DO..WHILE..

--Ramesh