InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 24 May 2012 10:59:15 GMT20http://www.sqlservercentral.com/Forums/Topic426732-1138-1.aspxhi i am new to this forummy problem is i have table to define the working hours of the employees based on that i need to calculate the hoursthere is no such rule of common shift timeing &gt;&gt;Problem statementsHave Creation date of ticketEmployee work in diffrent shift based on the World country they support so weekoff and shift time are in one table and list of holidays in another tableso now i need to calculate the time spent as of now... received time and current time - i should get the business hoursTable 1 : Shift start Time | Shift End time | Weekoff1 | weekoff2 |Table 2 : HolidaysResult should be business hoursTue, 27 Mar 2012 01:24:58 GMTLearning userhttp://www.sqlservercentral.com/Forums/Topic426732-1138-1.aspxVery nice job with the function that calculates time between two dates, I am working in a report that I need to calculate the number of working days less holidays and I was wondering if those functions can be modified to be used to calculate working days excluding holidays, I do not need to remove lunch hour. I am new in this, so I will appreciate a little guidance.Fri, 18 Mar 2011 12:29:23 GMTsunnyplacehttp://www.sqlservercentral.com/Forums/Topic426732-1138-1.aspxHello, it's a great tool for calculate SLA'sbut I'm trying to use this for calculate saturday morning include (here (Brazil) we work in saturdays too)can you help me to do this modification?thank youMon, 03 Jan 2011 12:32:20 GMTc1680222http://www.sqlservercentral.com/Forums/Topic426732-1138-1.aspxHello - I have the below function ([b]source[/b]: http://ask.sqlteam.com/questions/1105/regarding-sql-query-further-queries) that basically calculates the business hours/minutes elapsed between two **[b]smalldatetime[/b]** fields:set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER FUNCTION [dbo].[getBusinessHours] (@smalldatetime1 smalldatetime, @smalldatetime2 smalldatetime)RETURNS bigint ASBEGINDECLARE @Diff bigint; DECLARE @adjusted_1 smalldatetime; DECLARE @adjusted_2 smalldatetime;SET @adjusted_1 = case when @smalldatetime1 - dateadd(day, datediff(day, 0, @smalldatetime1), 0) &lt; '18:00' then @smalldatetime1 else dateadd(day, datediff(day, 0, @smalldatetime1), 0) + case when datename(weekday,@smalldatetime1) in ('saturday','sunday') then '17:30' else '18:00' end end;SET @adjusted_2 = case when @smalldatetime2 - dateadd(day, datediff(day, 0, @smalldatetime2), 0) &gt; '08:30' then @smalldatetime2 else dateadd(day, datediff(day, 0, @smalldatetime2), 0) + case when datename(weekday,@smalldatetime1) in ('saturday','sunday') then '09:30' else '08:30' end end;SET @Diff = case when datename(weekday,@smalldatetime1) in ('saturday','sunday') then datediff(minute, @adjusted_1, @adjusted_2) - (datediff(day, @adjusted_1, @adjusted_2) * 960) else datediff(minute, @adjusted_1, @adjusted_2) - (datediff(day, @adjusted_1, @adjusted_2) * 870) end;return @DiffENDThe problem I am facing is that the function fails to calculate the business hours correctly if the "**smalldatetime1**" falls between **[b]12:00 AM[/b]** and **[b]8:30 AM[/b]**.Also FYI - - [b]smalldatetime1[/b] is the date/time when a call is logged into the database. - [b]smalldatetime2[/b] is the date/time when the call was closed.Now basically I would like to track the calls that took more than 24 hrs / 4 hrs to close, only considering business hours, which is: - [b]8:30 AM - 6:00 PM on Weekdays[/b] - [b]9:30 AM - 5:30 PM on Weekends[/b]Any help would be much appreciated.Thanks!Wed, 30 Jun 2010 19:44:41 GMTQuestionBoyhttp://www.sqlservercentral.com/Forums/Topic426732-1138-1.aspx[quote][b]RTaylor2208 (5/6/2010)[/b][hr]Thanks I managed to get it all working successfully.Also I modified the functions so I could provide 2 dates so I can calculate the time passed between them instead of using getdate(). Nice work.[/quote]Cool... two way street here, though. Please post your solution and functions. Thanks.:-)Thu, 06 May 2010 06:36:41 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic426732-1138-1.aspxThanks I managed to get it all working successfully.Also I modified the functions so I could provide 2 dates so I can calculate the time passed between them instead of using getdate(). Nice work.Thu, 06 May 2010 03:23:05 GMTRTaylor2208http://www.sqlservercentral.com/Forums/Topic426732-1138-1.aspxThank you very much RTaylor.For your requirement, simply you do not need to use two of the functionsDateAt1130DateAt12Also modifythe below three functions to remove the reference of the above two functionsCalcCreateDateCalcGetDateCalcTimeBetweenTwoDatesAdditionally, modify the functions DateAt730 to DateAt8and DateAt16 to DateAt18to suit your timing8am till 6PMWed, 05 May 2010 17:10:22 GMTMazharuddin Ehsanhttp://www.sqlservercentral.com/Forums/Topic426732-1138-1.aspxHi, could you give me a little guidance on removing the lunch break as we have a rolling support team who work from 8am till 6PM.I have managed to update the start and end working hours, but do not want to deduct a half hour lunch break.Any help would be appreciated as you solution works very well.Thanks in advance.Wed, 05 May 2010 03:57:57 GMTRTaylor2208http://www.sqlservercentral.com/Forums/Topic426732-1138-1.aspxHey Guys,Thanks for the really great functions, but I'm having some wierd issue here. I know this is a old thread, sorry.We have a shift cycle from 7am - 10 pm here with a 1 hour lunch. I have altered the lunch calc to 3600 which works fine and changed the dateFN's to hours that we use. So basically there are 14 working hours in days (long days).The problem is that when ever I try calculate hours over a date period greater than 2 days, I loose 6 hours per day?I am more than confused...select dbo.CalcTimeBetweenTwoDates('2009-11-16 06:00', '2009-11-16 22:30')14 --(as expected)select dbo.CalcTimeBetweenTwoDates('2009-11-16 06:00', '2009-11-17 22:30')28 --(as expected)select dbo.CalcTimeBetweenTwoDates('2009-11-16 06:00', '2009-11-18 22:30')36 -- (6 hours short????)select dbo.CalcTimeBetweenTwoDates('2009-11-16 06:00', '2009-11-19 22:30')44 --(12 hours short now???)Mon, 30 Nov 2009 09:03:47 GMTgeoff.waltonhttp://www.sqlservercentral.com/Forums/Topic426732-1138-1.aspx[quote][b]Jeff Moden (9/24/2009)[/b][hr]I get it... [b]you're not running the same code as in the article[/b]. Please post the code you are running. Thanks.[/quote]You are right Jeff. The code in the article [url=http://www.sqlservercentral.com/scripts/Scalar-valued+Functions/61567/][b]Calculating the Number of Business Hours Passed since a Point of Time[/b][/url] does not contain the code for the function [b]CalcTimeBetweenTwoDates[/b].I posted it later in the discussion forum of the article as a reader requested for it. Please find attached the code for the function CalcTimeBetweenTwoDates in the [url=http://www.sqlservercentral.com/Forums/Topic426732-1138-1.aspx][b]discussion forum of the article[/b][/url][quote][b]Mazharuddin Ehsan (11/28/2007)[/b][hr]Comments posted to this topic are about the item [B][url=http://www.sqlservercentral.com/scripts/Scalar-valued+Functions/61567/]Calculating The Number Of Business Hours Passed Since a Point of Time [/url][/B]The comments of some of the readers motivated me to do enhancements in the solution:1. To calculate duration between two values of time.2. Considering the holidays.See the attachment for the details.Edited: 1/13/2008 5:43 PM by Mazharuddin Ehsan[url=http://www.sqlservercentral.com/Forums/Attachment281.aspx][b]CalculatingDurationOfOfficeHoursPassedBetweenTwoPointsOfTime.doc[/b][/url][/quote]-MazharuddinFri, 25 Sep 2009 03:50:51 GMTMazharuddin Ehsanhttp://www.sqlservercentral.com/Forums/Topic426732-1138-1.aspxI get it... you're not running the same code as in the article. Please post the code you are running. Thanks.Thu, 24 Sep 2009 17:56:09 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic426732-1138-1.aspxI ran your code from the previous post... here it is, again...[code="sql"]select getdate()select dbo.CalcTimeBetweenTwoDates('2004-11-16 15:30', '2007-11-21 07:31')select getdate()[/code]Here's what I get for an error message from that code...[code="sql"]Msg 4121, Level 16, State 1, Line 1Cannot find either column "dbo" or the user-defined function or aggregate "dbo.CalcTimeBetweenTwoDates", or the name is ambiguous.[/code]Looking at the code from your good article, I see no function called dbo.CalcTimeBetweenTwoDates. I'm still looking, though.Thu, 24 Sep 2009 17:51:12 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic426732-1138-1.aspxThanks for the feedback, Mazharuddin... I'll take a look.Thu, 24 Sep 2009 17:41:08 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic426732-1138-1.aspx[quote][b]Jeff Moden (7/26/2009)[/b][hr]If I may suggest...1. We don't need RBAR to do this... no WHILE loops please. :sick:2. We don't need 8 functions to do this... that's a lot of complicated code to maintain not to mention the overhead of calling 8 functions.3. We certainly don't need any hardcoding of times for this. Even the DatePart(DW) shouldn't be hardcoded because of the possible settings of DATEFIRST4. The absolute best way to do this is to have a Calendar table. Yeah, I know... for some reason, lots and lots of folks would rather use some very complicated code and While Loops to do this instead of the very simple method of using a Calendar table.With all of that in mind, might I suggest the following instead?[/quote]Hi Jeff,The alternative you provided looked smart in the initial glance.I tried to use it and below are my observations:Using my function CalcTimeBetweenTwoDates:[code="sql"]select getdate()select dbo.CalcTimeBetweenTwoDates('2004-11-16 15:30', '2007-11-21 07:31')select getdate()Result:2009-09-24 17:05:58.2806280.522009-09-24 17:05:58.543[/code]Time taken = 263 msThe below is how it works using the alternative you provided:(Note: I had to increase the number of rowes in the Taly table (from the article [url=http://www.sqlservercentral.com/articles/T-SQL/62867/][b]"The "Numbers" or "Tally" Table: What it is and how it replaces a loop"[/b][/url]) from 11000 to 110000 to get it working)[code]2009-09-24 17:21:06.2806280.5000002009-09-24 17:21:06.687[/code]Time taken = 407 msAs you see, the time taken is more than the loop method and the result is also not accurate (It should be 6280.52)I know, to get the accurate result, I need to increase the number of rows in the tally table. But the question is how much and why should I do it, when I am doing it better using the loop. There can be reservations by the db admin to invest so much space in a Tallly table just to do calculations.1. I agree that the aternative you provided uses less amount of TSQL code, but on the other hand it requires an ever demanding tally tabl.2. The one thing impressive and useful that you are providing is dynamic work timings. This is worth emulating.Thanks,Thu, 24 Sep 2009 09:07:34 GMTMazharuddin Ehsanhttp://www.sqlservercentral.com/Forums/Topic426732-1138-1.aspxWhat is with all the smaller functions:dbo.DateAtMidnightdbo.DateAt730dbo.DateAt1130 etc etc....Mon, 21 Sep 2009 09:57:53 GMTdmaddi 20731http://www.sqlservercentral.com/Forums/Topic426732-1138-1.aspxIf I may suggest...1. We don't need RBAR to do this... no WHILE loops please. :sick:2. We don't need 8 functions to do this... that's a lot of complicated code to maintain not to mention the overhead of calling 8 functions.3. We certainly don't need any hardcoding of times for this. Even the DatePart(DW) shouldn't be hardcoded because of the possible settings of DATEFIRST4. The absolute best way to do this is to have a Calendar table. Yeah, I know... for some reason, lots and lots of folks would rather use some very complicated code and While Loops to do this instead of the very simple method of using a Calendar table.With all of that in mind, might I suggest the following instead?[code="sql"]--===== Declare some obviously named variablesDECLARE @StartDateTime DATETIME, @EndDateTime DATETIME, @WorkTimeStart1 DATETIME, @WorkTimeEnd1 DATETIME, @WorkTimeStart2 DATETIME, @WorkTimeEnd2 DATETIME, @BinSize INT, @Saturday INT, --Datepart(dw) for Saturday regardless of DATEFIRST @Sunday INT --Datepart(dw) for Sunday regardless of DATEFIRST;--===== Preset the variables SELECT @StartDateTime = '2007-11-16 15:30', --Likely parameter in function @EndDateTime = '2007-11-20 14:00', --Could be parameter in function @WorkTimeStart1 = '07:30', --Could be parameter in function @WorkTimeEnd1 = '11:30', --Could be parameter in function @WorkTimeStart2 = '12:00', --Could be parameter in function @WorkTimeEnd2 = '16:00', --Could be parameter in function @BinSize = 15, --Minutes, Could be parameter in function @Saturday = DATEPART(dw,5), --First Saturday of 1900 @Sunday = DATEPART(dw,6) --First Sunday of 1900;--===== Using the start and end time, calculate the number of business hours -- between those two date/times.WITH cteTimeSlots AS(--==== Produces a list of datetime slots in @BinSize minute intervals SELECT DATEADD(mi,(t.n-1)*@BinSize,@StartDateTime) AS TimeSlot FROM dbo.Tally t WHERE t.N <= DATEDIFF(mi,@StartDateTime,@EndDateTime)/@BinSize),cteDates AS(--==== Separates the Time as a separate column and removes weekends SELECT ts.TimeSlot, Date = DATEADD(dd,DATEDIFF(dd,0,ts.TimeSlot),0), Time = ts.TimeSlot - DATEADD(dd,DATEDIFF(dd,0,ts.TimeSlot),0) FROM cteTimeSlots ts WHERE DATEPART(dw,ts.TimeSlot) NOT IN (@Saturday,@Sunday))--===== Counts time slots within the workday and converts to -- decimal hours. To exclude dates from a Holiday table, -- uncomment the last line and modify as necessary. SELECT COUNT(*)*@BinSize/60.0 FROM cteDates d WHERE ( (d.Time >= @WorkTimeStart1 AND d.Time < @WorkTimeEnd1) OR (d.Time >= @WorkTimeStart2 AND d.Time < @WorkTimeEnd2) )-- AND NOT EXISTS (SELECT 1 FROM dbo.Holiday h WHERE d.Date = h.Date)[/code]As usual, details of how it works are in the comments. If you don't know what a Tally table is or how it works, please see the following article...[url=http://www.sqlservercentral.com/articles/T-SQL/62867/][font="Arial Black"][size="2"]http://www.sqlservercentral.com/articles/T-SQL/62867/[/size][/font][/url]Sun, 26 Jul 2009 13:14:27 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic426732-1138-1.aspxIn our work order system we have service levels that we must meet. One of the service levels is how long a call is open before it is assigned to a technician. We use the business hours function to give an accurate report on the calls that are currently opened and have not been assigned in the allotted time frame and we also use it to look at closed calls and see over a certain time frame the amount of calls that were assignedot assigned on time.Tue, 11 Mar 2008 10:34:18 GMTPeteO-430407http://www.sqlservercentral.com/Forums/Topic426732-1138-1.aspxThanks for the information. It would be nice if you share more details like you used it in what scenario.Tue, 11 Mar 2008 10:13:13 GMTMazharuddin Ehsanhttp://www.sqlservercentral.com/Forums/Topic426732-1138-1.aspxJust wanted to let you know we did implement this into our work order time tracking reports. It works GREAT!!!Fri, 07 Mar 2008 12:12:12 GMTPeteO-430407http://www.sqlservercentral.com/Forums/Topic426732-1138-1.aspxHi Pete,I am pleased to know that it is working for you. Testing and debugging a piece of programming script to make it work exactly as you want is different than 'proofreading'. It involves understanding the business logic and much more other than knowing the syntax of the language. So be careful and take care :)Sincerely,MazThu, 31 Jan 2008 10:50:14 GMTMazharuddin Ehsanhttp://www.sqlservercentral.com/Forums/Topic426732-1138-1.aspxThis seems to work GREAT!!! Thanks so much for your help. It really helps to have someone else to look at your code, so thanks for doing my proofreading for mr!!Tue, 29 Jan 2008 11:50:47 GMTPeteO-430407http://www.sqlservercentral.com/Forums/Topic426732-1138-1.aspxCaught the bug, You did a visibly small mistake with the [b]begins and ends[/b] while modifying the code which botched up the whole thing.Every “[b]begin[/b]” should have its “[b]end[/b]” in the proper place. This is a simple rule with each and everything in this universe I believe.I tested and found that is the only snag. Correct this and this will work. I have modified and writen my comments in the attachment.Check these two lines in the attachment.[code] --end (You have put this “end” wrongly here. And you are missing one ‘END’ which corresponds to the ‘BEGIN’ above which should be just before the line ‘return @CalcHours’)[/code][code]END – You were missing this END here[/code]Also I have a piece of advice. When you are doing programming (even if the original writer is someone else and you are modifying it) you must pay your fullest attention. No doubt it is time consuming but this is all about programming sadly.I believe you are smart enough ;), possibly you just did not pay attention just because you are the boss:w00t: there. Have a nice time.Sincerely,MazThu, 24 Jan 2008 12:14:20 GMTMazharuddin Ehsanhttp://www.sqlservercentral.com/Forums/Topic426732-1138-1.aspxThat is cool! I am looking at where I modified the code and that is where SQL is throwing the error. Just not smart enough to determine exactly what is the problem:PTue, 22 Jan 2008 13:01:54 GMTPeteO-430407http://www.sqlservercentral.com/Forums/Topic426732-1138-1.aspxOh another bug! Let me catch it this weekend!:)Tue, 22 Jan 2008 11:30:12 GMTMazharuddin Ehsanhttp://www.sqlservercentral.com/Forums/Topic426732-1138-1.aspxHmmmm, I must be doing something wrong then, as the code you have described is exactly what I am doing. Initially I was using a view and passing these parameters to the function. Doing this I received this error:Msg 8115, Level 16, State 2, Procedure CalcTimeBetweenTwoDates, Line 35Arithmetic overflow error converting expression to data type datetime. I thought this might be due to the fact that I was using a view so I inserted the values into a table. I then used this code:select id,TicketCreatedDate,StatusChangeTimefrom StatusTimeswhere dbo.CalcTimeBetweenTwoDates(TicketCreatedDate,StatusChangeTime) &gt; 4order by idbut I get the same exact error message.So I went in and modified my table. I only put one row in the table and everything worked fine. I then put a second row into the table with the EXACT same data. This also worked fine. I then added a row with DIFFERENT data at which time I got the above error.I have modified your code to remove the lunch hour and I am pretty sure I caused the problem by doing this. I have attached the code for dbo.CalcTimeBetweenTwoDates . I can also add all the other functions if you need me to. Once again your help in this is GREATLY appreciated. Tue, 22 Jan 2008 09:35:34 GMTPeteO-430407http://www.sqlservercentral.com/Forums/Topic426732-1138-1.aspxYou can use the functions [i][b]CalcTimeBetweenTwoDates[/b][/i] & [i][b]CalcTimeSinceCreated[/b][/i] in any SQL statement, stored procedure or UDF For example[code]select dbo.CalcTimeBetweenTwoDates(date_created, date_modified) from [Your Table][/code]will give the duration between the two fields.[code]select dbo.CalcTimeSinceCreated(date_created) from [Your Table][/code]will give you duration passed since the case was created. You can use this function to track the duration since creation for the unresolved cases.Sincerely,MazSat, 19 Jan 2008 12:29:23 GMTMazharuddin Ehsanhttp://www.sqlservercentral.com/Forums/Topic426732-1138-1.aspx[quote][b]Mazharuddin Ehsan (12/28/2007)[/b][hr]Incidently, I am also using this solution to calculate and report the duration passed for the unresolved IT HelpDesk cases at my work place. The results are displayed on an overhead dashboard for everyone to see.quote]How exactly do you pass your variables to the function. We have a date created field and a date modified field. I wish to compare the times between these fields but am having trouble figuring out how to do this. (I am somewhat of a newbie so this probably explains it:hehe:) It sounds like I am trying to do exactly what you are doing so if you could possibly show me how you have accomplished this it would be greatly appreciated!Fri, 18 Jan 2008 10:39:32 GMTPeteO-430407http://www.sqlservercentral.com/Forums/Topic426732-1138-1.aspxVERY COOL. We needed to be able to calculate time between the creation date of a help ticket and the time it was first modified. Without being able to take in account business hours and holidays any returns we came up with were useless. I just modified the code to reflect our 8-5 business hours, and removed the lunch hour deduction. Again thanks a BUNCH!!Thu, 17 Jan 2008 10:58:47 GMTPeteO-430407http://www.sqlservercentral.com/Forums/Topic426732-1138-1.aspxDear Carlos,You had a similar remark earlier also[quote]when i execute the function "select dbo.ufninc_CalcTimeSinceCreated (getdate(),getdate()+1)" it return 9 hours instead of the 8 hours. [/quote]Then you sloved it yourself.By the way, it is working correctly for me (see attached).select dbo.CalcTimeBetweenTwoDates(getdate()-1,getdate())is resulting 0 for me because it is Saturday today.So clearly you are missing something.Regards,MazSat, 05 Jan 2008 11:41:59 GMTMazharuddin Ehsanhttp://www.sqlservercentral.com/Forums/Topic426732-1138-1.aspxselect dbo.CalcTimeBetweenTwoDates(getdate()-1,getdate())your new code without any changes by me returns 8.5 hours instead of the 8 hours.Fri, 04 Jan 2008 08:54:22 GMTcarlosdanielsousahttp://www.sqlservercentral.com/Forums/Topic426732-1138-1.aspxHi carlos, Nice to see you back. I know the reason. This is because you need to modify your function [b]dbo.ufninc_CalcGetDate[/b]I have clarified this in the first posting in this forum. Please check.Add the below code(with your tablename):[quote]while (1 = 1) begin if dbo.DateAtMidnight(@EndDate) in (select dbo.DateAtMidnight(Holiday) from dbo.ListOfHolidays) set @EndDate = dbo.DateAt730(@EndDate + 1) else break end[/quote]in your function [b]dbo.ufninc_CalcGetDate[/b] atthe end.Regards,MazFri, 04 Jan 2008 03:09:37 GMTMazharuddin Ehsanhttp://www.sqlservercentral.com/Forums/Topic426732-1138-1.aspxHello again, Mazharuddin Ehsan Ok, that works but it still have a problem if i put the end date in an holiday:select dbo.ufninc_CalcTimeSinceCreated(convert(datetime,'26-12-2007',105),convert(datetime,'1-1-2008',105))Best RegardsThu, 03 Jan 2008 16:24:58 GMTcarlosdanielsousahttp://www.sqlservercentral.com/Forums/Topic426732-1138-1.aspxDear carlosdanielsousa,Here is the bug:You need to move the code[quote]if dbo.ufninc_DataAMeiaNoite(@VarCalcCreateDate) in (select dbo.ufninc_DataAMeiaNoite (EffectiveIntervalStart) from dbo.CalendarRuleBase where ExtentCode=2 and SubCode=5)set @VarCalcCreateDate = dbo.ufninc_DataInicioTrab(@VarCalcCreateDate + 1)[/quote]7 lines down its present position in your code, ie just after[quote]set @VarCalcCreateDate = dbo.ufninc_DataInicioTrab(@VarCalcCreateDate + 1)end [/quote]Because of this the value [b]@DataInicio[/b] is never getting equal to [b]@DataFim[/b] and it is stuck into an infinite loop.Other than this you seems to have done it impeccably and undrestood the essence. Congrats! Secondly, in the course of debug I found out another bug which originated from my script.Replaceif DATEPART(dw, @VarCalcCreateDate) = 1withif DATEPART(dw, @VarCalcCreateDate) = 7(This mistake happened because in my workplace the weekly off days are Thursday and Friday. I translated it for the SQLServerCentral script to make it Saturday, Sunday. The mistake happened in the course of this change.)Let us know your feedback.Regards,MazThu, 03 Jan 2008 15:18:23 GMTMazharuddin Ehsanhttp://www.sqlservercentral.com/Forums/Topic426732-1138-1.aspxDataAMeiaNoite = dateatmidnightDataInicioTrab= dateAt730DataAlmocoInicio = dateAt1130DataAlmocoFim= dateAt12DataFIMTrab = dateAt16DataInicio= startDateDataFim= endDate[size="2"][font="System"]ALTER function [dbo].[ufninc_CalcTimeSinceCreated](@DataInicio DateTime,@DataFim DateTime)returns realasbegin declare @CalcHours real declare @VarCalcCreateDate datetime if dbo.ufninc_DataAMeiaNoite(dbo.ufninc_CalcCreateDate(@DataInicio)) = dbo.ufninc_DataAMeiaNoite(dbo.ufninc_CalcGetDate(@DataFim)) set @CalcHours = case when dbo.ufninc_CalcCreateDate(@DataInicio) = dbo.ufninc_DataAlmocoInicio(dbo.ufninc_CalcGetDate(@DataFim)) then round(((cast(datediff(ss, dbo.ufninc_CalcCreateDate(@DataInicio), dbo.ufninc_CalcGetDate(@DataFim)) as real)-5400)/3600),2) else round((cast(datediff(ss, dbo.ufninc_CalcCreateDate(@DataInicio), dbo.ufninc_CalcGetDate(@DataFim )) as real)/3600),2) end else begin set @CalcHours = case when dbo.ufninc_CalcCreateDate(@DataInicio) &lt;= dbo.ufninc_DataAlmocoInicio(dbo.ufninc_CalcCreateDate(@DataInicio)) then round(((cast(datediff(ss, dbo.ufninc_CalcCreateDate(@DataInicio), dbo.ufninc_DataFimTrab(dbo.ufninc_CalcCreateDate(@DataInicio))) as real)-5400)/3600),2) else round((cast(datediff(ss, dbo.ufninc_CalcCreateDate(@DataInicio), dbo.ufninc_DataFimTrab(dbo.ufninc_CalcCreateDate(@DataInicio))) as real)/3600),2) end set @VarCalcCreateDate = dbo.ufninc_CalcCreateDate(@DataInicio) while (1 = 1) begin if dbo.ufninc_DataAMeiaNoite(@VarCalcCreateDate) in (select dbo.ufninc_DataAMeiaNoite (EffectiveIntervalStart) from dbo.CalendarRuleBase where ExtentCode=2 and SubCode=5) set @VarCalcCreateDate = dbo.ufninc_DataInicioTrab(@VarCalcCreateDate + 1) if DATEPART(dw, @VarCalcCreateDate) = 6 set @VarCalcCreateDate = dbo.ufninc_DataInicioTrab(@VarCalcCreateDate + 3) else begin if DATEPART(dw, @VarCalcCreateDate) = 1 set @VarCalcCreateDate = dbo.ufninc_DataInicioTrab(@VarCalcCreateDate + 2) else set @VarCalcCreateDate = dbo.ufninc_DataInicioTrab(@VarCalcCreateDate + 1) end if dbo.ufninc_DataAMeiaNoite(@VarCalcCreateDate) = dbo.ufninc_DataAMeiaNoite(dbo.ufninc_CalcGetDate(@DataFim )) set @CalcHours = @CalcHours + (case when @VarCalcCreateDate = dbo.ufninc_DataAlmocoInicio(dbo.ufninc_CalcGetDate(@DataFim )) then round(((cast(datediff(ss, @VarCalcCreateDate, dbo.ufninc_CalcGetDate(@DataFim )) as real)-5400)/3600),2) else round((cast(datediff(ss, @VarCalcCreateDate, dbo.ufninc_CalcGetDate(@DataFim)) as real)/3600),2) end) if dbo.ufninc_DataAMeiaNoite(@VarCalcCreateDate) = dbo.ufninc_DataAMeiaNoite(dbo.ufninc_CalcGetDate(@DataFim)) BREAK else set @CalcHours = @CalcHours + 8 end end return round(@CalcHours,2) end[/font][/size]Thu, 03 Jan 2008 06:21:32 GMTcarlosdanielsousahttp://www.sqlservercentral.com/Forums/Topic426732-1138-1.aspxI think I can resolve the issue if I see your modified code. Apparently it is not able to get out of the loop if the previous day of the second parameter is a holiday. You need to kill this bug.Thu, 03 Jan 2008 05:01:15 GMTMazharuddin Ehsanhttp://www.sqlservercentral.com/Forums/Topic426732-1138-1.aspxit's good to have such a big lunch time!I'm having another problem, when i make select dbo.CalcTimeSinceCreated(convert(datetime,'31-12-2007',105),convert(datetime,'02-01-2008',105))And i've an holiday in my database for the date (01-01-2008),it goes to a infinite cicle.but when i make select dbo.CalcTimeSinceCreated(convert(datetime,'31-12-2007',105),convert(datetime,'03-01-2008',105))all works fine! do you know why?Thu, 03 Jan 2008 04:15:41 GMTcarlosdanielsousahttp://www.sqlservercentral.com/Forums/Topic426732-1138-1.aspxWonderful! Do you mean that you have 1.5 hours of lunch break at your work place. I envy you.Sat, 29 Dec 2007 08:07:27 GMTMazharuddin Ehsanhttp://www.sqlservercentral.com/Forums/Topic426732-1138-1.aspxyou where right! tnx for the answer and for the short waiting time!I changed 1800s to 5400s that is my lunch time, and the code work just like it should!best regardsCarlos Sousa, PortugalFri, 28 Dec 2007 09:26:46 GMTcarlosdanielsousahttp://www.sqlservercentral.com/Forums/Topic426732-1138-1.aspxClearly it is a logical error. At least your code is free from any syntax error. Check carefully. My original program deducts 0.5 hours for the lunch period each day. Possibly the error is around this point.Fri, 28 Dec 2007 09:07:58 GMTMazharuddin Ehsanhttp://www.sqlservercentral.com/Forums/Topic426732-1138-1.aspxHello again, tnx for the answer!I've now another problem, since i have to recalcute all the history of my service desk to show in a report, i've to change your code to accept the start and the end date.I've change your code to accept two parameters, start date and End Date, but when i change the getdate() to enddate parameter, when i execute the function "select dbo.ufninc_CalcTimeSinceCreated (getdate(),getdate()+1)" it return 9 hours instead of the 8 hours. Do you know why?Fri, 28 Dec 2007 07:54:18 GMTcarlosdanielsousa