November 7, 2011 at 9:41 pm
Please forgive me for my rbar ways. How should I recode this (preferably without a tally table unless I can get one approved by our dba's)
declare @MyDates table(d datetime not null)
declare @dtStart datetime, @dtEnd datetime, @dtThis datetime
set @dtStart = DATEADD(day, -60, getdate())
set @dtEnd = GETDATE()
set @dtThis = @dtStart
while @dtThis < @dtEnd
begin
insert into @MyDates(d) values(@dtThis)
set @dtThis = DATEADD(day, 1, @dtThis)
end
select d from @MyDates
.
November 7, 2011 at 10:24 pm
This may seem a little silly, kind of a tally-table-without-a-tally-table solution...
INSERT INTO @MyDates (d)
SELECT TOP 60 DATEADD(day, -1 * (ROW_NUMBER() OVER (ORDER BY name)), getdate())
FROM Master.dbo.SysColumns;
November 8, 2011 at 2:14 am
Assuming you're using SQL Server 2008 (due to the forum you posted in), you could do this: -
DECLARE @MyDates TABLE (d DATETIME NOT NULL)
INSERT INTO @MyDates (d)
SELECT DATEADD(day, N, getdate())
FROM (VALUES(-1),(-2),(-3),(-4),(-5),(-6),(-7),(-8),(-9),
(-10),(-11),(-12),(-13),(-14),(-15),(-16),(-17),(-18),(-19),
(-20),(-21),(-22),(-23),(-24),(-25),(-26),(-27),(-28),(-29),
(-30),(-31),(-32),(-33),(-34),(-35),(-36),(-37),(-38),(-39),
(-40),(-41),(-42),(-43),(-44),(-45),(-46),(-47),(-48),(-49),
(-50),(-51),(-52),(-53),(-54),(-55),(-56),(-57),(-58),(-59),
(-60)) a(N)
ORDER BY N ASC
SELECT d FROM @MyDates
This uses the exact same principle as goofbauer's solution, but without relying on syscolumns.
November 8, 2011 at 3:59 am
Another solution that is very similar to goofbauer's , but uses SQL server's numbers table and does not need row_number()
SELECT DATEADD(day, -1 * (number ), getdate()) as Last_Dates
FROM spt_values
where spt_values.type = 'P' and number > 0 and number <= 60
order by Last_Dates
November 8, 2011 at 4:48 am
Never mind - silly idea deleted.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 8, 2011 at 6:39 am
Create a temporary tally table and use it, no need for a permanent one.
The probability of survival is inversely proportional to the angle of arrival.
November 8, 2011 at 6:55 am
sturner (11/8/2011)
Create a temporary tally table and use it, no need for a permanent one.
Theres no point, SQL server has an in-built one with spt_values up to 2047 rows
November 8, 2011 at 7:00 am
steveb. (11/8/2011)
Theres no point, SQL server has an in-built one with spt_values up to 2047 rows
Oh I wouldn't say that - several of the previous solutions used an on-the-fly numbers table, which isn't so very different a concept.
The spt_values table in master is a tricky one. I do sometimes use it on here for demo code, but I would feel uneasy having a reference to it in production code. After all, it is undocumented, and might change significantly or go away altogether...which would be a bit of an inconvenience for user code that references it. Many will judge it an acceptable risk, I guess.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 8, 2011 at 7:04 am
Good point Paul, I did not realise it was un-documented. I jave just been using it without thinking about it too much... maybe time to re-think this..
November 8, 2011 at 7:50 am
I have a new favorite method, courtesy of Mike Powell at
http://stackoverflow.com/questions/58429/sql-set-based-range
No dependencies, backward compatible w/ 2005.
DECLARE @MyDates TABLE(d DATETIME NOT NULL);
WITH CTE AS (
SELECT1 AS n
UNION ALL
SELECTn + 1 AS n
FROM CTE
WHEREn + 1 <= 60)
INSERT INTO @MyDates (d)
SELECTDATEADD(DAY, -1 * n, getdate())
FROMCTE;
SELECT * FROM @MyDates;
November 8, 2011 at 8:00 am
goofbauer (11/8/2011)
I have a new favorite method, courtesy of Mike Powell athttp://stackoverflow.com/questions/58429/sql-set-based-range
You might like to read http://www.sqlservercentral.com/articles/T-SQL/74118/
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 8, 2011 at 8:03 am
goofbauer (11/8/2011)
I have a new favorite method, courtesy of Mike Powell athttp://stackoverflow.com/questions/58429/sql-set-based-range
No dependencies, backward compatible w/ 2005.
DECLARE @MyDates TABLE(d DATETIME NOT NULL);
WITH CTE AS (
SELECT1 AS n
UNION ALL
SELECTn + 1 AS n
FROM CTE
WHEREn + 1 <= 60)
INSERT INTO @MyDates (d)
SELECTDATEADD(DAY, -1 * n, getdate())
FROMCTE;
SELECT * FROM @MyDates;
BAD! 😛
SET NOCOUNT ON
IF object_id('tempdb..#MyDates') IS NOT NULL
BEGIN
DROP TABLE #MyDates
END
PRINT '========== CTE 1 =========='
SET STATISTICS TIME ON
;
WITH CTE AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 AS n
FROM CTE
WHERE n + 1 <= 500000)
SELECT DATEADD(DAY, n, '1900-01-01') AS d
INTO #MyDates
FROM CTE
OPTION (MAXRECURSION 0);
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
IF object_id('tempdb..#MyDates2') IS NOT NULL
BEGIN
DROP TABLE #MyDates2
END
PRINT '========== CTE 2 =========='
SET STATISTICS TIME ON
;WITH t1 AS (SELECT 1 N UNION ALL SELECT 1 N),
t2 AS (SELECT 1 N FROM t1 x, t1 y),
t3 AS (SELECT 1 N FROM t2 x, t2 y),
t4 AS (SELECT 1 N FROM t3 x, t3 y),
t5 AS (SELECT 1 N FROM t4 x, t4 y),
tally AS (SELECT TOP 500000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
FROM t5 x, t5 y)
SELECT DATEADD(DAY, n, '1900-01-01') AS d
INTO #MyDates2
FROM tally
WHERE n <= 500000
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
========== CTE 1 ==========
SQL Server Execution Times:
CPU time = 7188 ms, elapsed time = 7643 ms.
================================================================================
========== CTE 2 ==========
SQL Server Execution Times:
CPU time = 859 ms, elapsed time = 377 ms.
================================================================================
SQL Kiwi (11/8/2011)
goofbauer (11/8/2011)
I have a new favorite method, courtesy of Mike Powell athttp://stackoverflow.com/questions/58429/sql-set-based-range
You might like to read http://www.sqlservercentral.com/articles/T-SQL/74118/
Ah, you beat me to it 🙂
November 8, 2011 at 8:10 am
Cadavre (11/8/2011)
Ah, you beat me to it 🙂
To be fair, I had less to type. :laugh:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 8, 2011 at 8:17 am
Excellent. That's why you guys are paid more than me. I'm digging into the article. Thanks for the pointer.
November 8, 2011 at 8:36 am
I now have a new favorite. :blush: The Itzek-style cross join. Begs the question, why does MS place the emphasis they do on cursors and rCTEs in certification. A bit like giving loaded guns to children, don't you think?
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply