October 22, 2008 at 4:24 pm
Answer, with out restricting the number of rows from the Tally table, SQL executes the dateadd function for every row in Tally until it errors.
😎
October 22, 2008 at 4:28 pm
that would be if i didn't have a where clause right...
this was my where clause:
DATEADD(m,t.N-1, @DateStart) <= @DateEnd
It gave me the correct rows but errored as well???
October 22, 2008 at 4:32 pm
There is no restriction there on the number of rows being returned from Tally. That is why you should add a second condition to the where clause, such as: and t1.N <= some value.
SQL doesn't know how many rows it needs to meet the existing criteria.
😎
October 22, 2008 at 4:40 pm
It sounds like your saying that my where clause was followed and SQL stopped making rows, but continued on with the query any way???
below instead of going for months, i went for days and no error... this where cluase work here but not for month... are you seeing why i am confused...
I like your persistance... TY
DECLARE @DateStart DATETIME
DECLARE @DateEnd DATETIME
DECLARE @MONTHS INT
SET @DateStart = '2008-08-25' -- 00:00:00.000
SET @DateEnd = '2013-08-25'
SELECT
DATEADD(D,t.N-1, @DateStart) AS NEXT_PAY_DATE
FROM dbo.Tally t
WHERE DATEADD(D,t.N-1, @DateStart) <= @DateEnd
October 22, 2008 at 4:46 pm
Remember when I said there were 97199 months between 1900-01-01 and 9999-12-31? Well, if you multiply 97,199 by 365 you get 35,477,635. Your Tally table (and mine) has 1,000,000 rows, so we never get close to the date 9999-12-31, so we don't get the error when you use dd in the dateadd function.
😎
October 22, 2008 at 4:51 pm
FYI...
select dateadd(dd, 1000000, '2008-10-22')
returns: 4746-09-19
😎
October 22, 2008 at 5:01 pm
Remember how queries work:
First, process the joins. In this case there are no joins - so we need to work on the full set of data which is 1,000,000 rows.
Next, process the where clause. As soon as the system tries to add a number to your start date that will generate a date greater than 9999-12-31, you generate the error.
You don't see this error because there are more than a million days between your start date and 9999-12-31 so the query can process all rows and eliminate any rows with a date greater than your end date.
By using months (which has a lot less than one million between your start date and 9999-12-31), the system hits the overflow error before it can eliminate the results.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
October 22, 2008 at 5:04 pm
I think i get...
I'm thinking my where clause (for months) was comparing
WHERE DATEADD(m,t.N-1, @DateStart) <= @DateEnd
would be equivilant to below
WHERE '2013-08-25 00:00:00.000' <= '2013-08-25 00:00:00.000'
thus when true it would stop... but your saying that it's comparing that date multiply stuff you did early...lol
October 22, 2008 at 5:13 pm
Not really - it is actually simpler than that. The where clause has to be evaluated for every row in the set. Since your table has 1,000,000 rows - it has to be evaluated for all 1,000,000 numbers.
Therefore, the calculation exceeds the allowable dates and causes an overflow error.
By adding a check for N 100000 you will still get the same error.
Example:
WHERE n>=100000
AND n < 130000
AND dateadd(m, n-1, @DateStart) <= @DateEnd
You are processing only 30,000 rows - but the value of N is going to cause an overflow.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
October 22, 2008 at 5:25 pm
I didn't know that...
so if i made a tally table with less rows (below that of the highest date SQL server has) I could avoid the error that way as well...
You guys are making me so smart.. 😀
Thanks...
October 22, 2008 at 5:31 pm
Well, sure - that is why I originally asked how many rows you had in your Tally table. However, even if you limit your Tally table to 11,000 rows (recommended for most processes) - you can cause the same problem.
Try the following: select dateadd(year, 11000, '2008-10-25');
You'll get the same exact error, for the same exact reason.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
October 22, 2008 at 5:40 pm
Which comes back to limiting the number of rows queried from Tally. For example:
and t1.N <= datediff(mm, @startdate, @enddate) -- you might want to add a + 1 here also.
-- change the mm to what ever datepart you are using in your query.
😎
October 22, 2008 at 7:53 pm
john.steinbeck (10/22/2008)
It sounds like your saying that my where clause was followed and SQL stopped making rows, but continued on with the query any way???
Your WHERE clause does not limit number of rows from Tally table in any way.
It limits value received as a result from calculation based on value N, and because SQL Server cannot predict results of that calculation it must do it for EVERY row in the table.
To limit number of rows you need to apply WHERE limitations to one of the values in the rows.
Just like Lynn did in his last post.
And remember the rule - never use functions on table columns in WHERE clause.
_____________
Code for TallyGenerator
October 23, 2008 at 6:08 am
I get it now, thank you all so much...
"And remember the rule - never use functions on table columns in WHERE clause. "
Is this like the CROSS APPLY???
October 26, 2008 at 10:28 am
Bottome line is, don't do calculations on t.N in the WHERE clause or all of the Tally table must be evaluated... as I saw previously suggested, this works the best for speed as well as preventing the error....
DECLARE @DateStart DATETIME
DECLARE @DateEnd DATETIME
SET @DateStart = '2008-08-25' -- 00:00:00.000
SET @DateEnd = '2013-08-25'
SELECT DATEADD(mm,t.N-1, @DateStart) AS NEXT_PAY_DATE
FROM dbo.Tally t
WHERE t.n <= DATEDIFF(mm,@DateStart,@DateEnd)-1
And, no... nothing really like a Cross Apply because there's no correlation. Just a simple "pseudo cursor".
--Jeff Moden
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply