InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 05:42:45 GMT20http://www.sqlservercentral.com/Forums/Topic649517-1472-1.aspxOne of the wonderful things about table-driven solutions is how easy the coding becomes once the table is in place. However, I'm bookmarking this to go back and take a look at your recursive solution.Wed, 28 Jul 2010 12:34:49 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic649517-1472-1.aspxWasn't even thinking of a table driven way of date evaluation. I like it though, must of took some time to write that.FYI: By adjusting the CTE to a week by week recursion rather than each day I was able to get performance 3x faster. A 1000 day recursion resulted on average a 0.009375 second penalty per invocation (on a moderately speced machine.) I will update that blog when I have time to get the negative advancement complete. Still I'm sure the table driven method is still 20x faster; not to mention all the extra benefits incurred from his toolset.Thanks for replying!Wed, 28 Jul 2010 12:12:23 GMTmmiller-511987http://www.sqlservercentral.com/Forums/Topic649517-1472-1.aspxRecursive CTEs are rarely the fastest performing way of getting something done. Again, a calendar table solution will be both fast and flexible.Tue, 27 Jul 2010 07:41:08 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic649517-1472-1.aspx[quote][b]mmiller-511987 (7/26/2010)[/b][hr]Great way to do date advancement: http://codemiller.blogspot.com/2010/07/sql-server-2005-date-advancement.html[/quote]I don't think using recursive CTE is a good approach for doing this kind of date arithmentic.Take a look at Jeff Modem's example earlier in this post.I ran his against yours and his is 65 times faster!Tue, 27 Jul 2010 07:37:55 GMTMichael Meierruthhttp://www.sqlservercentral.com/Forums/Topic649517-1472-1.aspx[quote][b]michaelberry67 (4/29/2009)[/b][hr]Well it is slow as most in-line functions are, but that is the nature of the beast I guess. It has worked well with larger output sets, but I have not benchmarked it. If you wanna...:) be my guest.I do love this website though. Great information continually!Mike[/quote]By the way, inline table valued functions are NOT slow. Multi-statement table valued functions are, but I've used inline functions many many times to replace user-defined functions and speed up queries.Tue, 27 Jul 2010 07:29:07 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic649517-1472-1.aspxGreat way to do date advancement: http://codemiller.blogspot.com/2010/07/sql-server-2005-date-advancement.htmlMon, 26 Jul 2010 23:31:46 GMTmmiller-511987http://www.sqlservercentral.com/Forums/Topic649517-1472-1.aspxCheck this out... very nice: http://codemiller.blogspot.com/2010/07/sql-server-2005-date-advancement.html - skips weekends and could be easily retrofitted for calendar lookups.Mon, 26 Jul 2010 23:29:29 GMTmmiller-511987http://www.sqlservercentral.com/Forums/Topic649517-1472-1.aspxHi Alex,Could you please post here that function of yours that calcs biz day going into the past :)Fri, 08 Jan 2010 09:21:00 GMTnroudakhttp://www.sqlservercentral.com/Forums/Topic649517-1472-1.aspxWell it is slow as most in-line functions are, but that is the nature of the beast I guess. It has worked well with larger output sets, but I have not benchmarked it. If you wanna...:) be my guest.I do love this website though. Great information continually!MikeWed, 29 Apr 2009 14:48:37 GMTmichaelberry67http://www.sqlservercentral.com/Forums/Topic649517-1472-1.aspx[quote][b]Bob Hovious (4/29/2009)[/b][hr]But, the same function that works acceptably for doing something one time may crawl if repeated a hundred thousand times. The issue Jeff raised is not whether or not your function works or is flexible. Restated, his question was "Is it suitable for mass volumes?" If not, it might work better if written as an inline table valued function for instance.[/quote]Correct... I just suck at asking questions via post. :-)Wed, 29 Apr 2009 11:54:13 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic649517-1472-1.aspxMichael, nobody is saying your baby is ugly. :-) Sure you are proud to have it published, but many of us already have calendar tables that make such queries trivial. In my current job, I've been asked twice to be able to determine the nTh weekday for a particular month. The first query took 5 minutes to write and debug and the second took less time to alter. On the other hand my company has many report requirements for turntimes or lead times in business days, which may or may not include Saturdays, or holidays. A function that lets users specify the number of days and other options is appropriate there. But, the same function that works acceptably for doing something one time may crawl if repeated a hundred thousand times. The issue Jeff raised is not whether or not your function works or is flexible. Restated, his question was "Is it suitable for mass volumes?" If not, it might work better if written as an inline table valued function for instance.Wed, 29 Apr 2009 11:45:17 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic649517-1472-1.aspxThe performance has been quite acceptable. If you need that specific logic it is fairly easy to read and you can pull it out if you wish. The main benefit is that it runs on a calendar table which you can query directly. Also, you can use it to get things like the 3rd working day skipping holidays and weekends. Try that with any other function.:)Wed, 29 Apr 2009 11:04:16 GMTmichaelberry67http://www.sqlservercentral.com/Forums/Topic649517-1472-1.aspxI wasn't going to say it, Jeff. :-) If all the function is used for is to find a [i]single[/i] date (like the first Tuesday of next month) which is then used in a where clause then a few milliseconds wouldn't matter. But you're right that there may be a problem when you need to calculate turntimes or lead times in business days for a large numbers of jobs.Wed, 29 Apr 2009 09:11:32 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic649517-1472-1.aspx[quote][b]michaelberry67 (4/29/2009)[/b][hr]I think you all my benefit from my function that was published by SQL server magazine:http://www.sqlmag.com/Articles/ArticleID/97590/97590.html?Ad=1[/quote]Do you have any performance stats... say, on 100,000 rows?Wed, 29 Apr 2009 08:40:14 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic649517-1472-1.aspxYou are the man....Rock on!Wed, 29 Apr 2009 07:58:14 GMTBob Griffinhttp://www.sqlservercentral.com/Forums/Topic649517-1472-1.aspxI think you all my benefit from my function that was published by SQL server magazine:http://www.sqlmag.com/Articles/ArticleID/97590/97590.html?Ad=1Wed, 29 Apr 2009 07:45:10 GMTmichaelberry67http://www.sqlservercentral.com/Forums/Topic649517-1472-1.aspxthanks bob..Wed, 29 Apr 2009 06:37:05 GMTdodyrydahttp://www.sqlservercentral.com/Forums/Topic649517-1472-1.aspxGETDATE() is a function. You have to include the parens after it. [dbo].fn_AddBizDays(GETDATE(), 1)Be advised that GETDATE includes the current time as well as date, unless fn_AddBizDays strips it off.Tue, 28 Apr 2009 10:44:55 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic649517-1472-1.aspxI'm trying to use this function but need my begin date to be today's date, trying [dbo].fn_AddBizDays(GETDATE, 1) but not working does anyone know how to get this operational from todays date?Tue, 28 Apr 2009 10:34:37 GMTdodyrydahttp://www.sqlservercentral.com/Forums/Topic649517-1472-1.aspxHere's a quick 2005 example of a single select using a tally table. stage 1 uses a tally table to generate a set of dates.stage 2 adds a date enumeration after removing the non-working days (Sat/Sun)stage 3 gets the matching 'business days' using the enumeration.If you need to include a holiday table then include an outer join/is null or 'not in' at stage 2.[code]select max(dts) from ( select dts, ROW_NUMBER ( ) over (order by dts) as nRow from ( select dateadd(dd,datediff(dd,0,GETDATE ()),0)+n as dts from dbo.Tally where n < @requiredDays) x where DATENAME(dw, dts) not in ('Saturday','Sunday') ) ywhere nRow < @requiredDays;[/code]G.Wed, 18 Feb 2009 05:21:27 GMTg.brennanhttp://www.sqlservercentral.com/Forums/Topic649517-1472-1.aspxI was getting 1 million rows in 8.5-9.5 seconds on my laptop without precalculation, but 4 ms for a single row. So faster in batch, but slower in low volumes. Sounds like pre-calc might be the most versatile solution.Fri, 06 Feb 2009 13:09:36 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic649517-1472-1.aspx[quote][b]Bob Hovious (2/5/2009)[/b][hr][quote]So my question here would be do you assume worst case that it's going to be hit really hard and go for the tabular method and take a hit if only doing a few rows or vice versa? [/quote]It won't be the same from system to system. I'm considering doing two versions, one for long batch jobs like reports and one for interactive. Before choosing, you really need to study your data and the nature of your workload. Reports shouldn't drag on for minutes that could be generated in seconds, but displays shouldn't take two seconds of DB time if they can be delivered in a fraction of a second. On the other hand, if the calculation is done for a small number of rows only a few times a day, then a couple of seconds isn't that big a deal. You just have to think about the ultimate consequences of your choice, whatever it may be.That said, do we have any performance numbers from precalculated table approaches?[/quote]Yes. 1-million rows in about 17 seconds. Less than 1 millisecond for a single-row calculation. Posted earlier in the thread. In other words, over 100 times faster than the second-place version.Fri, 06 Feb 2009 06:31:32 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic649517-1472-1.aspx[quote]So my question here would be do you assume worst case that it's going to be hit really hard and go for the tabular method and take a hit if only doing a few rows or vice versa? [/quote]It won't be the same from system to system. I'm considering doing two versions, one for long batch jobs like reports and one for interactive. Before choosing, you really need to study your data and the nature of your workload. Reports shouldn't drag on for minutes that could be generated in seconds, but displays shouldn't take two seconds of DB time if they can be delivered in a fraction of a second. On the other hand, if the calculation is done for a small number of rows only a few times a day, then a couple of seconds isn't that big a deal. You just have to think about the ultimate consequences of your choice, whatever it may be.That said, do we have any performance numbers from precalculated table approaches?Thu, 05 Feb 2009 14:46:34 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic649517-1472-1.aspx[quote]scalar value results: 500 rows: CPU time = 844 ms, elapsed time = 879 ms.15,000 rows: CPU time = 25531 ms, elapsed time = 25603 ms.tabular results:500 rows: CPU time = 2703 ms, elapsed time = 2699 ms.15,000 rows: CPU time = 6656 ms, elapsed time = 6654 ms.[/quote]So my question here would be do you assume worst case that it's going to be hit really hard and go for the tabular method and take a hit if only doing a few rows or vice versa?Thu, 05 Feb 2009 14:17:44 GMTAndy Reillyhttp://www.sqlservercentral.com/Forums/Topic649517-1472-1.aspxI really like that solution too Jim - really simple and yet so neat :)Thu, 05 Feb 2009 14:14:15 GMTAndy Reillyhttp://www.sqlservercentral.com/Forums/Topic649517-1472-1.aspx:peter[b]You 'da man![/b][quote]scalar value results: 500 rows: CPU time = 844 ms, elapsed time = 879 ms.15,000 rows: CPU time = 25531 ms, elapsed time = 25603 ms.tabular results:500 rows: CPU time = 2703 ms, elapsed time = 2699 ms.15,000 rows: CPU time = 6656 ms, elapsed time = 6654 ms.[/quote]i added another set size for the tabular results:[quote]500 rows: CPU time = 2734 ms, elapsed time = 2738 ms.5000 rows: CPU time = 3969 ms, elapsed time = 3967 ms.15000 rows: CPU time = 6656 ms, elapsed time = 6663 ms.[/quote]So, the in-line tabular method takes a sec to get out the blocks, but once it gets going, it flies! :w00t:There is an error in your calculation somewhere (it miscalculates the date) but I'm sure it's a simple oversight. Thanks for sharing your talent!Thu, 05 Feb 2009 14:08:15 GMTantonio.collinshttp://www.sqlservercentral.com/Forums/Topic649517-1472-1.aspx[quote] Let me go on the record as loving this.[/quote]See what you started? Fun isn't it?Thu, 05 Feb 2009 13:33:49 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic649517-1472-1.aspx[quote][b]James Goodwin (2/5/2009)[/b][hr]Using the Constraints of using the existing Holidays Table and not using a date table and no loops, I came up with the following function. ... [/quote]Let me go on the record as loving this.Now duck, dude...-jjgThu, 05 Feb 2009 13:27:15 GMTj-977990http://www.sqlservercentral.com/Forums/Topic649517-1472-1.aspxUsing the Constraints of using the existing Holidays Table and not using a date table and no loops, I came up with the following function. I do use a numbers (Tally) table that does not contain a 0, if you have a zero you need to adjust the where clause.I have only tested slightly in SQL 2000.[code]CREATE Function tmpWorkingDays(@BeginDate datetime, @numDays int)RETURNS datetimeASBEGINDECLARE @val as datetimeDECLARE @tmp TABLE(num int identity(1,1), CalDate datetime)insert into @tmp(CalDate)Select CalDate FROM (SELECT n, DateAdd(d, n, @BeginDate) as CalDate FROM numbers WHERE n <= @NumDays*10) as Calendar --Calendar Dates starting with @BeginDate LEFT JOIN tblHolidays ON Calendar.CalDate = tblHolidays.holiday_date WHERE holiday_id is NULL and datename(dw, CalDate) not in ('Saturday','Sunday')ORDER BY CalDate --Working DaysSelect @val = CalDate from @tmp where num = @numdaysReturn @valEND[/code]An interesting expansion of this is [code]CREATE Function tmpWorkingDaysTable(@BeginDate datetime)Returns @WorkingDays TABLE(num int identity(1,1), CalDate datetime)asBEGINinsert into @WorkingDays(CalDate)Select CalDate FROM (SELECT n, DateAdd(d, n, @BeginDate) as CalDate FROM numbers) as Calendar --Calendar Dates starting with @BeginDate LEFT JOIN tblHolidays ON Calendar.CalDate = tblHolidays.holiday_date WHERE holiday_id is NULL and datename(dw, CalDate) not in ('Saturday','Sunday')ORDER BY CalDate--) WorkingDays --Working DaysReturnEND[/code]Which returns a table beginning at the passed date where the number of working days can be calculated between any two dates by subtracting the num values for the dates. This allows:[code]Select A.CalDate as BeginDate, B.CalDate as EndDateFROM tmpWorkingDaysTable('20080101') A INNER JOIN tmpWorkingDaysTable('20080101') B ON A.num = B.num - 5[/code]To return a set of Days and the day 5 working days in the future. You can then join this into your report and do it with only 2 function calls instead of a function call per row.--JimFiveThu, 05 Feb 2009 12:53:45 GMTJames Goodwinhttp://www.sqlservercentral.com/Forums/Topic649517-1472-1.aspx[quote][b]antonio.collins (2/5/2009)[/b][hr]:peterah. i missed the in-line requirement. then it makes sense, since the function would be just short-hand for a select.[/quote]I quickly made a modified version of your function but am unable to test it myself here and for comparison its better to run at your own machine anyways. Any coding errors I made you have to fix yourself as we cant bounce code over and over. Try it out and let us know how this inline table version works for you.[code]alter function [dbo].[fAddBusinessDays]( @date smalldatetime, @days smallint, @nonWorking1 tinyint, @nonWorking2 tinyint )-- @date: the start date-- @days: number of days to add-- @nonWorking1: weekday number of a non-working day-- @nonWorking2: weekday number of another non-working day---- depends on sequencing table/view [Numbers]( number smallint ) -- depends on holiday table/view [Holidays]( holiday smallint )returns tableas return ( with cleanDateQ( [date] ) as ( select dateadd( day, 0, datediff( day, 0, @date ) ) ) , preCalcQ( [range], [date] ) as ( select case when abs( @days ) * 2 < 10 then 10 else abs( @days ) * 2 end , cleanDateQ.[date] + case when -- working day datepart( weekday, cleanDateQ.[date] ) not in ( @nonWorking1, @nonWorking2 ) then 0 when -- non-working day as well as the folloing day datepart( weekday, cleanDateQ.[date] ) in ( @nonWorking1, @nonWorking2 ) and datepart( weekday, cleanDateQ.[date] ) in ( @nonWorking1, @nonWorking2 ) then 2 when -- non-working day datepart( weekday, cleanDateQ.[date] ) in ( @nonWorking1, @nonWorking2 ) then 1 end from cleanDateQ ) -- get the date of the Nth working day select workDate as endDate from ( -- collect the top N working days and number them select top(abs(@days)+1) workDate, row_number() over (order by number) - 1 as dayNum from ( -- collect consecutive days from the given date to the end of the range select dateadd( day, number * sign(@days), preCalcQ.[date] ) as workDate , number from Numbers cross join preCalcQ where number between 0 and preCalcQ.[range] ) as X where workDate not in (select holiday from Holidays) -- exclude holidays and datepart( weekday, workDate ) not in (@nonWorking1,@nonWorking2) -- exclude non-working days ) as Z where dayNum = abs(@days) -- the Nth working day )go[/code]The smileys are [b])[/b] tokens ofcourse.Thu, 05 Feb 2009 09:30:31 GMTpeter-757102http://www.sqlservercentral.com/Forums/Topic649517-1472-1.aspxOkay, finally had time to do the inline function and retest. The inline function with CROSS APPLY actually seems to run just a hair faster, averaging 89xx ms versus the subquery's 90xx ms, even though the execution plans appear to be the same.WARNING: Because it's a function, it can't see the #holiday temptable.This is a powerful new weapon for the arsenal. Many thanks Peter.... and many thanks to Mr. Gravelle for getting this discussion started.Subquery/Derived TableSQL Server Execution Times: CPU time = 9094 ms, elapsed time = 30777 ms.Inline Function with CROSS APPLYSQL Server Execution Times: CPU time = 8922 ms, elapsed time = 8948 ms.-------------------------------------------------------------------------------------------------Alter FUNCTION dbo.tvfAddBizDays2 ( @startDt datetime, @bizdays int)RETURNS TABLE -- yes, it MUST be an inline function for speedASRETURN ( select top 1 endDate from (select top (@bizdays) @startDT + n as endDate from tally t with(nolock) left join holiday h with(nolock) on h_date = @startDT + n where h_date is null ) dt order by endDate desc )GO------------------------------------------------------------------------------------------- to use itdeclare @timer datetimeset @timer = current_timestamp set statistics time on; update #TEST set endDT = tvf.enddate from #TEST t CROSS APPLY dbo.tvfAddBizDays2(startDt,Bizdays) tvf set statistics time off;select datediff(ms,@timer,current_timestamp)Thu, 05 Feb 2009 09:07:51 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic649517-1472-1.aspx:peterah. i missed the in-line requirement. then it makes sense, since the function would be just short-hand for a select.Thu, 05 Feb 2009 08:19:57 GMTantonio.collinshttp://www.sqlservercentral.com/Forums/Topic649517-1472-1.aspx[quote][b]antonio.collins (2/5/2009)[/b][hr][quote][b]Bob Hovious (2/5/2009)[/b][hr][quote][b]peter (2/5/2009)[/b][hr]When dealing whith functions that are called frequently, it usually pays off to write them as [b]inline table valued[/b] functions (also called parameterized views) and then invoke them using a cross apply.What happends is that the optimizer can see the functions inner workings as part of the main select statement and optimize accordingly. In addition the bizzare call overhead of non-inline functions is circumvented.[/quote]Thanks, peter. I have implemented table-valued functions with CROSS APPLY before, but I wasn't aware of how differently they were handled. I will test that out and post results back later this morning.[/quote]I switched my version (way back on page 5) to produce a single row table-value and it ran a bit slower.[quote] -- get the date of the Nth working day select @endDate = workDate[/quote]became[quote] -- get the date of the Nth working day insert into @returnTable select workDate, dayNum[/quote]benchmarking with [#test15000]( dt smalldatetime, seq int, primary key(seq) ):[/quote][b]insert into @returnTable[/b] means you did not recode the function to the required form. It is not an inline function but a normal function returning a table. It is essential that the function works inline for the optimizer to see the the function body and incorporate it smarter into the main query.Read my full post on the previous page see what exactly I mean with inline.Thu, 05 Feb 2009 08:12:11 GMTpeter-757102http://www.sqlservercentral.com/Forums/Topic649517-1472-1.aspx[quote][b]Bob Hovious (2/5/2009)[/b][hr][quote][b]peter (2/5/2009)[/b][hr]When dealing whith functions that are called frequently, it usually pays off to write them as [b]inline table valued[/b] functions (also called parameterized views) and then invoke them using a cross apply.What happends is that the optimizer can see the functions inner workings as part of the main select statement and optimize accordingly. In addition the bizzare call overhead of non-inline functions is circumvented.[/quote]Thanks, peter. I have implemented table-valued functions with CROSS APPLY before, but I wasn't aware of how differently they were handled. I will test that out and post results back later this morning.[/quote]I switched my version (way back on page 5) to produce a single row table-value and it ran a bit slower.[quote] -- get the date of the Nth working day select @endDate = workDate[/quote]became[quote] -- get the date of the Nth working day insert into @returnTable select workDate, dayNum[/quote]benchmarking with [#test15000]( dt smalldatetime, seq int, primary key(seq) ):[quote]set statistics time on--set statistics io ongodeclare @test table ( dt smalldatetime)insert into @test select dbo.fAddBusinessDays( T.dt, 180, 1, 7) from #test15000 where seq <= 500godeclare @test table ( dt smalldatetime)insert into @test select dbo.fAddBusinessDays( T.dt, 180, 1, 7) from #test15000 T --where seq <= 500godeclare @test table ( dt smalldatetime)insert into @test select X.workDate from #test15000 T cross apply dbo.fAddBusinessDaysT( T.dt, 180, 1, 7) X where seq <= 500godeclare @test table ( dt smalldatetime)insert into @test select X.workDate from #test15000 T cross apply dbo.fAddBusinessDaysT( T.dt, 180, 1, 7) X go[/quote]results are[quote]scalar timings:500 rows: CPU time = 937 ms, elapsed time = 929 ms.15,000 rows: CPU time = 25422 ms, elapsed time = 25480 ms.tabular timings:500 rows: CPU time = 1031 ms, elapsed time = 1033 ms.15,000 rows: CPU time = 31281 ms, elapsed time = 31343 ms.[/quote]Thu, 05 Feb 2009 08:06:50 GMTantonio.collinshttp://www.sqlservercentral.com/Forums/Topic649517-1472-1.aspx[quote][b]peter (2/5/2009)[/b][hr]When dealing whith functions that are called frequently, it usually pays off to write them as [b]inline table valued[/b] functions (also called parameterized views) and then invoke them using a cross apply.What happends is that the optimizer can see the functions inner workings as part of the main select statement and optimize accordingly. In addition the bizzare call overhead of non-inline functions is circumvented.[/quote]Thanks, peter. I have implemented table-valued functions with CROSS APPLY before, but I wasn't aware of how differently they were handled. I will test that out and post results back later this morning.Thu, 05 Feb 2009 07:24:42 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic649517-1472-1.aspx[quote][b]j (2/4/2009)[/b][hr][quote][b]Mazharuddin Ehsan (2/4/2009)[/b][hr]Hi J Gravelle,Congrats for the high response to the write-up and making the topic a hit.[/quote]You're very kind. Shukriya.[quote]I am anxiously waiting for the second part of the thriller, especially as I got published similar solution on the SQLServerCentral.com[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][url=http://www.sqlservercentral.com/Forums/Topic426732-1138-2.aspx][b]Calculating the Number of Business Hours Passed Between Two Points of Time[/b][/url]This will be interesting to see how you approach the solution. I also used a loop based approach. It would be interesting to see alternative solution without using the loop.[/quote]Looks like I'd BETTER take that approach, hey?Between your piece and all the suggestions in this thread, it's likely the accreditation footnotes will be longer than the article itself though.Namasté...-jjg[/quote]Hi jjg,You sure can use the approach that I used. However it would be more useful if you could take up the challenge of incorporating some of the enhancements pointed out like:1. To enable to define the week days dynamic instead of making hard coded (Saturday, Sunday). In many places in the world the weekends are Thursday, Friday or possibly only one day off instead of two.2. Do it without using loopsI am 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. It works fine without any performance issue in spite of the loop based approach. The SLA in our case is 8 hours so it is unlikely that the system has to loop through thousands of years so there is no problem. But sure it will be a performance issue if we use it to loop through so many years.Salaam and Namasté...MazThu, 05 Feb 2009 05:36:59 GMTMazharuddin Ehsanhttp://www.sqlservercentral.com/Forums/Topic649517-1472-1.aspxI'd like to second what Derek says - it's been an intersting discussionCheers to J Gravelle AndyThu, 05 Feb 2009 05:16:36 GMTAndy Reillyhttp://www.sqlservercentral.com/Forums/Topic649517-1472-1.aspxBy the way, I would also congratulate anyone who takes the trouble to write an article, even though, in this case, I think that the solution has limited application. In my case, where I had to process over 100,000 records and work out at least 2 differences in business hours for each record, all the functions I came up with could not compete with a calendar table.Thanks to J Gravelle for the article (and resulting discussion).Thu, 05 Feb 2009 05:12:20 GMTDerek Dongrayhttp://www.sqlservercentral.com/Forums/Topic649517-1472-1.aspx[quote][b]clifford.wilson (2/4/2009)[/b][hr]Hi, I have a similar problem to solve regarding contact centre SLAs. However the measure is on the number of hours the call is open. I need to know how many business hours a support call was opened. Does anyone have a function for doing this? It needs to take into account calls that were open (and/or closed) outside of busienss hours. E.g. support hours are 8am to 6pm Monday to Friday. So, a call opened at 7pm Thursday (we provide support to multiple time zones so it is calls do get opened outside of business hrs) and closed at 10am Monday, then the call is open 12 business hours (10 hrs Friday + 2 hours Monday).[/quote]This sounds similar to the problem I had, except that our SLAs needed to measure the time to respond to a problem as well as how long queries to the submitter were outstanding in addition to the overall time to close. Since the submitters might be in any of over 30 countries and the support centres are in 3 difference countries (and 3 different timezones) and everyone has different working shift patterns and public holidays so the whole thing gets complicated very fast!In addition, of course, there's the Islamic calender for Moslem holidays which strictly cannot be calculated as it really does depend of the phase of the moon and what the weather's like (See [url=http://en.wikipedia.org/wiki/Islamic_calendar#cite_ref-6]Wikipedia[/url]).Hence a calendar table, with support for multiple countries, plus tables for shift working etc, was the only solution.This is an extract showing the holidays we allow for in 2009 (without the country references). There are 111 days (including public holidays at weekends).[code]date holiday01/01/2009 Independence Day, New Year's Day, Nova godina, Viti i Ri02/01/2009 New Year's Day 2 , Saint Berchtold06/01/2009 Day of Ashurah, Epiphany07/01/2009 Orthodox Christmas19/01/2009 Baptism of Jesus Christ, Martin Luther King Day09/02/2009 Ashura16/02/2009 Washington's Birthday01/03/2009 Green Monday03/03/2009 Liberation Day (Den na osvobozhdenieto), Mother's Day08/03/2009 International Women's Day, Milad un Nabi, Revolution Day09/03/2009 Clean Monday (1st Day of Lent)14/03/2009 The Summer Day (Dita e Veres)15/03/2009 National Day17/03/2009 St Patrick's Day19/03/2009 Saint Joseph's Day22/03/2009 Nevruz25/03/2009 25th March, Greek Independence Day01/04/2009 Greek Cypriot National Day05/04/2009 Qing Ming Jie09/04/2009 Day of National Unity, Maundy Thursday10/04/2009 Good Friday13/04/2009 Easter Monday, Velikden14/04/2009 Easter Tuesday17/04/2009 Evacuation Day, Orthodox Good Friday20/04/2009 Orthodox Easter Monday25/04/2009 Anniversary of Liberation26/04/2009 Freedom Day30/04/2009 Queen's Day01/05/2009 Labor Day, Labour Day, Den na truda, May Day, Nje Maji, State Holiday02/05/2009 Autonomous Community Day, Labour Day 203/05/2009 Constitution Day, Labour Day 304/05/2009 Early Spring Holiday, May Day, Remembrance of the dead05/05/2009 Liberation Day06/05/2009 Martyrs' Day, St George's Day (Gergiovden)08/05/2009 Fete de La Victoire 1945, General Prayer Day, Liberation Day09/05/2009 Victory over Fascism12/05/2009 St Andrews Day15/05/2009 San Isidro17/05/2009 Constitution Day18/05/2009 Victoria Day21/05/2009 Ascension Day24/05/2009 Bulgarian Educatio, Culture and Slavonic Literature Day25/05/2009 Late Spring Bank Holiday, Memorial Day26/05/2009 Independence Day01/06/2009 June Bank Holiday, Pentecost, WhitMonday02/06/2009 Republic Day05/06/2009 Constitution Day06/06/2009 National Day08/06/2009 Day of the Holy Spirit11/06/2009 Corpus Christi13/06/2009 San Antony19/06/2009 Commemoration Day, Midsummer Eve29/06/2009 St Peter and St Paul01/07/2009 Canada Day, CPC Founding Day04/07/2009 Independence Day05/07/2009 Independence Day, St Cyril & St Methodus06/07/2009 Jan Hus Day14/07/2009 Bastille Day19/07/2009 Ista and Miraj21/07/2009 National Holiday23/07/2009 Egypt's Revolution Day01/08/2009 Army Day, Swiss National Day03/08/2009 August Bank Holiday06/08/2009 Shab-eBr'aat15/08/2009 Assumption Day, Assumption of the Virgin Mary, Assumption of Virgin Mary, Dormition of Theotokos20/08/2009 St Stephen Day21/08/2009 First day of fasting28/08/2009 Dormition day of All-Holy Theotokos31/08/2009 Summer Bank Holiday01/09/2009 Union of Syria, Egypt & Libya06/09/2009 Unification Day07/09/2009 Labor Day, Labour Day10/09/2009 Ali ibn Abi Talib's death16/09/2009 Nusul Al-Qur'an20/09/2009 Big Bayram (Bajrami i Madh), Eid ul-Fitr, Swiss Federal Feast21/09/2009 Eid ul-Fitr 222/09/2009 Independence Day28/09/2009 St Wenceslas (Czech Statehood)01/10/2009 Cyprus Independence Day, National Day03/10/2009 German Unity Day05/10/2009 Republic Day06/10/2009 Start of 1973 October war12/10/2009 Columbus Day, National Holiday, Thanksgiving14/10/2009 Day of Svetitskhoveli Cathedral19/10/2009 Mother Teresa Day23/10/2009 National Day26/10/2009 Austrian National Day, October Bank Holday28/10/2009 Greek National Day, Ochi Day31/10/2009 Reformation Day01/11/2009 All Saints Day, Anniversary of the Revolution, National Revival Day09/11/2009 Feast of the Almudena Virgin11/11/2009 Armistice Day, Independence Day, Remembrance Day, Veterans Day16/11/2009 National Day17/11/2009 Struggle for Freedom/Democracy23/11/2009 St George's Day25/11/2009 Hajj26/11/2009 Hajj 2, Thanksgiving27/11/2009 Eid ul-Adha, Small Bayram (Bajrami i Vogel / al-Eid Adha)28/11/2009 Eid ul-Adha 2, Independence Day (Dita e Pavaersise)29/11/2009 Liberation Day (Dita e Clirimit)01/12/2009 Independence Day06/12/2009 Constitution Day07/12/2009 Independence Day08/12/2009 Immaculate Conception17/12/2009 Islamic New Year23/12/2009 Little Christmas Day24/12/2009 Christmas Eve25/12/2009 1st Day of Christmas, Christmas Day26/12/2009 2nd Day of Christmas, Boxing Day, Day of Ashurah, St Stephen's Day28/12/2009 Boxing Day, St Stephen's Day31/12/2009 New Year's Eve[/code]In fact, there are only 179 days when everyone is working and only 8 weeks where everyone works the whole week (Monday-Friday)!Thu, 05 Feb 2009 04:58:19 GMTDerek Dongrayhttp://www.sqlservercentral.com/Forums/Topic649517-1472-1.aspx[quote][b]Bob Hovious (2/4/2009)[/b][hr]More on performance. ...Even using the tally table technique in a user function made the update take MINUTES to run, but that's to be expected when a user function queries a table. (Sigh. Note to self: Make sure we're not using the function in any batch stored procs.) To be fair to JJG, if his function is being run for just one row at a time, say at the point a specific order is being displayed on a screen, then the optimizer wouldn't get a chance to show off like that....[/quote]When dealing whith functions that are called frequently, it usually pays off to write them as [b]inline table valued[/b] functions (also called parameterized views) and then invoke them using a cross apply.[code]select somefieldsfrom sometable cross apply someFunction( sometable.inputField ) functionResultswhere functionResults.someFunctionResultField = someCondition[/code]What happends is that the optimizer can see the functions inner workings as part of the main select statement and optimize accordingly. In addition the bizzare call overhead of non-inline functions is circumvented.Try it out and check books online on the details of how to modify your function. Be aware that this form of a function requires that your function body is a single select wrapped up in a return statement! Most code can be rewritten to fit and even difficult ones can be made readable and understandable by making use of one or more CTE (Common Table Expressions).Thu, 05 Feb 2009 02:22:30 GMTpeter-757102