I want to write a t-sql query which will simply list all of the years from 2005 to present. But i want this automated so this list will still be current next year. I kno开发者_如何学Cw I could just hard code years with union. But how do I develop a t-sql query which will automate this?
I'd recommend a tally table approach - basically just create a table (called "Tally"/"Numbers" as you wish) with a single column and populate with e.g. numbers 0 to 1000 and put a CLUSTERED index on the column.
You can then use it like:
SELECT 2005 + Num AS Yr
FROM Tally
WHERE Num <= YEAR(GETDATE()) - 2005
Tally tables can come in very handy for all kinds of usages, hence my suggestion to use one as it could help other scenarios.
Many articles on tally tables out there, here's just one:
http://www.sqlservercentral.com/articles/T-SQL/62867/
You can do this using Common Table Expressions (CTEs) and it doesn't require you to create a temporary table:
DECLARE @TheYear as date
SET @TheYear = '1/1/2005'
;WITH DateIntervalsCTE AS
(
SELECT 0 i, @TheYear AS [Year]
UNION ALL
SELECT i + 1, DATEADD(year, i + 1, @TheYear )
FROM DateIntervalsCTE
WHERE YEAR(DATEADD(year, i, @TheYear )) <= YEAR(GETDATE())
)
SELECT [Year] FROM DateIntervalsCTE
I would try this way
WITH cte(YEAR)
AS (
SELECT 2005 YEAR
UNION ALL
SELECT CH.YEAR +1
FROM cte ch
WHERE YEAR <= YEAR(GETDATE())
)
SELECT * FROM CTE
result
YEAR
----
2005
2006
2007
2008
2009
2010
2011
2012
A crude but effective simple method
DECLARE @I INT
CREATE TABLE #years (TheYear datetime)
SET @I = 0
WHILE @I <= DATEDIFF(year, '1/1/2005', GETDATE())
BEGIN
INSERT INTO #years VALUES (DATEADD(year, @i, '1/1/2005'))
SET @I = @I + 1
END
SELECT YEAR(TheYear) FROM #years
DROP TABLE #years
Encapsulate that in a function with a table type return and you should be able to join against that in future queries.
精彩评论