Using a recursive common table expression to generate a date range

At one company that I worked for, I inherited a bunch of reporting code written in T-SQL. A lot of it was for reporting on monthly information. Sometimes where were inserts into temp tables for month data and the performance was terrible.

One consultant I hired saw some of the T-SQL code and suggested using a recursive common table expression (CTE) to generate dates. This was much faster than inserting into a temp table.

Here is an example of a CTE to generate the range of days for the current month. Obviously using a recursive algorithm can be dangerous. There is a limit on the number of recursive calls that can be made. You can add OPTION (MAXRECURSION N); to the select from a recursive CTE to limit the number of interations where N is a number between 0 and 32,767.

 
DECLARE @StartDate datetime
DECLARE @EndDate datetime
SET @StartDate = DATEADD(month, DATEDIFF(month,'20120101', CURRENT_TIMESTAMP), '20120101')
SET @EndDate = DATEADD(month, DATEDIFF(month,'20120131', CURRENT_TIMESTAMP), '20120131')

;WITH DateRange(Date) AS
    (
        SELECT
            @StartDate Date
        UNION ALL
        SELECT
            DATEADD(day, 1, Date) Date
        FROM
            DateRange
        WHERE
            Date < @EndDate
    )
SELECT * FROM DateRange

First and last day of the current month T-SQL

In programming there are usually many ways to accomplish a given task. A good developer is always on the search for a cleaner algorithm. The caveat being that less code is great as long as it does not come at the expense of code readability/maintainability.

One algorithm question I used to ask in interviews went as follows:

Give me an algorithm to calculate the last day of the current month assuming that your language has functions to both create dates and perform date arithmetic.

A simple way to do this is to find the first day of the next month, and subtract a day. T-SQL does not have a last day of the month function so I found that I was having to write code like this a lot:

 
declare @startDate dateTime
declare @endDate dateTime
set @startDate = getDate()
set @startDate = CAST(CAST(YEAR(@startDate) AS VARCHAR(4)) + '/' + CAST(MONTH(@startDate) AS VARCHAR(2)) + '/01' AS DATETIME)
set @endDate = DATEADD(month, +1, @startDate)
set @endDate = DATEADD(day, -1, @endDate)

print @startDate
print @endDate

But in reading Itzik Ben-Gan's book T-SQL Fundamentals, he illustrated a simpler way to to this:

DECLARE @StartDate datetime
DECLARE @EndDate datetime
SET @StartDate = DATEADD(month, DATEDIFF(month,'20120101', CURRENT_TIMESTAMP), '20120101')
SET @EndDate = DATEADD(month, DATEDIFF(month,'20120131', CURRENT_TIMESTAMP), '20120131')
print @startDate
print @EndDate 

This way simply calculates the month offset between an anchor date and the current date and then adds that many months back to the anchor date. If the anchor date is the first date of its month, this yields the first date of the current month. However if the anchor date is the last day of its month, you get the last day of the current month.

For me a much more elegant algorithm that is readable and maintainable.

Practical example of the Revealing Module Pattern in JavaScript

One of my friends, Russell Durham, created an excellent blog post on the revealing module pattern in javascript. I asked him if I could replicate this on my blog. The original post is JavaScript Revealing Module Pattern

The revealing module pattern is one of several patterns that allow you to create namespaces within your javascript while also providing a method of creating public and private functions. In general, anything you write in a script block is considered to be in the global namespace so any other javascript on the page will have access to it.

 
function globalOne() {
 //random code here
};

function globalTwo() {
 globalOne();
}; 

I’ve seen a lot of developers that actually write javascript this way and while it may still work it is not a good practice. When writing your class libraries or other server side code you wouldn’t (at least I hope you wouldn’t) expose every function within the library. Some functions exist only to help the public functions complete their task and have no business being called outside of the library. The same can apply to javascript functions. Let’s say you have an online shopping site and while in the checkout process the user can update the quantity of what’s in the cart as well as the shipping method and this in turn updates the total. Using the method above of keeping everything in the global namespace our javascript would look something like this:

function updateQuantity(newQuantity) {
 //code to update the quantity
 updatePrice(newPrice);
};

function updateShipping(newShippingMethod) {
 //code to update the shipping method
 updatePrice(newPrice);
};

function updatePrice(newPrice) {
 //code to update the price
}; 

Functionally, this code is correct. It meets the requirements and everything is good. However, now everyone has access the the updatePrice function. I’m not sure about everyone else but I sure don’t want my users to be able to update the price whenever they feel like it. I want a little more control over how and when that function is called. This is where the revealing module pattern comes in. The pattern is pretty simple:

var myObject = (function() {

 var publicFunction = function() {
  //do something public
 };

 var privateFunction = function() {
  //do something private
 }

 return {
  doSomething: publicFunction
 }

})();

The first thing you’ll notice is that we create an object to place the functions inside of. Doing this removes the functions from the global namespace and makes the only visible within the scope of the object (making them all private). To make a function public you need to return the function from within the object which is done on line 11. Notice how left of the “:’” is “doSomething” while to the right is the name of our function. Changing the value on the left side allows us to change the name of the public facing function. So now, instead of calling:

myObject.publicFunction();  

We can call it like this:

myObject.doSomething();  

In this case doSomething is mapped to publicFunction. Now that we’ve seen the pattern, let’s apply it to our earlier example with the shopping cart.

var cart = (function() {

 function updateQuantity(newQuantity) {
  //code to update the quantity
  updatePrice(newPrice);
 };

 function updateShipping(newShippingMethod) {
  //code to update the shipping method
  updatePrice(newPrice);
 };

 function updatePrice(newPrice) {
  //code to update the price
 };

 return {
  updateQuantity: updateQuantity,
  updateShipping: updateShipping
 }

})();

That’s much better. Now all the functions are removed from the global scope and placed inside of our new cart object. Only updateQuantity and updateShipping are returned which means that updatePrice is now a private method that can not be accessed outside of the cart object. To call our new functions is a simple as:

cart.updateQuantity(5);
cart.updateShipping('overnight');  

However, if we try and call cart.updatePrice(5.00) we’ll have an exception thrown since cart.updatePrice does not exist. As I mentioned before there are other patterns that can be used to accomplish the same thing but the revealing module pattern is the one I find myself using the most often. It is easy to read, easy to use and makes exposing public members simple.

Conversion between HTTPContext and HTTPContextBase

 
// Getting HttpContext from HttpContextBase:
HttpContext httpContext = httpContextBase.ApplicationInstance.Context;

 
// Getting HttpContextBase from HttpContext:
HttpContextBase httpContextBase = new System.Web.HttpContextWrapper(context);

Returning null when implementing IDependencyResolver

We have an MVC4 application that uses the Unity IoC container. Upon startup there were numerous errors like

Activation error occured while trying to get instance of type IControllerFactory, key ""

I found a fix for this in a stackoverflow article: Activation error occured while trying to get instance of type. The article included an implementation of IDependencyResolver that returned null when an object could not be resolved.

I then remembered that that I had used this solution before without really thinking about it.

I found a better explanation in this article ASP.NET MVC 3 Hosting :: Problem in implementing IControllerActivator in ASP.NET MVC 3.

SelectListItems from a LINQ query using anonymous types

 
                var searchList = from products in productdbConnection.Products
                                 group products by products.Discontinued  into g
                                 select new SelectListItem
                                 {
                                     Text = g.Key.ToString(),
                                     Value = g.Key.ToString()
                                 };

I wanted to show an example of creating SelectListItems from a LINQ query using anonymous types and grouping to grab distinct elements.

Example using pivot in SQL Server

-- Create a test table
CREATE TABLE [dbo].[AnnualSales]
 (         
       [CustomerID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,         
       [Gender] [char](1) NULL,               
       [City] [varchar](25) NULL,
       [Education] [varchar](25) NULL,               
       [AnnualPurchases] [money] NULL
 ) 
 
 -- Insert some test data
 insert into AnnualSales (Gender, City, Education, AnnualPurchases) values ('M','New York', 'University', 6223) 
 insert into AnnualSales (Gender, City, Education, AnnualPurchases) values ('M','New York', 'High School', 4233) 
 insert into AnnualSales (Gender, City, Education, AnnualPurchases) values ('F','Seattle', 'University', 6560) 
 insert into AnnualSales (Gender, City, Education, AnnualPurchases) values ('M','Chicago', 'University', 5001) 
 insert into AnnualSales (Gender, City, Education, AnnualPurchases) values ('F','New York', 'University',7034) 
 insert into AnnualSales (Gender, City, Education, AnnualPurchases) values ('F','Chicago', 'University',5345) 
 insert into AnnualSales (Gender, City, Education, AnnualPurchases) values ('F','Seattle', 'High School',790) 
 insert into AnnualSales (Gender, City, Education, AnnualPurchases) values ('F','Seattle', 'None', 240) 
 insert into AnnualSales (Gender, City, Education, AnnualPurchases) values ('M','Seattle', 'University', 4300) 
 insert into AnnualSales (Gender, City, Education, AnnualPurchases) values ('M','New York', 'None', 232)

  -- Use Pivot
  -- Columns that get displayed
  SELECT City, 
         M, 
         F
    FROM (
           -- We are summing AnnualPurchases, our pivot defines the columns by gender so the rows are cities
           SELECT Gender, 
                  City, 
                  AnnualPurchases
             FROM annualSales) ansales
            PIVOT (avg(AnnualPurchases) 
              FOR Gender IN (M, F)
          ) 
      AS pvt
ORDER BY City

I wanted to show an example of the Pivot operator in SQL Server. So I implemented an example that I saw in an O'Reilly excel pivot table tutorial. ** page 2 of this tutorial has an error, the average male purchase for New York should be 3566.

Table-Valued Function in SQL Server

---- Create a test table and insert some data 
IF OBJECT_ID('ProductionHistory')>0   
       DROP TABLE ProductionHistory;  

 CREATE TABLE [dbo].[ProductionHistory]
 (         
       [WellID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY, 
       [WellName] [varchar](50) NULL,               
       [Date] datetime NULL,         
       [Oil] decimal(18,6) NULL,               
       [Gas] decimal(18,6) NULL,               
       [Water] decimal(18,6) NULL               
 )  

 DECLARE @counter int
 SET @counter = 1            

 WHILE (@counter <=1000)
 BEGIN                           

       INSERT INTO [ProductionHistory](Wellname, [date], [oil], [gas], [water])                     
       VALUES ('Mustang Ranch #1', DATEADD(day, @counter, '2009-01-01'), 50 + (80-50)*RAND(), 10 + (30-10)*RAND(), 1 + (10-1)*RAND())                  

       INSERT INTO [ProductionHistory](Wellname, [date], [oil], [gas], [water])                     
       VALUES ('Mary May #1', DATEADD(day, @counter, '2009-01-01'), 80 + (120-80)*RAND(), 1 + (10-1)*RAND(), 20 + (30-20)*RAND())                  



       SET @counter = @counter + 1
 END  
 GO
---- Create a table valued function to sum values to date
Create FUNCTION dbo.udf_GetCumulativeProduction
 (
       @WellName varchar(50),
       @Date datetime
 )
 RETURNS @CumulativeProductionTable TABLE
 (
       CumulativeOil Decimal(18,6),
       CumulativeGas Decimal(18,6),
       CumulativeWater Decimal(18,6)
 )  

 BEGIN
      
       INSERT INTO @CumulativeProductionTable(CumulativeOil, CumulativeGas, CumulativeWater)
       SELECT
             SUM(Oil), SUM(Gas), Sum(Water)
       FROM
             ProductionHistory
       WHERE WellName = @WellName 
         AND Date <= @Date     
       RETURN
 END
 GO

---- Call the function for each row 
SELECT *
FROM ProductionHistory ph
 CROSS APPLY dbo.udf_GetCumulativeProduction(ph.wellName, ph.date)  

Wanted to have a simple example of a table-valued function. For this example I created some daily production data for a couple of wells. I use the table valued function to calculate the cumulative production to date for each well.

Queryable.Where versus Enumerable.Where

        public ViewResult list1()
        {
            IQueryable<Customer> custs = db.Customers;
            var someCustomers = custs.Where(c => c.lastName.StartsWith("Smith"));
            return View();
        }

        public ViewResult list2()
        {
            IEnumerable<Customer> custs = db.Customers;
            var someCustomers = custs.Where(c => c.lastName.StartsWith("Smith"));
            return View();
        }

I had a mystery for a few minutes the other day when I was testing a snippet of code. I was not getting any records back from list2(), while list1() was functioning like I expected.
 In the test database I was using, all last names were stored in lowercase.

This illustrated the difference between
Queryable.Where (lambda expression converted to an expression tree)
and
 Enumerable.Where. (lambda expression converted to a delegate)

In the first case the filter is executed in the database and is not case sensitive. In the second case, the filter is executed in .NET code using Contains which is case sensitive.