December 2009

You are currently browsing the monthly archive for December 2009.

Since a while now, about two months and a bit to be exact, I’ve been publishing some of my articles on Experts Exchange.  It all started when their Content Coordinator found my blog and convinced me to start doing that, thanks for that Jenn (aka jennhp)!

In total I have now six articles published and four of them are EE Approved.  What does that mean?

EE-Approved is a designation awarded by the Page Editors to show that your Article is of superior quality: either it discusses something new, or provides a unique and original solution to a particularly interesting problem. If your article is selected as EE-Approved, you will receive 1,500 points.

When I published my first article back in September, getting an article EE Approved earned me 1,500 additional points on top of the 500 for getting it published.  This was recently changed to 4,000.  Which I really appreciate and I think was needed to make this a success.  When comparing the effort that goes into writing a good article with the effort to decently answer a 500 points question I think it is still not comparable.  A question can earn me 2,000 points (when the answer gets A-graded, most of my answers do) in just a couple of minutes – which doesn’t mean that all questions are solved this easily,  believe me.  But an article always takes several hours to write and publish.  Just to give you an idea: for my last article it took me one hour just to get it converted from my version in Live Writer to the tagged format expected by the “Write a new article” feature.  But then again, the minimum length requirements for an article is 300 words while my last article counted close to 2,500 words.

What should I conclude from the above paragraph?  That I should learn to make my articles shorter?  Or that Article Points and Expert Points received by answering questions are not comparable?  Well, probably both.  What I should learn is to split articles up when possible, but I will still keep mentioning the necessary details which in my opinion increases the quality indicator :-)

Anyway, it seems that I’ve been doing a good job because this is what the situation looked like on November 23, 2009.  Following screenshots were taken while filtering on Microsoft articles.

Five articles in Microsoft top 10

Not only did my articles control the Top 3 of most popular Microsoft articles,  they were also at position nine and ten, so in total I had five of my articles in the Top 10.

(The filter on Microsoft articles is highlighted in green btw.)

Highlighted on the left you can see one of my articles being featured.  This is a result of it being EE Approved.

As the next two screenshots show, it wasn’t the only one.

One of my EE Approved articles featured at Experts Exchange

And here’s number three:

My "Pie Chart Techniques" article feature at Experts Exchange

On that same page we can also see the Top 25 of Microsoft Contributors.  I was at position 7 with almost 15,000 points.  (Today I am at position 6, with about 20,000 points.)

Me at position 7 in the Top 25 of Microsoft article writers

So, why am I doing this?  Let’s see, I have several reasons:

  • I hope people will find them useful, which means I have been able to teach them something
  • It helps to improve my writing skills
  • It encourages me to explore details about certain topics that I wouldn’t need to explore otherwise
  • It brings me into contact with interesting people
  • I found out that writing can actually be a fun thing to do!
  • Points == T-shirts :-)

Right, so I started this post by thanking someone, let’s finish it the way it started.

I’d like to thank mark_wills for his enthusiasm and support.  He’s the Page Editor that has reviewed my articles and selected several for the EE Approved status (in cooperation with mwvisa1 I believe).  Just to make sure I haven’t missed anyone, I’d like to say thanks to all Page Editors that have approved my articles!

If you’d like to have a look at the articles, they are mentioned in my profile at Experts Exchange.  While you’re at it, if you like them I wouldn’t mind if you clicked that little YES button :-)

Now if you’ll excuse me, I’ve got some articles to write.  (In an attempt to control the Top 5 – erm, yeah right – just kidding of course.)

Happy reading!



Tags: , ,

This post is meant partly as a “reminder to self” but also to show that, if you need to generate a list of dates, all you need are numbers.  Oh well, if that doesn’t make sense: read on!

One of the undocumented tables of SQL Server is called spt_values and it is located in the master database.  This table serves as a lookup table for SQL Server itself, more precisely it’s used by several system stored procedures.

Being a lookup table, it doesn’t consist of many columns.  One of the most important columns though is called type.  This one defines the meaning of the record.  An interesting value for this column is “LNG”.  When filtering on “LNG” it returns the following:

List of languages with their LCID

I’m quite sure that SharePoint developers recognize these values stored in the number column.  That’s the locale identifier of the language mentioned in the name column.  Could be interesting in case you need a list of languages to be shown somewhere, in a report parameter for instance.

But anyway, these records are not the point of this blog post so let’s move on.  The title of this post says we’re going to generate some dates out of numbers.  So to get started, what we need are those numbers.  Guess what the spt_values table contains?  Indeed, when filtering the name column on “P”, what we get are numbers starting at zero and ending at 2047:

List of numbers retrieved from master..spt_values

And how can we turn these into dates?  Using some creativity it’s not that complicated.  Let’s say we want to generate a list of dates starting at the first of January of the current year up until today, could be interesting for YTD calculations.  Following query does just that:

select CAST(DATEADD(dd, -number, GETDATE()) as date) dt
from master..spt_values
where type = 'P' and number < DATEPART(dy, GETDATE())

As you can see, several datetime-related functions are being used here.  The easiest one is GETDATE() which returns the current system timestamp of the database server.

Then we’ve got DATEPART() in combination with the “dy” parameter.  This returns the numeric value for the day of the year that the date parameter represents.  The following query returns 336 when ran today (December 2, 2009):

select DATEPART(dy, GETDATE())

(For readability purposes I will not repeat “when ran today” each time, the remainder of the post assumes everything is being executed on the aforementioned date.)

These two functions are being used in the WHERE clause.   So what the WHERE clause says is that we want all numbers smaller than 336.

Okay, so let’s move on to the SELECT.  It uses the DATEADD() function to subtract the retrieved number values from the current system date.  How does that work?  In combination with the “dd” parameter it tells the function what we’re working on are the days, and the minus sign in front of the number field ensures that days are being subtracted instead of added.

And to finish off, the value returned by DATEADD() is being converted to the date type using the CAST() function.  This removes the time value of the datetime type which is good because all we needed are dates.

This is what the query returns:

YTD date values

As you can see, we get 336 records which matches exactly with the values returned by DATEPART(dy, GETDATE()).  (BTW: the number field starts at zero which is why we get 336 instead of 335 records here)

For more advanced usage of a list of numbers I’d like to point you the following article by colleague Experts Exchange Expert Mark Wills: Fun with MS SQL spt_values for delimited strings and virtual calendars

Have fun!



Tags: ,

© 2008-2019 BI: Beer Intelligence? All Rights Reserved