InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 14:49:25 GMT20http://www.sqlservercentral.com/Forums/Topic991677-2630-1.aspx[quote][b]CELKO (9/23/2010)[/b][hr]I still prefer Julianizing the business days to make the math easier[/quote]I know it's an old post but absolutely spot on here, Joe. It makes life much easier and the code much faster especially when proper indexing is in place.Sun, 11 Mar 2012 12:27:23 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic991677-2630-1.aspxIt depends. I find that this is a lot easier to discuss on specific examples and I’d like to analyze a couple. Let’s take the code that you present in your article. There you have a couple of functions “NearestBusinessDayOnOrBefore” and “BusinessDaysBetweenEndsOfDays”. I had plenty of cases like that in production code where we had to validate UDFs. In that case you should calculate the results yourself and T.S.T. could be an ideal tool to use.If you are talking about a test scenario where you expect a stored procedure to return hundreds or thousands of rows, then you are most likely talk about an integration test and not a unit test. Let’s say that you have a stored procedure that will return a dataset where every row contains information about the cost accrued per day in the context of some financial dealings. Maybe the issue is complicated by the fact that there are a lot of parameters like: discounts that fluctuate with time, service interruptions, variable level of service provided, holidays, client status, legal aspects and so on. If you will feed to the system scenarios that cover a long period of time and include variations of a lot of these parameters you may get a result that will consist of hundreds and thousands of rows where the results are impossible to calculate manually. In this case TST can still be useful especially if you store the baselines in some tables or if the baseline can be calculated by an alternate set of procedures. However, depending of the test infrastructure and the tools you have it is possible you will want to use something different. Like I said, this type of test is not a unit test – it is an integration test. Traditionally there is a lot of confusion between the two and in my experience a lot of people build integration tests and call them unit tests. Ignoring for a moment the terminology issue, such a way of doing things is a valid one and automating it has value but also has certain implication (most of them around maintenance) that have to be understood. I’ll get back to this in a minute. A unit test will be different. You’ll feed to the system a set of scenarios where only one parameter varies and where the result consists of a very small data set typically 1-10 rows. It is a little bit more to be said about how the code and the unit tests can be written in a way that will assure code coverage in scenarios where multiple parameter vary simultaneously. These types of tests will be appropriate for TST and the expected results will be calculated manually. Getting back to the case where you do an integration test- that can have great advantages but also has certain downsides. Advantages: - You can cover a large number of cases that include complicated combinations of the scenarios parameters. Creating and freezing a baseline can be done very productively in this way. Downsides: - This approach suffers from a test matrix explosion. I have seen cases where this was a significant issue. The result was that not all test cases were covered. Also when calculating the expected results there is a real risk to accept erogenous results. Calculating expected results based on some alternate automation is not trivial. Unit tests will not suffer from the matrix explosion issue. - When a calculation rule changes it can be sometimes difficult to recalculate the expected result. Unit tests are great in this case if that were written correctly in the sense that they have a minimum of overlapping. I think that both integration tests and unit tests have value but it is important to understand the difference ant their cost implication especially long term related to maintenance in face of changing requirements. In conclusion I think that T.S.T can give you a very fast start – asks only for SQL expertise and has no other dependencies. It is also very easy to install and very easy to learn for someone who knows SQL. For large systems it may make sense to be combined or even replaced with heavier and more feature rich frameworks.Tue, 05 Oct 2010 22:19:56 GMTladi.molnarhttp://www.sqlservercentral.com/Forums/Topic991677-2630-1.aspxLadi,I think that when we are dealing with T-SQL and the expected results are result sets consisting of rows and columns, with column names and column types, following the principle "that you should calculate the expected result yourself" uses up a huge amount of time. It is much faster to let the tool generate the results for you, if you have a tool you can trust. I do have such a tool, which saves me a huge amount of time. What do you think?Tue, 05 Oct 2010 19:17:08 GMTAlexander Kuznetsovhttp://www.sqlservercentral.com/Forums/Topic991677-2630-1.aspx[quote][b]Alexander Kuznetsov (9/25/2010)[/b][hr]Ladi,This is a interesting suggestion, a response I have been waiting for. I know about your framework, I read your article and commented on it. What you are suggesting is certainly worth doing. What we are doing in C# is also a working approach.Yet I have another suggestion. Once we are done with manual runs, once we are completely satisfied with the results, we can just use a simple tool to save expected results in Json or XML. Because we frequently have multiple result sets, we better do it in C# - T-SQL is incapable of capturing multiple result sets. Generating expected results is much faster than having to write unit tests.Also when expected results are decoupled from the test, it simplifies maintenance.What do you think?[/quote]What you are suggesting is a practical approach. However it goes a little bit against an important principle in unit testing – that you should calculate the expected result yourself and not trust the application to provide a baseline for you. Also in case of TDD that is not really appropriate (or possible).I could see some scenarios that involve integration testing rather than unit testing where your approach would be OK. For example when you’d want to collect large results and consider them as a baseline to protect against regressions or changes in behavior before a refactoring. Bringing “better languages” in the test automation has some advantages. SQL is error prone compared with C# so if you have a solid infrastructure written in C# that could allow unit testing of SQL code that may be a better solution than T.S.T.T.S.T. is addressed to developers or teams who are familiar with T-SQL but do not have the expertise or the environment needed to support other unit test tool that could exercise SQL Code. For these situations T.S.T. is ideal. Your article were written as if for such an environment (the test code was also in SQL) so that is why I thought T.S.T. was an approach worth mentioning. Tue, 05 Oct 2010 18:53:21 GMTladi.molnarhttp://www.sqlservercentral.com/Forums/Topic991677-2630-1.aspx[quote][b]migurus (9/29/2010)[/b][hr]Thanks for a simple yet effective tool.One thing was not clear = why all those functions are declared to return TABLE? it looks to me these funcs return just a date. What am I missing?[/quote]migurus,I made sure that my functions are inline, so that they perform fast. TABLE keyword if just part of declaration syntax for inline functions.Sun, 03 Oct 2010 12:54:07 GMTAlexander Kuznetsovhttp://www.sqlservercentral.com/Forums/Topic991677-2630-1.aspx[quote][b]CELKO (9/27/2010)[/b][hr][quote][b]Alexander Kuznetsov (9/24/2010)[/b][hr][quote][b]CELKO (9/23/2010)[/b][hr]I still prefer Julianizing the business days to make the math easier CREATE TABLE Calendar (cal_date DATE NOT NUL PRIMARY KEY, julian_business_nbr INTEGER NOT NULL, ...); INSERT INTO Calendar VALUES ('2007-04-05', 42), ('2007-04-06', 43), -- good friday ('2007-04-07', 43), ('2007-04-08', 43), -- Easter sunday ('2007-04-09', 44), ('2007-04-10', 45); --Tuesday To compute the business days from Thursday of this week to next Tuesday: SELECT (C2. julian_business_nbr - C1. julian_business_nbr) FROM Calendar AS C1, Calendar AS C2 WHERE C1.cal_date = '2007-04-05', AND C2cal_date = '2007-04-10'; [/quote]Yes, we do it too. When a new holiday comes up, like President Ford's funeral, we update all those future numbers.[/quote]The updating is not much of a problem:1) New holidays do not occur very often, per one or two per decade at most. But queries about business days occur very frequently. [/quote]Joe, In general, I concur - storing consecutive numbers with business days makes perfect sense. Yet changes in what is a business day occur all the time, especially if we deal with multiple countries, and a business day must be such in all involved countries. Even if we limit the scope to US only, changes do occur. Just recently, Chicago Mercantile Exchange announced how they are going to operate on Columbus Day, and that seems to e a change from previous years.Sun, 03 Oct 2010 12:52:11 GMTAlexander Kuznetsovhttp://www.sqlservercentral.com/Forums/Topic991677-2630-1.aspxThanks for a simple yet effective tool.One thing was not clear = why all those functions are declared to return TABLE? it looks to me these funcs return just a date. What am I missing?Wed, 29 Sep 2010 01:16:47 GMTmigurushttp://www.sqlservercentral.com/Forums/Topic991677-2630-1.aspx[quote][b]Alexander Kuznetsov (9/24/2010)[/b][hr][quote][b]CELKO (9/23/2010)[/b][hr]I still prefer Julianizing the business days to make the math easier CREATE TABLE Calendar (cal_date DATE NOT NUL PRIMARY KEY, julian_business_nbr INTEGER NOT NULL, ...); INSERT INTO Calendar VALUES ('2007-04-05', 42), ('2007-04-06', 43), -- good friday ('2007-04-07', 43), ('2007-04-08', 43), -- Easter sunday ('2007-04-09', 44), ('2007-04-10', 45); --Tuesday To compute the business days from Thursday of this week to next Tuesday: SELECT (C2. julian_business_nbr - C1. julian_business_nbr) FROM Calendar AS C1, Calendar AS C2 WHERE C1.cal_date = '2007-04-05', AND C2cal_date = '2007-04-10'; [/quote]Yes, we do it too. When a new holiday comes up, like President Ford's funeral, we update all those future numbers.[/quote]The updating is not much of a problem:1) New holidays do not occur very often, per one or two per decade at most. But queries about business days occur very frequently. Think about expected delivery dates which are quoted in business days. How many times a day do you think Amazon or Netflix uses an expected delivery date computation or look-up? So design the schema for the most common case. 2) The julianized date column will not be indexed, so it gets updated in place without any re-indexing overhead. If you want to save space, use a SMALLINT (32,767 = 89 years). The DATE data type is only 3 bytes now. Even if I have other columns, I should have short rows and a low percentage of free space on the data pages. 3) By indexing on the calendar date in descending order, we can keep the current time frame in cache or main storage, so the julianized date math will be done without much disk access. The math is straight integer subtraction, far faster than calling a temporal procedure.Mon, 27 Sep 2010 14:34:45 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic991677-2630-1.aspxThank you Alvin, your remark together with the link from wildt about[url=http://arcanecode.com/2009/11/18/populating-a-kimball-date-dimension/ ]Populating a Kimball Date Dimension[/url] are what I am looking for.Sun, 26 Sep 2010 09:10:17 GMTIulian -207023http://www.sqlservercentral.com/Forums/Topic991677-2630-1.aspxLadi,This is a interesting suggestion, a response I have been waiting for. I know about your framework, I read your article and commented on it. What you are suggesting is certainly worth doing. What we are doing in C# is also a working approach.Yet I have another suggestion. Once we are done with manual runs, once we are completely satisfied with the results, we can just use a simple tool to save expected results in Json or XML. Because we frequently have multiple result sets, we better do it in C# - T-SQL is incapable of capturing multiple result sets. Generating expected results is much faster than having to write unit tests.Also when expected results are decoupled from the test, it simplifies maintenance.What do you think?Sat, 25 Sep 2010 15:08:27 GMTAlexander Kuznetsovhttp://www.sqlservercentral.com/Forums/Topic991677-2630-1.aspxI really want to encourage people to automate these type of tests. I know that you said in the article about automating tests that “it is not a commonly accepted practice yet” and I think that may be because of the overhead involved by some of the automation frameworks. Here is a solution that is automated yet it is as simple to write as your manual tests. Once you've installed the TST database (http://tst.codeplex.com, once downloaded it takes about 20 seconds to install and it is all T-SQL) all you’d have to do for your case is to write the tests procedures below. Then you can execute the tests in command prompt by running: "TST /RunAll DatabaseName" or in SQL by running "EXEC TST.Runner.RunAll DatabaseName"CREATE PROCEDURE dbo.SQLTest_NearestBusinessDayOnOrBeforeAS BEGIN DECLARE @ActualResult DATETIME SELECT @ActualResult = Date FROM dbo.NearestBusinessDayOnOrBefore('20100827') EXEC TST.Assert.Equals 'NearestBusinessDayOnOrBefore(''20100827'')', {d '2010-08-27'}, @ActualResult SELECT @ActualResult = Date FROM dbo.NearestBusinessDayOnOrBefore('20100828') EXEC TST.Assert.Equals 'NearestBusinessDayOnOrBefore(''20100828'')', {d '2010-08-30'}, @ActualResult SELECT @ActualResult = Date FROM dbo.NearestBusinessDayOnOrBefore('20100829') EXEC TST.Assert.Equals 'NearestBusinessDayOnOrBefore(''20100828'')', {d '2010-08-30'}, @ActualResult SELECT @ActualResult = Date FROM dbo.NearestBusinessDayOnOrBefore('20100830') EXEC TST.Assert.Equals 'NearestBusinessDayOnOrBefore(''20100828'')', {d '2010-08-30'}, @ActualResultENDGOCREATE PROCEDURE dbo.SQLTest_NearestBusinessDayOnOrBeforeNullAS BEGIN DECLARE @ActualResult DATETIME SELECT @ActualResult = Date FROM dbo.NearestBusinessDayOnOrBefore(NULL) EXEC TST.Assert.IsNull 'NearestBusinessDayOnOrBefore(NULL) should return NULL', @ActualResultENDGOCREATE PROCEDURE dbo.SQLTest_BusinessDaysBetweenEndsOfDaysOutOfRangeAS BEGIN DECLARE @ActualResult DATETIME SELECT @ActualResult = NumDays FROM dbo.BusinessDaysBetweenEndsOfDays('20010827', '20100817') EXEC TST.Assert.IsNull 'BusinessDaysBetweenEndsOfDays(''20010827'', ''20100817'') should return NULL', @ActualResult SELECT @ActualResult = NumDays FROM dbo.BusinessDaysBetweenEndsOfDays('20100827', '20990817') EXEC TST.Assert.IsNull 'BusinessDaysBetweenEndsOfDays(''20100827'', ''20990817'') should return NULL', @ActualResult SELECT @ActualResult = NumDays FROM dbo.BusinessDaysBetweenEndsOfDays('20010827', '20990817') EXEC TST.Assert.IsNull 'BusinessDaysBetweenEndsOfDays(''20010827'', ''20990817'') should return NULL', @ActualResultENDGOSat, 25 Sep 2010 02:32:30 GMTladi.molnarhttp://www.sqlservercentral.com/Forums/Topic991677-2630-1.aspx[quote][b]CELKO (9/23/2010)[/b][hr]I still prefer Julianizing the business days to make the math easier CREATE TABLE Calendar (cal_date DATE NOT NUL PRIMARY KEY, julian_business_nbr INTEGER NOT NULL, ...); INSERT INTO Calendar VALUES ('2007-04-05', 42), ('2007-04-06', 43), -- good friday ('2007-04-07', 43), ('2007-04-08', 43), -- Easter sunday ('2007-04-09', 44), ('2007-04-10', 45); --Tuesday To compute the business days from Thursday of this week to next Tuesday: SELECT (C2. julian_business_nbr - C1. julian_business_nbr) FROM Calendar AS C1, Calendar AS C2 WHERE C1.cal_date = '2007-04-05', AND C2cal_date = '2007-04-10'; [/quote]Yes, we do it too. When a new holiday comes up, like President Ford's funeral, we update all those future numbers.Fri, 24 Sep 2010 14:24:35 GMTAlexander Kuznetsovhttp://www.sqlservercentral.com/Forums/Topic991677-2630-1.aspxA few suggestions:1) Use the BIT datatype for Is* columns, which eliminates the need for a CONSTRAINT2) Make the primary key of the calendar an INT in YYYYMMDD format (per Ralph Kimball), which makes joining faster. If you need a DATE representation, just add a column for it3) If you need the closest business date on or before the current date, just add a column for it and populate it once when you create the table. Along the same lines, you may add columns for DayNumberOfYear, WeekNumberOfYear, MonthNumberOfYear, NumberOfHolidaysThisMonth, NumberOfHolidaysThisYear, etc.Larry.Shiller@gmail.comFri, 24 Sep 2010 08:24:51 GMTlarry-610474http://www.sqlservercentral.com/Forums/Topic991677-2630-1.aspxI still prefer Julianizing the business days to make the math easier CREATE TABLE Calendar (cal_date DATE NOT NUL PRIMARY KEY, julian_business_nbr INTEGER NOT NULL, ...); INSERT INTO Calendar VALUES ('2007-04-05', 42), ('2007-04-06', 43), -- good friday ('2007-04-07', 43), ('2007-04-08', 43), -- Easter sunday ('2007-04-09', 44), ('2007-04-10', 45); --Tuesday To compute the business days from Thursday of this week to next Tuesday: SELECT (C2. julian_business_nbr - C1. julian_business_nbr) FROM Calendar AS C1, Calendar AS C2 WHERE C1.cal_date = '2007-04-05', AND C2cal_date = '2007-04-10'; Thu, 23 Sep 2010 11:42:05 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic991677-2630-1.aspxwildh: thanks for looking those links up. One of those was very helpful to me! Why re-invent the wheel.Thu, 23 Sep 2010 11:12:44 GMTJJ Bhttp://www.sqlservercentral.com/Forums/Topic991677-2630-1.aspxAny item of information that can be derived from a date could be stored in the calendar table if it simplifies or improves the performance of queries or code. Whether or not the data items you mention should be added to the calendar table is an implementation decision, but they all seem plausible candidates. It is quite normal for a calendar table to have more than 20 data items, e.g. http://arcanecode.com/2009/11/18/populating-a-kimball-date-dimension/Thu, 23 Sep 2010 09:33:44 GMTfeakesjhttp://www.sqlservercentral.com/Forums/Topic991677-2630-1.aspx[quote][b]wildh (9/23/2010)[/b][hr]Is this any good - http://facility9.com/2009/04/23/populating-us-federal-holidays-in-a-calendar-table/or - http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.htmlWe have a similar issue in the UK but it's only a 10 min job to crib it from a web site into a update script. But hang on, then it's a 4 month wait for the DBA's to run it.[/quote]Yup, that's basically the T-SQL I ended up writing. If you want to be picky, there is also a US law that gives federal employees a holiday for presidential inaugurations but only in certain jurisdictions near Washington DC. There are also unplanned holidays like when Gerald Ford dies.Thu, 23 Sep 2010 09:15:46 GMTAndrew Notarianhttp://www.sqlservercentral.com/Forums/Topic991677-2630-1.aspxI frequently used a similar table where I used to work. It was the Date Dimension table from our data warehouse. Look around, you may already have something like this, and the logic to build it, on one of your servers.Thu, 23 Sep 2010 09:14:27 GMTAlvin Ramardhttp://www.sqlservercentral.com/Forums/Topic991677-2630-1.aspx[quote][b]Iulian -207023 (9/23/2010)[/b][hr]Very nice article Alex, thank you.Indeed IsBusinessDate 'Y' or 'N' is great for building an understanding of the concepts, I remember when I implemented this functionality using something similar; I used the name of the holiday in the HolidayName field ('Sunday', 'Saturday', 'Easter', 'Independence day', etc. ) and considering as business day the records where HolidayName is null or empty.This helped me when I was asked [i]"why there are less working days this month?" :-)[/i], and I had the answer at hand. Also it was easy for the HR to define company's holidays as they might change during the year, by just typing the name of the holiday in the interface.I am curious in approaching multiple calendars, i.e. for different departments or for different geographies.For example: National days are different for most of the countries but also some religious holidays, Italian branch of one company has the Easter holiday in another period of time than Russian branch.Also for production department some Saturdays might be business/working days since for sales dept. Saturdays might be weekends/non business days.Kind regards,Iulian Cozma[/quote]Iulian,We have multiple calendars, for countries and individul companies as well. For instance, Chicago Mercantile Exchange's calendar is sometimes different from the offcial US one. All we need to do is to add CalendarName column to the primary key, and @CalendarName parameter to all these functions.Thu, 23 Sep 2010 09:07:43 GMTAlexander Kuznetsovhttp://www.sqlservercentral.com/Forums/Topic991677-2630-1.aspx[quote][b]feakesj (9/23/2010)[/b][hr]The article tries to cover too much ground and ends up doing none of the topics justice. In particular, the first function described would have been much better implemented as an additional column on the calendar table -- a key benefit of such tables is that they allow results to be looked up rather than calculated.[/quote]If we only have one typical query, then adding one column makes sense. If, however, we have dozens fo different queries, such as "last business day of current month", "first business day of next month" and such, keeping too many columns, and maintaining them if there is an additional holiday (such as President Ford's funeral) becomes very messy.Thu, 23 Sep 2010 08:55:44 GMTAlexander Kuznetsovhttp://www.sqlservercentral.com/Forums/Topic991677-2630-1.aspx[quote][b]Alexander Kuznetsov (9/22/2010)[/b][hr]Comments posted to this topic are about the item [B]&lt;A HREF="/articles/Test-Driven+Development/71075/"&gt;Fun with business days, calendar tables, and test-driven development&lt;/A&gt;[/B][/quote]That's right, I concur. Thanks!Thu, 23 Sep 2010 08:52:59 GMTAlexander Kuznetsovhttp://www.sqlservercentral.com/Forums/Topic991677-2630-1.aspxMany times I have had to ask what portion of this date range was non-work time.This does indeed vary by geography so we have added gmt start and end for each holiday.We then have to handle four cases to determine the overlaps so the function has to sum the time from UNION ALL of the cases.I can post this function if anyone is interested but if there is a better way please let me know.Thu, 23 Sep 2010 08:52:07 GMTRobert Jhttp://www.sqlservercentral.com/Forums/Topic991677-2630-1.aspxIs this any good - http://facility9.com/2009/04/23/populating-us-federal-holidays-in-a-calendar-table/or - http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.htmlWe have a similar issue in the UK but it's only a 10 min job to crib it from a web site into a update script. But hang on, then it's a 4 month wait for the DBA's to run it.Thu, 23 Sep 2010 08:29:19 GMTwildhhttp://www.sqlservercentral.com/Forums/Topic991677-2630-1.aspx[quote][b]wildh (9/23/2010)[/b][hr]Andrew,Is this any good for you? - http://www.opm.gov/operating_status_schedules/fedhol/2010.aspbtw, Groundhog Day was on TV this weeend in the UK, hadn't seen it in years :)[/quote]This is the master list as provided by the US government, but it's not very easy to parse.Someone needs to make this list into something clean like XML, JSON, etc. Hell, even a CSV would be better than parsing 10 web pages with weekday names and optional asterisks.Thu, 23 Sep 2010 08:11:13 GMTAndrew Notarianhttp://www.sqlservercentral.com/Forums/Topic991677-2630-1.aspxAndrew,Is this any good for you? - http://www.opm.gov/operating_status_schedules/fedhol/2010.aspbtw, Groundhog Day was on TV this weeend in the UK, hadn't seen it in years :)Thu, 23 Sep 2010 08:00:03 GMTwildhhttp://www.sqlservercentral.com/Forums/Topic991677-2630-1.aspxSomeone should really make a list of US Federal Holidays. You would think that data.gov would have something like that.As many have done before me, I ended up making my own table. None of the solutions I found online addressed the move of fixed-date holidays to prior Fridays or following Mondays. And most of them had Groundhog Day in there like that actually means anything outside Punxsutawney.Thu, 23 Sep 2010 06:51:58 GMTAndrew Notarianhttp://www.sqlservercentral.com/Forums/Topic991677-2630-1.aspxI'd agree with feakesj, we've got an 'is working day' bit field. Work out the days by counting that value between the two dates, simple. Looks this is an over engineered solution. However, I will admit that this solution dosnt work well if multiple sites/ business units have different holidays, but then again neither would the articles solution.Thu, 23 Sep 2010 03:09:33 GMTwildhhttp://www.sqlservercentral.com/Forums/Topic991677-2630-1.aspxVery nice article Alex, thank you.Indeed IsBusinessDate 'Y' or 'N' is great for building an understanding of the concepts, I remember when I implemented this functionality using something similar; I used the name of the holiday in the HolidayName field ('Sunday', 'Saturday', 'Easter', 'Independence day', etc. ) and considering as business day the records where HolidayName is null or empty.This helped me when I was asked [i]"why there are less working days this month?" :-)[/i], and I had the answer at hand. Also it was easy for the HR to define company's holidays as they might change during the year, by just typing the name of the holiday in the interface.I am curious in approaching multiple calendars, i.e. for different departments or for different geographies.For example: National days are different for most of the countries but also some religious holidays, Italian branch of one company has the Easter holiday in another period of time than Russian branch.Also for production department some Saturdays might be business/working days since for sales dept. Saturdays might be weekends/non business days.Kind regards,Iulian CozmaThu, 23 Sep 2010 02:36:19 GMTIulian -207023http://www.sqlservercentral.com/Forums/Topic991677-2630-1.aspxThe article tries to cover too much ground and ends up doing none of the topics justice. In particular, the first function described would have been much better implemented as an additional column on the calendar table -- a key benefit of such tables is that they allow results to be looked up rather than calculated.Thu, 23 Sep 2010 01:32:19 GMTfeakesjhttp://www.sqlservercentral.com/Forums/Topic991677-2630-1.aspxHiJust a suggestion - but when you are doing your workday bit - you should really ensure you get the right setting for datefirst into the script, otherwise you might find it going horribly wrong...Thu, 23 Sep 2010 01:21:53 GMTRichBhttp://www.sqlservercentral.com/Forums/Topic991677-2630-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Test-Driven+Development/71075/">Fun with business days, calendar tables, and test-driven development</A>[/B]Wed, 22 Sep 2010 20:25:48 GMTAlexander Kuznetsov