WHEN (MinutesEnd - MinutesStart - Overtime) > 0 THEN (MinutesEnd - MinutesStart - Overtime) SELECT ID, DateStart, DateEnd, CONVERT(VARCHAR, Minutes/60) +':'+ CONVERT(VARCHAR, Minutes % 60) AS ReactionTime SELECT = TABLE (ID INT, DateStart DATETIME, DateEnd DATETIME) The function assumes the start of the next available work day (08:30 h) when the is off-hours, and the end of the previous available work day (16:00 h) when is off-hours. No special handling/support for overtime. The function result is the time difference in business hours between two dates.
So for example a timespan of 8:00 hours would be " 08:00:00" and 25 hours would be " 01:00:00". The function returns a DATETIME value meant as an offset from date 0 (which is " 00:00:00"). SET = * - DATEDIFF(mi, DATEDIFF(mi, - return duration as an offset in minutes from date 0 calculate effective duration in minutes
#How to use @workday in caseware idea full#
SET = DATEADD(dd, 0, DATEDIFF(dd, 0, = CASE WHEN DATEDIFF(mi, DATEADD(mi, count full work days (subtract Saturdays and Sundays) SET = DATEADD(dd, 0, DATEDIFF(dd, 0, = CASE DATEPART(dw, THEN + 2 SET = DATEADD(dd, 0, DATEDIFF(dd, 0, = CASE WHEN DATEDIFF(mi, > + 1ĮLSE if Saturday or Sunday, make it next Monday SET = assume next day if start was after end of workday calculate Saturday and Sunday dependent on SET DATEFIRST option If I have time today, I'll look into writing an example function.ĮDIT: Here is something with bells and whistles, calculating around weekends transparently: ALTER FUNCTION dbo.udfDateDiffBusinessHours DATETIME Further refinements are possible without the need to change hard to read nested SELECT/CASE WHEN constructs, which would be the alternative. Extending it to include support for holidays (I'm thinking of a Holidays table here) would not be too hard. In a function you can write way more expressive SQL than in a query, and you don't clog your query with business rules, making it hard to maintain.Īlso a function can easily be reused. I'm not sure where your Overdue value comes from, so I left it off in my example. SELECTĭbo.udfDateDiffBusinessHours(MIN(Date), MAX(Date)) ReactionTime Hundreds of companies, ranging from medium-sized businesses to Fortune 50 enterprises, have selected Workday.I would recommend building a user defined function that calculates the date difference in business hours according to your rules. Founded in 2005, Workday delivers human capital management, financial management, and analytics applications designed for the world's largest organizations. Workday is a leading provider of enterprise cloud applications for human resources and finance.
#How to use @workday in caseware idea license#
See the Application License Agreement for details. To use the Workday app, your company must be a Workday customer and you must be an authorized user for the app. Not all Workday features are available on mobile. You’ll have access to mobile features your organization has enabled, based on your role. Not all mobile features may be available to you. Note: Your organization must authorize access to the Workday mobile app. With support for fingerprint scanning, only you can access your information.
0 kommentar(er)
