Systems Engineering and RDBMS

Getting all the alternate Mondays in the Year

Posted by decipherinfosys on December 30, 2008

Got an interesting question from one of our readers yesterday. They have project plans where they do releases on every other Monday – he wanted to know if there is a quick way in T-SQL to generate a list of all bi-weekly Mondays in a year.  He was referencing one of our earlier posts in which we had discussed how to generate dates between two date values and wanted to know if we can extend it to provide him an answer to this current question as well. It can definitely be done by making changes to that UDF but there is an even simpler way to achieve this.

We will make use of the CTE solution that we had discussed before in a couple of our posts in order to generate a number table.  Using the same solution, we can get the desired output by using this SQL:

with
N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
N4 (n) AS (SELECT 1 FROM N3 AS X, N3 AS Y),
N5 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n%10) – 1 FROM N4 AS X, N4 AS Y)
select DATEADD(dd, n * 14, ‘20090105’) as Monday_Date
from N5
WHERE DATEADD(dd, n * 14, ‘20090105’) <= ‘20091231’

Or, if you use a number table in your environment then the above query could be based directly at the top of it.  That is all what the above CTE does – prepare a number table based off which we can write of the query to get the desired result.

And in the above query, you can easily substitute the values for the dates as parameters and make it generic for any year.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 81 other followers

%d bloggers like this: