Heute hatte ich eine Nachricht von Avaaz in der Inbox. Es geht um den Klimagipfel, der jetzt in Lima (Peru) stattfindet. Ich war überrascht, daß bei diesem Gipfel die Senkung der Treibhaus-Emissionen auf 0 (ja wirklich: Null, Zero, Nada, Niente) auf der Agenda steht. Nun gut, wenn es auf der Agenda steht, muß das noch lange nicht heißen,
This is a follow-up to my article How to generate a date sequence in T-SQL.
In my experience, it is frequently required to map data to points in time – display data on a periodic schedule, aggregate access log data by time intervals and so on. It goes without saying that this mapping should be handled as quick as possible. Of course performance matters.
In my previous post I discussed a method to generate these time points on-the-fly. Here I present a full solution for pre-generating a number sequence and generate a date sequence from it (which might be stored in a table as well).
If you ever had the pleasure to run queries over an access log I bet you had a desperate need for a date sequence to map query results to.
There are plenty of pages out there discussing the best way to create and populate number tables. After reading this great post on stack overflow I came up with a slight variation of one of the solutions mentioned there, like so:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
DECLARE @fromDate DATETIME = '2014-01-01' DECLARE @toDate DATETIME = '2014-09-30' SELECT datum = DATEADD(DAY, number - 1, @FromDate) FROM ( SELECT number = CAST(number AS INT) FROM ( SELECT number = ROW_NUMBER() OVER (ORDER BY v1.number) FROM master..spt_values AS v1 CROSS JOIN (SELECT number FROM master..spt_values) AS v2 ) AS vu WHERE number BETWEEN 1 AND DATEDIFF(DAY, @fromDate, DATEADD(DAY, 1, @toDate)) ) AS u |
This returns 273 records with dates from Jan, 1 2014 to Sep, 30 2014.
The advantages of this solution are:
It took me some time to grasp the idea about how to use npm and bower in Visual Studio node.js projects, given the fact that Nuget is such a great package manager for this environment. From an ASP.net perspective it would be preferable to use just a single tool for package management and load javascript libraries the same way we load .Net libraries. After all this is how you do it in the asp.net context – loading bootstrap, jquery etc via Nuget or using a preconfigured project template with all required javascript libraries, css files and so on.