20 April 2016

T-SQL Generating af quick table with calendar dates

SELECT DATEADD(d, ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n, GETDATE())
FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) tens(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) hundreds(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) thousands(n)
ORDER BY 1

The fastest way to generate a base table with date entries...

Originally a method used in 2003, when we developed the statistics calculations for Skandias Fond performance dashboards.
Still available today at https://www.skandia.dk/funddatasheet?fundID=142

I keep forgetting the method, so now it is documented here.

No comments: