August 2, 2010 at 11:54 am
alen teplitsky (8/2/2010)
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 issue
Apple uses the week of manufacture as part of the serial number in all of their products
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.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
August 2, 2010 at 11:55 am
I 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.
August 2, 2010 at 12:05 pm
Stefan Krzywicki (8/2/2010)
alen teplitsky (8/2/2010)
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 issue
Apple uses the week of manufacture as part of the serial number in all of their products
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.
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 signal
and 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 firms
for 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 dates
August 2, 2010 at 12:59 pm
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 wish to create a table for short-term use.
WITH Tally
AS
(
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
)
August 2, 2010 at 1:03 pm
Everett T (8/2/2010)
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.
WITH Tally
AS
(
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
)
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.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
August 2, 2010 at 1:07 pm
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
August 2, 2010 at 1:13 pm
alen teplitsky (8/2/2010)
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
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.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
August 2, 2010 at 2:23 pm
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?
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 fiscaldate
or vice versa
August 2, 2010 at 2:57 pm
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.
August 2, 2010 at 3:18 pm
Carolyn Stern (8/2/2010)
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.
There is, huh? Interesting. I'll take a look. Thanks!
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
August 2, 2010 at 3:26 pm
Smendle (8/2/2010)
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?
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.
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)
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.
Also would you agree that any criterion you use against a tally table works inversly?
Using your example
(Tally)Date against fiscaldate
or vice versa
I'm not entirely sure what you mean, but I suppose it could.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
August 2, 2010 at 3:28 pm
Carolyn Stern (8/2/2010)
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.
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.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
August 2, 2010 at 5:03 pm
Stefan Krzywicki (8/2/2010)
Smendle (8/2/2010)
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?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.
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)
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.
Also would you agree that any criterion you use against a tally table works inversly?
Using your example
(Tally)Date against fiscaldate
or vice versa
I'm not entirely sure what you mean, but I suppose it could.
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
Change is inevitable... Change for the better is not.
August 2, 2010 at 5:07 pm
alen teplitsky (8/2/2010)
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
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?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 2, 2010 at 5:14 pm
Jeff Moden (8/2/2010)
Stefan Krzywicki (8/2/2010)
Smendle (8/2/2010)
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?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.
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)
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.
Also would you agree that any criterion you use against a tally table works inversly?
Using your example
(Tally)Date against fiscaldate
or vice versa
I'm not entirely sure what you mean, but I suppose it could.
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.
Oops, sorry about that! Thanks for the catch. I'll make the correction.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply