InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 15:47:08 GMT20http://www.sqlservercentral.com/Forums/Topic961796-2755-1.aspxNice article. Thanks for sharing. Now it is time to play with the scripts.Tue, 03 Aug 2010 15:29:05 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic961796-2755-1.aspxStefan,First I would like to say, great Article. I did notice an issue though. As this is a standard ISO 544 Financial Calendar (544 refers to the week count of each period in a quarter), the last week of the first period in a quarter can include the first week of the next month. This causes the calculation of the Fiscal Year to glitch. Take a look at what happens around 03/01/2009 for an example.[code="sql"] Case WHEN DATEPART(MM,FiscalDate) > FiscalPeriod THEN DATEPART(YYYY, FiscalDate) + 1 ELSE DATEPART(YYYY, FiscalDate) END AS FiscalYear[/code]Simply checking that the Fiscal Period also is equal to 12 will fix this:[code="sql"] Case WHEN (DATEPART(MM,FiscalDate) > FiscalPeriod) and (FiscalPeriod = 12) THEN DATEPART(YYYY, FiscalDate) + 1 ELSE DATEPART(YYYY, FiscalDate) END AS FiscalYear[/code]And, in keeping with the spirit of the CTE:[code="sql"]Declare @StartDate as Date = '12/31/2008';WITH YearDays (YearDay, N)AS ( select top 3640 CASE Tally.N%364 WHEN 0 THEN 364 ELSE N%364 END AS YearDay, N FROM Tally ),FWQ (FiscalWeek, FiscalQuarter, YearDay, N)AS ( Select CAST(CEILING(Cast(YearDay as Real)/CAST(7 as Real)) AS INT) as FiscalWeek, CEILING(Cast(YearDay as Real)/CAST(91 as real)) as FicalQuarter, YearDay, N FROM YearDays ),FP ( FiscalDate, FiscalWeekDay, FiscalPeriod, FiscalWeek, FiscalQuarter )AS ( SELECT Cast(DATEADD(DD, N, @StartDate) as DATE) AS FiscalDate, CASE DATEPART(DW, DATEADD(dd, N, @StartDate)) WHEN 1 THEN 7 WHEN 2 THEN 1 WHEN 3 THEN 2 WHEN 4 THEN 3 WHEN 5 THEN 4 WHEN 6 THEN 5 WHEN 7 THEN 6 END as FiscalWeekDay, CASE WHEN FiscalWeek%13 BETWEEN 1 AND 5 THEN ((FiscalQuarter-1) * 4) + 1 -(FiscalQuarter-1) WHEN FiscalWeek%13 BETWEEN 6 and 9 THEN ((FiscalQuarter-1) * 4) + 2 -(FiscalQuarter-1) WHEN FiscalWeek%13 > 9 THEN ((FiscalQuarter-1) * 4) + 3 -(FiscalQuarter-1) WHEN FiscalWeek%13 = 0 THEN ((FiscalQuarter-1) * 4) + 3 -(FiscalQuarter-1) END As FiscalPeriod, FiscalWeek, FiscalQuarter FROM Fwq )SELECT FP.*, Case WHEN (DATEPART(MM,FiscalDate) > FiscalPeriod) and (FiscalPeriod = 12) THEN DATEPART(YYYY, FiscalDate) + 1 ELSE DATEPART(YYYY, FiscalDate) END AS FiscalYearFROM FP[/code]This can then be dropped into an inline function, moving the @StartDate declaration into args for the function. That gives you an easy way to rebuild your table, or even just use the function as the source for ad-hoc queries when the Financial guys ask for some obscure data as then tend to do. :-DWhat I find really great about your code though is that a simple adjustment of the Case statement for the Fiscal Period allows this to also handle the other common ISO Calendars (the 454 and 445). This make for a very flexible piece of code, with very little editing.Again, thanks for the great article, I'm going to replace some of what I have been using with this, because it greatly simplifies Fiscal Calendar creation.Micah RitchieTue, 03 Aug 2010 12:54:48 GMTMicah Ritchiehttp://www.sqlservercentral.com/Forums/Topic961796-2755-1.aspx[quote][b]jim.jaggers1 (8/3/2010)[/b][hr]And, a question on type casting. I notice you use explicit type casting with Cast(Ceiling(Cast(YearDay as real)/Cast(7 as real))as int)and similar functions. I think the same result would be accomplished with the implicit typecasting of ceiling(YearDay/7.0)and be a little easier to read.Of course I am not a "real" programmer as I write code to do my job rather than as my job (though I'm considering a career change from Data Analysis to DB Developer). So it may be that real programmers find it better to explicitly control the data type rather than trusting to implicit data typing "rules".[/quote]I tend to prefer it because it eliminates a point of failure. Also if someone else uses the code in the future and I'm not explaining it to them and I haven't commented that particular piece (I should, I know), they won't be tempted to get rid of the "pointless" .0 and end up with errors that seem inexplicable.Tue, 03 Aug 2010 09:18:54 GMTStefan Krzywickihttp://www.sqlservercentral.com/Forums/Topic961796-2755-1.aspxAnd, a question on type casting. I notice you use explicit type casting with Cast(Ceiling(Cast(YearDay as real)/Cast(7 as real))as int)and similar functions. I think the same result would be accomplished with the implicit typecasting of ceiling(YearDay/7.0)and be a little easier to read.Of course I am not a "real" programmer as I write code to do my job rather than as my job (though I'm considering a career change from Data Analysis to DB Developer). So it may be that real programmers find it better to explicitly control the data type rather than trusting to implicit data typing "rules".Tue, 03 Aug 2010 09:14:39 GMTSQL Curioushttp://www.sqlservercentral.com/Forums/Topic961796-2755-1.aspx[quote][b]jim.jaggers1 (8/3/2010)[/b][hr]Stefan, Great article. I always enjoy seeing the set based alternatives to the iterative approach. And, I love the math. I think I have a more intuitive approach to the period calculation you provided as:CASE WHEN FiscWeek%13 BETWEEN 1 and 5 THEN ((FiscQuarter - 1) * 4) + 1 - (FiscQuarter - 1) WHEN FiscWeek%13 BETWEEN 5 and 9 THEN ((FiscQuarter - 1) * 4) + 2 - (FiscQuarter - 1) WHEN FiscWeek%13 > 9 THEN ((FiscQuarter - 1) * 4) + 3 - (FiscQuarter - 1) WHEN FiscWeek%13 = 0 THEN ((FiscQuarter - 1) * 4) + 3 - (FiscQuarter - 1) END as Period If you consider that each quarter contains 3 periods - numbered 1, 2, and 3 - and the purpose of your math is to shift the numbering scheme from quarter to year, then you will want a number scheme like the followingQtr-Pd # in Qtr Yr-Pd # in Q1 Yr-Pd # in Q2 Yr-Pd # in Q3 Yr-Pd # in Q41 1 4 7 102 2 5 8 113 3 6 9 12 Then the pattern emerges that each Yr-Pd # is the (Qtr-Pd #) + (wks in Pd)*(Pd # - 1) orCASE WHEN FiscWeek%13 BETWEEN 1 and 5 THEN 1+3*(FiscQuarter - 1) WHEN FiscWeek %13 BETWEEN 6 and 9 THEN 2+3*(FiscQuarter - 1) WHEN FiscWeek %13 > 9 THEN 3+3*(FiscQuarter - 1) WHEN FiscWeek %13 = 0 THEN 3+3*(FiscQuarter - 1)END Which is algebraically equivalent to your math; but, I think, more intuitive logic.[/quote]Nice, I'll give it a try. Thanks!Tue, 03 Aug 2010 09:09:31 GMTStefan Krzywickihttp://www.sqlservercentral.com/Forums/Topic961796-2755-1.aspxStefan, Great article. I always enjoy seeing the set based alternatives to the iterative approach. And, I love the math. I think I have a more intuitive approach to the period calculation you provided as:CASE WHEN FiscWeek%13 BETWEEN 1 and 5 THEN ((FiscQuarter - 1) * 4) + 1 - (FiscQuarter - 1) WHEN FiscWeek%13 BETWEEN 5 and 9 THEN ((FiscQuarter - 1) * 4) + 2 - (FiscQuarter - 1) WHEN FiscWeek%13 > 9 THEN ((FiscQuarter - 1) * 4) + 3 - (FiscQuarter - 1) WHEN FiscWeek%13 = 0 THEN ((FiscQuarter - 1) * 4) + 3 - (FiscQuarter - 1) END as Period If you consider that each quarter contains 3 periods - numbered 1, 2, and 3 - and the purpose of your math is to shift the numbering scheme from quarter to year, then you will want a number scheme like the followingQtr-Pd # in Qtr Yr-Pd # in Q1 Yr-Pd # in Q2 Yr-Pd # in Q3 Yr-Pd # in Q41 1 4 7 102 2 5 8 113 3 6 9 12 Then the pattern emerges that each Yr-Pd # is the (Qtr-Pd #) + (wks in Pd)*(Pd # - 1) orCASE WHEN FiscWeek%13 BETWEEN 1 and 5 THEN 1+3*(FiscQuarter - 1) WHEN FiscWeek %13 BETWEEN 6 and 9 THEN 2+3*(FiscQuarter - 1) WHEN FiscWeek %13 > 9 THEN 3+3*(FiscQuarter - 1) WHEN FiscWeek %13 = 0 THEN 3+3*(FiscQuarter - 1)END Which is algebraically equivalent to your math; but, I think, more intuitive logic.Tue, 03 Aug 2010 09:04:19 GMTSQL Curioushttp://www.sqlservercentral.com/Forums/Topic961796-2755-1.aspxThanks Steve.Tue, 03 Aug 2010 07:49:45 GMTStefan Krzywickihttp://www.sqlservercentral.com/Forums/Topic961796-2755-1.aspxStefan's updates have been published for the article.Tue, 03 Aug 2010 07:41:24 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic961796-2755-1.aspx[quote][b]Smendle (8/2/2010)[/b][hr][quote]The real answer here is that it doesn't really matter if a Tally table contains 10,000 rows or a million rows. It it's used properly and if it has the correct clustered index (an omission in this article which needs to be fixed), it's just not going to matter how big a Tally table is. The only reason why I keep mine small (11k rows) is because I almost never need something larger and it always fits in even the smallest cache on most machines. Any smaller than 8k and you have to check that VARCHAR splits (and the like) won't overrun the Tally table.So far as the word "inversly" goes here, I'm not sure what you mean by the way you used the word. --Jeff Moden[/quote]Well I spelled inversly wrong I meant inversely, which is to say that given 2 seperate distinct objects (for lack of a better description) I could create a tally table for either object in his case tally table the dates or tally table the Y W D values and then create a query against whichever tally table was created.[/quote]Heh... nah... wasn't even concerned about the misspelling. But now I understand your statement...You gain nothing by having a Tally Table smaller than 8K. In 2k5, if I need something larger than my standard Tally Table of 11k rows (more than 30 years worth of days), then I'll use a cross join on the Tally Table or some "Itzek" cascading CTE's to make one on the fly. Some folks just create a Tally Table with a million rows (no real performance disadvantage for proper code) and call it a day. There IS a performance DISADVANTAGE of combining a Tally Table with date information because it decreases the row density of the Tally Table.Tue, 03 Aug 2010 06:50:47 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic961796-2755-1.aspx[quote][b]Smendle (8/2/2010)[/b][hr][quote]The real answer here is that it doesn't really matter if a Tally table contains 10,000 rows or a million rows. It it's used properly and if it has the correct clustered index (an omission in this article which needs to be fixed), it's just not going to matter how big a Tally table is. The only reason why I keep mine small (11k rows) is because I almost never need something larger and it always fits in even the smallest cache on most machines. Any smaller than 8k and you have to check that VARCHAR splits (and the like) won't overrun the Tally table.So far as the word "inversly" goes here, I'm not sure what you mean by the way you used the word. --Jeff Moden[/quote]Well I spelled inversly wrong I meant inversely, which is to say that given 2 seperate distinct objects (for lack of a better description) I could create a tally table for either object in his case tally table the dates or tally table the Y W D values and then create a query against whichever tally table was created.[/quote]Well, the Tally Table itself is just a single column int table of consecutive integers. I used it to create the Fiscal Dates table. You could use it to create either of those date tables, but it itself is just a numbers column.Mon, 02 Aug 2010 17:32:44 GMTStefan Krzywickihttp://www.sqlservercentral.com/Forums/Topic961796-2755-1.aspx[quote]The real answer here is that it doesn't really matter if a Tally table contains 10,000 rows or a million rows. It it's used properly and if it has the correct clustered index (an omission in this article which needs to be fixed), it's just not going to matter how big a Tally table is. The only reason why I keep mine small (11k rows) is because I almost never need something larger and it always fits in even the smallest cache on most machines. Any smaller than 8k and you have to check that VARCHAR splits (and the like) won't overrun the Tally table.So far as the word "inversly" goes here, I'm not sure what you mean by the way you used the word. --Jeff Moden[/quote]Well I spelled inversly wrong I meant inversely, which is to say that given 2 seperate distinct objects (for lack of a better description) I could create a tally table for either object in his case tally table the dates or tally table the Y W D values and then create a query against whichever tally table was created.Mon, 02 Aug 2010 17:26:07 GMTSmendlehttp://www.sqlservercentral.com/Forums/Topic961796-2755-1.aspx[quote][b]Jeff Moden (8/2/2010)[/b][hr][quote][b]Stefan Krzywicki (8/2/2010)[/b][hr][quote][b]Smendle (8/2/2010)[/b][hr]Notice your taking 'Fisc' information and from the tally table determining the Y W D information. But what if your tally table was just Y W D information, since that data never changes, you could even enter in logic to determine if its feb 29 a lot easier (leap year), and pass a date or date range into a UDF and return the Y W D information for that passed date value?[/quote]Then it wouldn't be a "tally" table, it'd be a calendar table, if I correctly understand what you mean. And if it was just a table with YWD information, you really wouldn't need a UDF, you'd just write a query or subquery against it.[quote]just a thought on tally tables. I mean your performance numbers are certainly very acceptable(less than a second) and I have no argument NOT to use your example to generate a tally table to run against logic that you have given, however, having said that, why wouldnt want to try and keep a tally table as small as possible for reference and performance issues. (performance when your in the millions of rows)[/quote]You certainly can. I want the tally table to be ready to use against anything that might come up so I keep it at around 20,000 rows. That's not really a very large table for SQL Server, especially since it is 1 int column. If you want to make the tally table only 1000 or even 100 records and grow it when you need to, as long as it works for your needs that's fine. I'd suggest reading the article on Tally Tables that I linked to in my article and reading the discussion pages that go with it. It should answer all the questions you're having.[quote]Also would you agree that any criterion you use against a tally table works inversly?Using your example (Tally)Date against fiscaldateor vice versa[/quote]I'm not entirely sure what you mean, but I suppose it could.[/quote]The real answer here is that it doesn't really matter if a Tally table contains 10,000 rows or a million rows. It it's used properly and if it has the correct clustered index (an omission in this article which needs to be fixed), it's just not going to matter how big a Tally table is. The only reason why I keep mine small (11k rows) is because I almost never need something larger and it always fits in even the smallest cache on most machines. Any smaller than 8k and you have to check that VARCHAR splits (and the like) won't overrun the Tally table.So far as the word "inversly" goes here, I'm not sure what you mean by the way you used the word.[/quote]Oops, sorry about that! Thanks for the catch. I'll make the correction.Mon, 02 Aug 2010 17:14:25 GMTStefan Krzywickihttp://www.sqlservercentral.com/Forums/Topic961796-2755-1.aspx[quote][b]alen teplitsky (8/2/2010)[/b][hr]i always prefer CTE's and table variables. i've had a few issues with temp tables where the query ran but for some reason the temp table doesn't get deleted and the script fails the next day[/quote]I can see using a CTE if it's not recursive and I'd be real careful about performance in the area of table variables for anything more than about a hundred rows (just a rule of thumb) or so. Temp tables are automatically dropped when a session or connection ends. Are you pooling sessions or connections?Mon, 02 Aug 2010 17:07:17 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic961796-2755-1.aspx[quote][b]Stefan Krzywicki (8/2/2010)[/b][hr][quote][b]Smendle (8/2/2010)[/b][hr]Notice your taking 'Fisc' information and from the tally table determining the Y W D information. But what if your tally table was just Y W D information, since that data never changes, you could even enter in logic to determine if its feb 29 a lot easier (leap year), and pass a date or date range into a UDF and return the Y W D information for that passed date value?[/quote]Then it wouldn't be a "tally" table, it'd be a calendar table, if I correctly understand what you mean. And if it was just a table with YWD information, you really wouldn't need a UDF, you'd just write a query or subquery against it.[quote]just a thought on tally tables. I mean your performance numbers are certainly very acceptable(less than a second) and I have no argument NOT to use your example to generate a tally table to run against logic that you have given, however, having said that, why wouldnt want to try and keep a tally table as small as possible for reference and performance issues. (performance when your in the millions of rows)[/quote]You certainly can. I want the tally table to be ready to use against anything that might come up so I keep it at around 20,000 rows. That's not really a very large table for SQL Server, especially since it is 1 int column. If you want to make the tally table only 1000 or even 100 records and grow it when you need to, as long as it works for your needs that's fine. I'd suggest reading the article on Tally Tables that I linked to in my article and reading the discussion pages that go with it. It should answer all the questions you're having.[quote]Also would you agree that any criterion you use against a tally table works inversly?Using your example (Tally)Date against fiscaldateor vice versa[/quote]I'm not entirely sure what you mean, but I suppose it could.[/quote]The real answer here is that it doesn't really matter if a Tally table contains 10,000 rows or a million rows. It it's used properly and if it has the correct clustered index (an omission in this article which needs to be fixed), it's just not going to matter how big a Tally table is. The only reason why I keep mine small (11k rows) is because I almost never need something larger and it always fits in even the smallest cache on most machines. Any smaller than 8k and you have to check that VARCHAR splits (and the like) won't overrun the Tally table.So far as the word "inversly" goes here, I'm not sure what you mean by the way you used the word.Mon, 02 Aug 2010 17:03:14 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic961796-2755-1.aspx[quote][b]Carolyn Stern (8/2/2010)[/b][hr]Retail Calendar - Just Google It.There is an adjustment evey few years to get that extra day back.This calendar is very specific and is in use by virtually all retail companies of any size.[/quote]This is the first time I've run across this specific calendar arrangement. Frequently I've had to make other unusual period calendars for banks, financial institutions, manufacturing comapnies, payroll departments, etc... Thanks again for the information about this particular configuration being a standard. I look forward to researching it further.Mon, 02 Aug 2010 15:28:57 GMTStefan Krzywickihttp://www.sqlservercentral.com/Forums/Topic961796-2755-1.aspx[quote][b]Smendle (8/2/2010)[/b][hr]Notice your taking 'Fisc' information and from the tally table determining the Y W D information. But what if your tally table was just Y W D information, since that data never changes, you could even enter in logic to determine if its feb 29 a lot easier (leap year), and pass a date or date range into a UDF and return the Y W D information for that passed date value?[/quote]Then it wouldn't be a "tally" table, it'd be a calendar table, if I correctly understand what you mean. And if it was just a table with YWD information, you really wouldn't need a UDF, you'd just write a query or subquery against it.[quote]just a thought on tally tables. I mean your performance numbers are certainly very acceptable(less than a second) and I have no argument NOT to use your example to generate a tally table to run against logic that you have given, however, having said that, why wouldnt want to try and keep a tally table as small as possible for reference and performance issues. (performance when your in the millions of rows)[/quote]You certainly can. I want the tally table to be ready to use against anything that might come up so I keep it at around 20,000 rows. That's not really a very large table for SQL Server, especially since it is 1 int column. If you want to make the tally table only 1000 or even 100 records and grow it when you need to, as long as it works for your needs that's fine. I'd suggest reading the article on Tally Tables that I linked to in my article and reading the discussion pages that go with it. It should answer all the questions you're having.[quote]Also would you agree that any criterion you use against a tally table works inversly?Using your example (Tally)Date against fiscaldateor vice versa[/quote]I'm not entirely sure what you mean, but I suppose it could.Mon, 02 Aug 2010 15:26:43 GMTStefan Krzywickihttp://www.sqlservercentral.com/Forums/Topic961796-2755-1.aspx[quote][b]Carolyn Stern (8/2/2010)[/b][hr]Retail Calendar - Just Google It.There is an adjustment evey few years to get that extra day back.This calendar is very specific and is in use by virtually all retail companies of any size.[/quote]There is, huh? Interesting. I'll take a look. Thanks!Mon, 02 Aug 2010 15:18:39 GMTStefan Krzywickihttp://www.sqlservercentral.com/Forums/Topic961796-2755-1.aspxRetail Calendar - Just Google It.There is an adjustment evey few years to get that extra day back.This calendar is very specific and is in use by virtually all retail companies of any size.Mon, 02 Aug 2010 14:57:57 GMTCarolyn Sternhttp://www.sqlservercentral.com/Forums/Topic961796-2755-1.aspxNotice your taking 'Fisc' information and from the tally table determining the Y W D information. But what if your tally table was just Y W D information, since that data never changes, you could even enter in logic to determine if its feb 29 a lot easier (leap year), and pass a date or date range into a UDF and return the Y W D information for that passed date value?just a thought on tally tables. I mean your performance numbers are certainly very acceptable(less than a second) and I have no argument NOT to use your example to generate a tally table to run against logic that you have given, however, having said that, why wouldnt want to try and keep a tally table as small as possible for reference and performance issues. (performance when your in the millions of rows)Also would you agree that any criterion you use against a tally table works inversly?Using your example (Tally)Date against fiscaldateor vice versaMon, 02 Aug 2010 14:23:05 GMTSmendlehttp://www.sqlservercentral.com/Forums/Topic961796-2755-1.aspx[quote][b]alen teplitsky (8/2/2010)[/b][hr]i always prefer CTE's and table variables. i've had a few issues with temp tables where the query ran but for some reason the temp table doesn't get deleted and the script fails the next day[/quote]I don't think I've ever run into that problem. Hope I never do. Still, with this you could use 2 table variables and not have to use a CTE,I usually like to use temp tables when developing a procedure that's going to use either a temp table or a table variable because I don't have to rerun the entire script every time I change or add something. Since the temp table stays in memory in that instance, I can just keep running new statements in that window against it until I drop it. If I want to when I'm done, I can switch it to a table variable, test it one more time and I'm good to go.Mon, 02 Aug 2010 13:13:34 GMTStefan Krzywickihttp://www.sqlservercentral.com/Forums/Topic961796-2755-1.aspxi always prefer CTE's and table variables. i've had a few issues with temp tables where the query ran but for some reason the temp table doesn't get deleted and the script fails the next dayMon, 02 Aug 2010 13:07:07 GMTalen teplitskyhttp://www.sqlservercentral.com/Forums/Topic961796-2755-1.aspx[quote][b]Everett T (8/2/2010)[/b][hr]A CTE could also be used to generate a tally table in memory. This can come in handy if you don't have access to create a table or don't wish to create a table for short-term use.[code="sql"]WITH TallyAS( SELECT TOP 20000 ROW_NUMBER() OVER (ORDER BY SC1.name) AS N FROM master.dbo.syscolumns AS SC1 CROSS JOIN master.dbo.syscolumns AS SC2)[/code][/quote]Sure, or you could just use a temp table or table variable. Still no need to use a CTE to do it either. Heck, if you really wanted to leave no long-term footprint, just make both your tally table and your date table temp tables and drop them when you're done. Or make them both table variables, which is harder to use if you want to keep adding bits and testing them as you add them, but still works.Mon, 02 Aug 2010 13:03:15 GMTStefan Krzywickihttp://www.sqlservercentral.com/Forums/Topic961796-2755-1.aspxA CTE could also be used to generate a tally table in memory. This can come in handy if you don't have access to create a table or wish to create a table for short-term use.[code="sql"]WITH TallyAS( SELECT TOP 20000 ROW_NUMBER() OVER (ORDER BY SC1.name) AS N FROM master.dbo.syscolumns AS SC1 CROSS JOIN master.dbo.syscolumns AS SC2)[/code]Mon, 02 Aug 2010 12:59:39 GMTEverett Travishttp://www.sqlservercentral.com/Forums/Topic961796-2755-1.aspx[quote][b]Stefan Krzywicki (8/2/2010)[/b][hr][quote][b]alen teplitsky (8/2/2010)[/b][hr]wish i just had a tally tablebought my wife an iPhone 4 just now and needed to know which days were week 30 of 2010 since that when her phone was manufactured. rumor is that the latest iphones have a matte coating on the antennal to avoid the death grip issueApple uses the week of manufacture as part of the serial number in all of their products[/quote]I didn't know that about Apple.Has she been having a problem with the phone? My fiance and I have iPhone 4s and we haven't had the problem. Plus, they are giving out free cases through an app for the phone.[/quote]new iphone 4 is still in the box. just bought it from the NYC Upper West Side store 90 minutes ago. called and they had a lot of stock.the death grip issue is only if you're in a low signal level zone. wife has had a 3G since 2/2009 and i had a 3GS since June of 2009. i knew the 5 bars most of the time was a bunch of BS and something apple put in the software. i also had something similar to the death grip with my 3GS a few times when using it on wifi. while SJ was giving his we're as bad as everyone else speech i replicated the issue on my Blackberry Curve on one half of the room i was in. the other half the room has a better signaland in my opinion apple knew about it before the release. i though it was strange that they were hyping the bumpers as well and i thought that touching the antenna may cause issues. or maybe it would increase the signal since it would allow the user to be the antenna as well.a lot of the hysteria is just internet hype. there are PR/marketing firms who hire people to do nothing but post on forums and youtube and whatever. apple is not perfect but i'm 99% sure one of their competitors created the hysteria via one of these new advertising firmsfor the serial number i've known about it for a while. when the 27" iMac's came out last year with the yellow screens everyone on MacRumors was tracking the week of manufacture to see if the issue was fixed. few weeks ago Gizmodo ran an article that Genius Bar iPhone 4 replacements were different than launch ones. which is why i wish i had a tally table and instead had to google "week 30 2010" to check the datesMon, 02 Aug 2010 12:05:19 GMTalen teplitskyhttp://www.sqlservercentral.com/Forums/Topic961796-2755-1.aspxI have also used Jeff's code as a jumping off point to do date tally tables. Julian date conversions were what started me down this road. I usually use the days int value as the pk_for the table with select cast(getdate() as int)Language conversions and other wierd stuff can then be stored in the date table that I usually put in master.John.Mon, 02 Aug 2010 11:55:56 GMTjohn.campbell-1020429http://www.sqlservercentral.com/Forums/Topic961796-2755-1.aspx[quote][b]alen teplitsky (8/2/2010)[/b][hr]wish i just had a tally tablebought my wife an iPhone 4 just now and needed to know which days were week 30 of 2010 since that when her phone was manufactured. rumor is that the latest iphones have a matte coating on the antennal to avoid the death grip issueApple uses the week of manufacture as part of the serial number in all of their products[/quote]I didn't know that about Apple.Has she been having a problem with the phone? My fiance and I have iPhone 4s and we haven't had the problem. Plus, they are giving out free cases through an app for the phone.Mon, 02 Aug 2010 11:54:08 GMTStefan Krzywickihttp://www.sqlservercentral.com/Forums/Topic961796-2755-1.aspxwish i just had a tally tablebought my wife an iPhone 4 just now and needed to know which days were week 30 of 2010 since that when her phone was manufactured. rumor is that the latest iphones have a matte coating on the antennal to avoid the death grip issueApple uses the week of manufacture as part of the serial number in all of their productsMon, 02 Aug 2010 11:44:59 GMTalen teplitskyhttp://www.sqlservercentral.com/Forums/Topic961796-2755-1.aspx[quote][b]Carolyn Stern (8/2/2010)[/b][hr]Hi - Actually this isn't a very specific use of a tally table for one customer.This is called the "Retail Calendar" and is used by pretty much every retail business including the one I work for. We have retail stores and we have a large factory.You can google it and look it up.I don't know if this person is on the retail calendar or if he just has a weird fiscal set up.I have been using a similar tally table for several years. One of the things I include is whether that particular date is a "working factory day". That doesn't mean to exclude sat/sun/holiday. As we approach the holidays we often have sat/sun where the factory works and ships product. So after the fact we add a Y if the factory worked that day. [/quote]Interesting, this is for a retail company, but I don't know if they are on a standard "retail calendar" or if they've made up their own that is just very similar. Does the standard one have the "correction" of an additional week that I entered?Thanks for the information!Mon, 02 Aug 2010 10:30:01 GMTStefan Krzywickihttp://www.sqlservercentral.com/Forums/Topic961796-2755-1.aspxHi - Actually this isn't a very specific use of a tally table for one customer.This is called the "Retail Calendar" and is used by pretty much every retail business including the one I work for. We have retail stores and we have a large factory.You can google it and look it up.I don't know if this person is on the retail calendar or if he just has a weird fiscal set up.I have been using a similar tally table for several years. One of the things I include is whether that particular date is a "working factory day". That doesn't mean to exclude sat/sun/holiday. As we approach the holidays we often have sat/sun where the factory works and ships product. So after the fact we add a Y if the factory worked that day. Mon, 02 Aug 2010 10:25:54 GMTCarolyn Sternhttp://www.sqlservercentral.com/Forums/Topic961796-2755-1.aspxI've always had to build a separate table and maintain it. Something like[code="other"]Period Date1 1/1/20001 1/2/2000...1 1/28/20002 1/29/2000...[/code]Mon, 02 Aug 2010 09:07:12 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic961796-2755-1.aspxthxi've read tally table articles here for the last few years and never thought i had a use for them. last 6 months i've been building a performance data warehouse but haven't done too much in getting use out of the data. going to build a tally table into it just to divide the data into weeks, months, etc to see if we can find trends later onMon, 02 Aug 2010 09:00:59 GMTalen teplitskyhttp://www.sqlservercentral.com/Forums/Topic961796-2755-1.aspx[quote][b]Steve Jones - Editor (8/2/2010)[/b][hr]I thought this was interesting, and I've dealt with this in a few places. If you don't have to work with the financial system, you might not encounter this. It seems CPAs always want things to end on a Friday, or some off period, not just letting things end on whatever day the month ends.[/quote]Thanks. As a consultant, I've seen this kind of table at a number of places and they're all different. Worse, most have the dates either updated by hand or in Excel and then imported.I likely should have pointed out in the article one of the reasons I listed this as a Tally Table article instead of just a Fiscal Period article. When I dealt with this kind of think before Tally Tables, it always came down to cursors and there's a big change in how you think about the problem with a Tally Table. With a cursor, you think about looping through the data and start setting up variables for incrementing the differenct results you'll need. Then you set your maximum values for the various types: quarters, periods, etc... Not only does it run slower, it becomes quite a bit to keep track of and it isn't easy to change when the inevitable requests come in to tweak it a bit.With the tally table, the variables go away and you think about math. Create a formula for each column and go! When the change requests come in, you either tweak the formula or, if it is a one-off, you do as I did in the article, change the start points and everything zooms along.The switch in thinking probably saves me more time than the improvement in run speed. And now I have code I can bring with me from assignment to assignment that's actually worth bringing.Mon, 02 Aug 2010 08:49:06 GMTStefan Krzywickihttp://www.sqlservercentral.com/Forums/Topic961796-2755-1.aspxI thought this was interesting, and I've dealt with this in a few places. If you don't have to work with the financial system, you might not encounter this. It seems CPAs always want things to end on a Friday, or some off period, not just letting things end on whatever day the month ends.Mon, 02 Aug 2010 08:29:11 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic961796-2755-1.aspx[quote][b]Jeff Moden (8/2/2010)[/b][hr][quote][b]Stefan Krzywicki (8/2/2010)[/b][hr]I do appreciate the suggestion, but I'm going to let it stand. I can't count the number of times I've read code or a coding technique and thought I'd never use it and later found how useful it was.[/quote]I have a great appreciation for that and my hat is off to you for making such a commitment to others. Well done!Like I said, I look forward to Part 2... even the original one. :-)[/quote]Well, it'll have to be the original once since it is running tomorrow : -)Mon, 02 Aug 2010 07:22:44 GMTStefan Krzywickihttp://www.sqlservercentral.com/Forums/Topic961796-2755-1.aspx[quote][b]Stefan Krzywicki (8/2/2010)[/b][hr]I do appreciate the suggestion, but I'm going to let it stand. I can't count the number of times I've read code or a coding technique and thought I'd never use it and later found how useful it was.[/quote]I have a great appreciation for that and my hat is off to you for making such a commitment to others. Well done!Like I said, I look forward to Part 2... even the original one. :-)Mon, 02 Aug 2010 07:12:56 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic961796-2755-1.aspx[quote][b]Jeff Moden (8/1/2010)[/b][hr]It IS interesting to see what folks do with the Tally Table or the "pseudo cursor" methods that are used to build it.Nice article and lot's of code to play with. Thanks. Now, I have to go back and play with the code along the way. I'm sometimes amazed at what some accountants/companies will go through in the name of a fiscal year. I have to admit that your requirements are fairly odd although quite easy to understand.Thanks for all the kudos on the Tally Table article, Stefan. I'm glad I could help and it's good to see so many folks carrying such a useful technique forward. Heh... not sure I deserve all that, though... lot's of folks were using a "numbers" table a very long time before my article. :-PLooking forward to Part 2...[/quote]And before I forget, thanks for the compliments above.Mon, 02 Aug 2010 06:44:52 GMTStefan Krzywickihttp://www.sqlservercentral.com/Forums/Topic961796-2755-1.aspx[quote][b]Jeff Moden (8/2/2010)[/b][hr]Heh... pretty low marks for the article so far, Stefan. Although I certainly appreciate what you tried to do both in your code and for the Tally Table, I think that people read "Tally Table Uses" as an article title and expect to see a plethora of code snippets for different purpose uses of the Tally Table across a wide spectrum. Instead, they find a fairly peculiar calendar table with a very limited use (one company) which also requires manual intervention every couple of years that just happens to use a Tally table as a driver. Again, I appreciate both attempts but the public apparently has a different take on the article. I'm telling you this because you may want to reconsider what you publish as Part 2. Give it a shot...[/quote]If they just don't find it useful, I don't mind so much. There are some good techniques (I think) in there for any kind of non-standard calendar and it only requires manual intervention if the calendar is changed by the business people. I put that part in to show how easy it is to make that kind of manual intervention. I thought the subtitle was pretty clear that article I only handles fiscal year calculations.Where I'd worry is if people started tearing the code apart, telling me I got things wrong, then I'd have to go back and reexamine things and fix things. Even then, I wouldn't so much "mind" as be chagrined. I do appreciate the suggestion, but I'm going to let it stand. I can't count the number of times I've read code or a coding technique and thought I'd never use it and later found how useful it was.Mon, 02 Aug 2010 06:44:14 GMTStefan Krzywickihttp://www.sqlservercentral.com/Forums/Topic961796-2755-1.aspx[quote][b]Jeff Moden (8/1/2010)[/b][hr]It IS interesting to see what folks do with the Tally Table or the "pseudo cursor" methods that are used to build it.Nice article and lot's of code to play with. Thanks. Now, I have to go back and play with the code along the way. I'm sometimes amazed at what some accountants/companies will go through in the name of a fiscal year. I have to admit that your requirements are fairly odd although quite easy to understand.Thanks for all the kudos on the Tally Table article, Stefan. I'm glad I could help and it's good to see so many folks carrying such a useful technique forward. Heh... not sure I deserve all that, though... lot's of folks were using a "numbers" table a very long time before my article. :-PLooking forward to Part 2...[/quote]Perhaps they were using it before you, but you're where I learned it so you get the thanks. : -)Mon, 02 Aug 2010 06:37:36 GMTStefan Krzywickihttp://www.sqlservercentral.com/Forums/Topic961796-2755-1.aspxHeh... pretty low marks for the article so far, Stefan. Although I certainly appreciate what you tried to do both in your code and for the Tally Table, I think that people read "Tally Table Uses" as an article title and expect to see a plethora of code snippets for different purpose uses of the Tally Table across a wide spectrum. Instead, they find a fairly peculiar calendar table with a very limited use (one company) which also requires manual intervention every couple of years that just happens to use a Tally table as a driver. Again, I appreciate both attempts but the public apparently has a different take on the article. I'm telling you this because you may want to reconsider what you publish as Part 2. Give it a shot...Mon, 02 Aug 2010 06:17:09 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic961796-2755-1.aspxIt IS interesting to see what folks do with the Tally Table or the "pseudo cursor" methods that are used to build it.Nice article and lot's of code to play with. Thanks. Now, I have to go back and play with the code along the way. I'm sometimes amazed at what some accountants/companies will go through in the name of a fiscal year. I have to admit that your requirements are fairly odd although quite easy to understand.Thanks for all the kudos on the Tally Table article, Stefan. I'm glad I could help and it's good to see so many folks carrying such a useful technique forward. Heh... not sure I deserve all that, though... lot's of folks were using a "numbers" table a very long time before my article. :-PLooking forward to Part 2...Sun, 01 Aug 2010 21:56:25 GMTJeff Moden