Weekday query

[tezread]

Hi thereI have a query of patient episodes that works out the difference between two dates 'RequestDateTime' and 'Procedure1DateTime'.The query takes out the weekend days so only workdaysSometimes though a patient episode may be put on hold using another two fieldsHoldStartDateTimeHoldEndDateTimeI was trying to modify my curent query so that the NumberofWorkdays checked if the episode was put on hold and if so, subtracted that from the value.In lamens terms Numberofworkdays = [Procedure1DateTime-RequestDatetime...... and the query below] - [HoldEndDateTime-HoldStartDateTime]here is current query:NumberOfWorkDays: Int([Procedure1Datetime]-[RequestDatetime]-(DateDiff("ww",[RequestDatetime],[Procedure1Datetime],7)-(Weekday([RequestDatetime])=7))-(DateDiff("ww",[RequestDatetime],[Procedure1Datetime],1)-(Weekday([RequestDatetime])=1))-(Select Count(*) from [tblHolidays] where [HolidayDate] between [RequestDatetime] and [Procedure1Datetime]))+1Many thanks for any support offered

 

[DCrake]

Right to extract a pause in a patient episod you still need to do the same calculation as if there was no pause. Then repeat the same calculation on your pause and restart date to get the pause duration. Then subtract the pause from the episode start and end dates.

Note you need to replace the pause start and end dates with Date() for null values to ensure that you have a valid date range

 

[tezread]

thank you DavidI have tried this but it is too long an expression:WorkDays: Int([Procedure1Datetime]-[RequestDatetime]-(DateDiff("ww",[RequestDatetime],[Procedure1Datetime],7)-(Weekday([RequestDatetime])=7))-(DateDiff("ww",[RequestDatetime],[Procedure1Datetime],1)-(Weekday([RequestDatetime])=1))- Date(([HoldEndDateTime1]-[HoldStartDateTime1]))-(Select Count(*) from [tblHolidays] where [HolidayDate] between [RequestDatetime] and [Procedure1Datetime]))+1

 

[DCrake]

Put them in 2 seperate columns and then take one column from the other

 

[Pelerin13]

Hey Tesread, i have a Funtion called WorkingDays that you can give it 2 dates and will give you the working days excluding the weekends and holidays
let me know if you are looking for something like that

 

[tezread]

Hey Tesread, i have a Funtion called WorkingDays that you can give it 2 dates and will give you the working days excluding the weekends and holidays
let me know if you are looking for something like that

Hi thereI have a working function for Workingdays but esstentially what I am trying toa dd is a function that takes into account a pause between start and end date

 

[DCrake]

Are you only dealing with one pause in the episode or can there be more than one.