SQL Table for Work Days

I was recently working on some reports using SQL and SSRS to find the velocity of my team. I found that the standard DATEDIFF(d, start, end) which includes weekends just wasn’t going to cut it.

So… I created my own table…

 

   1:  DECLARE @Result TABLE (CalendarDate DATETIME, WorkDay BIT)

   2:   DECLARE @startDate DATE = '2012-1-1'

   3:   DECLARE @endDate DATE = '2015-12-31'

   4:   Declare @CurrentDate datetime

   5:   DECLARE @workday BIT 

   6:    Set @CurrentDate=DATEADD(day, -1,@StartDate)

   7:    While @CurrentDate<=@EndDate

   8:    Begin

   9:      Select @CurrentDate= DateAdd(dd,1,@CurrentDate)

  10:      SELECT @workday = CASE WHEN datename(dw,@CurrentDate) IN('Saturday','Sunday') THEN 0 ELSE 1 END;

  11:      Insert Into @Result Values (@CurrentDate, @workday)

  12:    End

  13:    

  14:    SELECT *

  15:    INTO mwg.Calendar

  16:    FROM @result

  17:    

  18:  SELECT * FROM mwg.Calendar

 

And then I created my own DIFF function

   1:  CREATE FUNCTION [mwg].[WorkdaysDiff](@date1 DateTime, @date2 DATeTIme) RETURNS INT 

   2:  AS 

   3:   

   4:  BEGIN 

   5:  DECLARE @result INT;

   6:  --DECLARE @date1 DATE = '2012-09-24';

   7:  --DECLARE @date2 DATE = '2012-10-12';

   8:   

   9:  SELECT @result = COALESCE(SUM(CASE WHEN WorkDay = 1 THEN 1 ELSE 0 END), 0) FROM mwg.Calendar

  10:  WHERE CalendarDate >= @date1

  11:      AND CalendarDate <= @date2

  12:      

  13:      RETURN @result

  14:  END    

  15:   

  16:  GO

So now I can query a velocity that excludes weekends. I can also toggle off holidays if I choose to.

SSRS Continuous Line

I have ran into issues with SSRS drawing a continuous line before. What do I mean by continuous line…

The yellow line actually has a point at 8/20 and so there should be yellow going from the left edge to the right. Also note that we want it to connect the dots on the blue line where there is a gap.

Lots of googling led me to start playing with EmptyPoint and EmptyPointValue.

But the result isn’t really what I was looking for.

First notice the blue line shot off to the right. That is not what I want because those dates haven’t happened yet. I want the blue line to just stop. Also note that the color is black. Getting the color to match the series would require custom code…. really.

Then I happened to stumble across something.

From this view everything looked fine…

But Series Properties revealed a different perspective.

No Category Field. So I set the CategoryField to TimeStamp and re-render the report…

Ah much better.

Hopefully this will save someone else the headache I went through. If nothing else – I can circle back to this next time I try and solve this problem.

Visual Studio allows seeing whitespace

Did you know that you can make whitespace visible in Visual Studio.

Ctrl+R, Ctrl+W

I discovered this from here.

It looks like this

Specifications in Javascript

I have learned to love the concept of specifications, derived from the Specification Pattern. While the way that I have used them is really just a subset of the actual pattern – I have found it very easy to introduce into my existing code.

Basically, we want to separate boolean based logic from the domain object so that it can be used by others, and changed independently.  For example given a Person object,

We may have business rules about their name like hasName and isAge.

these examples show how we can easily reuse the logic behind hasName and isAge. Here is what the functions actually look like.

It is worth noting that hasName doesn’t actually have to return a function – because the specification itself doesn’t have any arguments (isAge does). For consistency, I decided that all specifications should wrap a function. The actual specification takes the arguments, and the inner function takes the object to be tested.

This actually only gets us to the point that we can do things like this

not things like

if we have gone through all the effort to create the specification, we might as well make it available right on the domain object. In a dynamic language like javascript that is relatively easy and highly reusable.

Sweet!!

I am debating moving specifications into the prototype stack. They aren’t very usable in the typical context of prototype (inheritance), but it would eliminate the need for passing specifications to SpecificationInjector because it would just look for it on the object that was passed to it.

MVVM and Dependencies

I am still learning Model-View-ViewModel via KnockoutJS. One concern I find myself continually coming back to is handling dependency. Let me illustrate with a simple example.

   1:  function PersonViewModel(personModel){

   2:      this.Name = ko.observable(personModel.Name);

   3:  }

Here we have created a simple view model for a person – not concerned with dependencies just yet… but now we want to be able to save a person – so we end up with…

   1:  function PersonViewModel(personModel){

   2:      this.Name = ko.observable(personModel.Name);

   3:      this.save = function(){

   4:          $.ajax(...);

   5:      };

   6:  }

This is a point of contention. My sniffer goes off about Single Responsibility, it is very possible that at some point we may change how we access the server for Person, and Person should be unaffected (ie. url change, moving some arguments into the url/ query string, switching from rest to node.js, or not hit the server at all, like local storage). So lets get some separation of concerns in here.

   1:  function PersonViewModel(personModel, personDataAccessObject){

   2:      var self = this;

   3:      self.Name = ko.observable(personModel.Name);

   4:      self.save = function(){

   5:          personDataAccessObject.save(self);

   6:      };

   7:  }

Alright, so this is looking good… until we start using it…

   1:  function ContactManagerViewModel(){

   2:      var self = this;

   3:      self.contacts = ko.observableArray([]);

   4:      self.createContact = function(){

   5:          self.contacts.push(new PersonViewModel({

   6:              Name: 'Sally'

   7:          }));

   8:      };

   9:  }

This is what we would like to see – note the simple call to construct a PersonViewModel – but this is going to fail miserably when we try to save (b/c we never passed in the personDataAccessObject). Surely we could just pass it in, but the coupling can get out of control as everything it needs, we need in our constructor, and we have to pass to it, and anytime its constructor changes so do we. Been there, done that, ain’t going back.

Options

1. Global Namespace we can put personDataAccessObject in the global namespace so that any other class can get to it, it also provides a way for a consumer to change that dependency without effecting other code. What I don’ like is that because it is global – if there were multiple instances of our contactManager, it would be very difficult to allow them to use different implementations of personDataAccessObject because they are both pointing to the same global reference. (Note while on the surface personDataAccesssObject might not seem like a good example, imagine one instance is for preview and you don’t want to actually save data)


2. Closure this style is going to be harder to explain, but basically the entire app (all the ViewModels, etc.) live in one closure so that they can share variables. This giant closure would have one public method exposed which returns the namespace so that you could put it wherever you want. Then you could have two of these namespaces, and can override the implementation of personDataAccessObject for just one of them. I prefer to keep my js classes encapsulated to one js file as this keeps me honest in terms of coupling. The equivalent of this in C# would be to use inner classes – which is rarely the right solution.


3. Coupling to Parent  for our PersonViewModel, instead of taking a personDataAccessObject, we would take a ContactManagerViewModel and would reference it as parent. ContactManagerViewModel would have the responsibility of having a way for us to save. I don’t feel like this really solves anything. We still have coupling between the two and we still need to pass in extra stuff to PersonViewModel. Where I guess would buy us something is when PersonViewModel requires 5 dependencies, it could be just one.


4. Factory Pattern in forcing myself to think about how I would solve this if I wasn’t in the UI – I land at Factory Pattern. The idea is that we would have one or more factories that are responsible for depdencies. They would encapsulate any instantiation logic so that there is one place that knows how to properly instantiate things, and would expose methods for things like personDataAccessObject.

Ironically, Factory Pattern is a little of the other 3 (but arguably the right little). Its constructor would live in the global namespace so that anyone can create one. It will have its own closure maintaining instances, etc. that is is not global like #2. And we will have to pass it to our children, but arguably only it, like #3.

So here is the factory

   1:  function ContactManagerFactory(){

   2:      this.getPersonDataAccessObject = function(){

   3:          return {

   4:              save : function(person){

   5:                  $.ajax(...);

   6:              }

   7:          };

   8:      }

   9:  }

we can new one up and override things at will (have as many separate instances as we want).

   1:   

   2:  var factory = new ContactManagerFactory();

   3:  factory.getPersonDataAccessObject = function(){

   4:      return {

   5:          save : function(){

   6:              // do nothing - this is  a preview

   7:          }

   8:      };

   9:  }

we can then pass it around like so

   1:  function ContactManagerViewModel(factory){

   2:      var self = this;

   3:      self.contacts = ko.observableArray([]);

   4:      self.createContact = function(){

   5:          self.contacts.push(factory.newPerson({

   6:              Name: 'Sally'

   7:          });

   8:      };

   9:  }

notice the need for a newPerson method (which needs to pass a factory to PersonViewModel and so happens to be a factory – see where this is going…)

   1:  function ContactManagerFactory(){

   2:      var self = this;

   3:      self.getPersonDataAccessObject = function(){

   4:          return {

   5:              save : function(person){

   6:                  $.ajax(...);

   7:              }

   8:          };

   9:      };

  10:   

  11:      self.newPerson = function(personModel){

  12:          return new PersonViewModel(personModel, self);

  13:      }

  14:  }

Lastly,

   1:  function PersonViewModel(personModel, factory){

   2:      var self = this;

   3:      self.Name = ko.observable(personModel.Name);

   4:      self.save = function(){

   5:          factory.getGersonDataAccessObject().save(self);

   6:      };

   7:  }

In closing, there are things I don’t like – (super factory object) but it’s the closest I was able to come up with, and the only thing I could come up with that I could stomach.

Continuous Integration for Javascript

I have been on a journey to introduce Javascript into our Continuous Integration and Test Driven Development eco-system.

For so long, Javascript has been treated like the ugly duckling, but with budding technologies like Knockout.js and Node.js, javascript is becoming a critical component of most systems.

The team at work decided to move forward with QUnit for the testing framework. I found a great link that showed how to get up and running with QUnit and PhantomJs here.

I ended up with the following files (from jQuery and the above blog post). Note I did not use the same name for my phantom-qunit-testrunner.js as the author of the blog post. 

Update – I am now using the run-qunit.js from phantomJS instead of phantom-qunit-testrunner.js from the above blog post.

I was then able to run phantomJs

Sweet!!

The blog post author was going after Continuous testing, which is very cool – but not what I need.

There is a lot to consider in the context of automation for Continious Integration. Generally, I don’t need my own html file. I just need the tests. So here goes the first feature.

When a js file (ie. coolplugin.js) is discovered during continuous integration, we should look for an associated test file (ie. coolplugin.test.js), if one is found, then we should generate the required qunit html, run the html though PhantomJS. The file should be unique (ie. coolplugin.test.html), and should not be deleted so that it can inspected for a failing test.

There will be times where I do need to provide my own dom elements, and therefore provide my own html. so here goes another feature.

When a js file (ie. coolplugin.js) is discovered during continuous integration, we should first look for an associated html file (ie. coolplugin.test.html), if one is found, then we should run the html though PhantomJS.

For this I created a simple executable which will perform the above tasks. (it is currently available on our internal svn – if you want it just leave a comment and I can put it in git hub).

Then I tied it all together with NAnt.

I am new to NAnt so I am sure I did several things wrong but its working….

Tests Passed

Tests Failed

Whats next? Prototype to production – and continuous testing for local development.

jQuery plugins and html data driven options

Wouldn’t it be nice if you could write a jquery plugin that takes options from the plugin, from the dom, or from defaults….?

But who wants to own that code?

This used to be a pain a few years ago, but html5 and jquery.data have made this quite easy.

JSHint or JSLint

Heard the buzz about JSHint?

I too am not a big fan of an opinionated code quality tool, but one of the main reasons I am hearing of people using JSHint is that doesn’t force you to declare your variables at the top of a function.

While it is really annoying to have to do this, I remembered in reading Javascript the Good Parts that it is best practice – I just couldn’t remember why.

Then I ran across this post which provides a snippet of why.

var name = "Dan";

function getName(){
    console.log(name);
    var name = "Paul";
    return name;
}
var newName = getName();

You would expect that console.log(name) would print “Dan” but instead it will print ‘undefined.’ This is because the variable name is scoped to the function and though the value gets set later, it is declared and scoped to the entire function.

If your going to allow this – you need to understand the ramifications.

The counter argument is that declaring variables at the top is noisy and unintuitive, especially in loops.

RabbitMQ

Alright, its time to dive into RabbitMQ and see if it can pull its weight. 

1. Created a Server2008 VM
2. Installed Erlang and RabbitMQ
3. Install Management Plugin (comes with RabbitMQ just not enabled)
1. Enabled Management Plugin
1. 
2. Restarted the Service
1. Supposed to do this
1. 
2. be sure to call rabbitmq-service.bat and not rabbitmq-server.bat
3. Get an answer at http://localhost:55672/mgmt/ but what user… standard installation includes a user: guest with password: guest

Knockout Presentation

I had a great time presenting Knockoutjs at VtDotNet last night. It was a privilege to get to share such a cool technology with other enthusiasts.

The slides can be found here.

The prototype can be found here. (note that it requires MVC4 – yes its time!!)

Here are the link of resources for easy access

• http://jsfiddle.net/
• Decompose a problem using this sandbox
• http://knockoutjs.com/
• Interactive tutorials – experience them
• http://www.knockmeout.net/
• Tons of experience captured in this blog
• http://channel9.msdn.com/Events/MIX/MIX11/FRM08
• Great video from early days of Knockout
• http://channel9.msdn.com/Events/TechDays/TechDays-2012-Belgium/199
• Recent video of Knockout + Web Api + Upshot = Amazing!

Thanks to all those that came and I welcome any feedback jhoguet@gmail.com.

Getting out from beneath the cell phone giants

My wife and I are currently paying $135.45 (before taxes) for our cell phones which gives us 700 shared minutes and two unlimited data plans. In an effort to save money we are biting the bullet and considering alternatives to bring this perpetual cost down.

Spoiler: We are going to switch to a $2/day prepaid plan with no contract. We are going to use google voice for most of our phone calls for free and will buy an adapter ($50) so that we can use a regular phone to make Google Voice phone calls from our house. We can then adjust our usage to effectively pay $0 / month for our cell phones instead of being locked into $135 month after month. 

Our requirements are:

• one data plan for GPS navigation and web browsing – we decided we could share the phone with the data plan when needed
• great cell coverage for emergency phone calls
• ~500 text messages / month
• ~700 minutes / month

Coverage for emergencies is a high priority, so this limited us to Verizon and ATT. This was based on looking at coverage maps and asking around.

Now that we are down to two companies – it is time to think about the plan.

My first thought was that if we joined forces with another family then a family plan might be worth it… I built an excel spreadsheet and found two things

1) Unlimited plans would be more expensive than we are paying now

2) 2000 minute plans would only be slightly cheaper than we are paying now

(note that this is based on 2 lines with 2 data plans per family)

This was unacceptable when factoring in the drama that comes from working with another family (fighting over minutes, handling money, etc.) so we moved on to prepaid…

We learned that prepaid with Verizon was not an option. They charge you $10-20 for texting, plus the voice part, plus the data part. ATT prices were close to the same as Verizon but included text messages making Verizon about $10-20 more expensive.

So onto ATT prepaid. Lets first talk about data. Data options are

Which means nothing if you don’t know how much you use – so off to Verizon to see how much we use…

Ouch – line 5961 (me) is using a lot – this pre-paid thing (as far as data goes) might not work out. Line 6447 could get by with the $25 plan which is $5 cheaper than currently paying Verizon. I called ATT and asked what would happen if I went over the 500MB.

“If you have money in your account, and your account configured to allow you to go over – then you will buy another 500MB.”

This is good news in the sense that I can use more than 500MB but means I would need 3 subscriptions per month totaling $75. Not so good.

But – this is all a mute point because I am not using that much anymore. At my last job we were not allowed to stream radio – so I was using Pandora through my phone. At my new job I can stream through my computer – so what is my usage like now…

So this usage extended out to a 30 day month, is ~260MB. Much better!

So data is $25. And the best part is… because I am not under contract (by going prepaid) I can turn the data on and off at will. I confirmed this by calling ATT and they do not require data with a droid on a pre-paid plan. Worth noting that iPhone has some special data requirements and isn’t eligible – but this didn’t effect me so I didn’t spend any time to figure out what that meant. 

Now to the voice and messaging of pre-paid.

The basic plan is

this provides a benchmark for comparing to the other plans. The pay by minute plan looks like this

With text messaging unlimited, but only 250 voice minutes included – the question becomes how many minutes would I have to use – to be better off with the unlimited plan – in other words - what is my breakeven point. A few calculations later and we arrive at 500 minutes. This is not looking like a good fit for us.

So how about the pay by day plan.

This plan also has unlimited texting  so the question is how many days would I have to use my phone to be better off with the unlimited plan. At $2 / day – it is simply 25 days.

It is worth taking a pause here to explain the pay by day. If you use your phone to send or receive a call, or send a text message, that is a $2/day charge. Yes, receiving texts are free. Once you you pay for  a day, you have unlimited calls and texts.

Back to the analysis – if we can keep it under 25 days – than we would be better off with the pay per day.

So for the final analysis we need to compare the prepaid to our contract plan. Our current Verizon bill $135 – we have already decided to remove one data plan at $30 so we arrive at $105. Prepaid monthly would be $50 per phone + $25 for one data plan. Totaling $125… but what about pay per day.

The breakeven point for pay per day with one data plan at $25 and 2 phones paying $2 / day is 20 days per phone.

Our conclusion:

We are committed to bringing down our cell bill and the prepaid gives us the greatest flexibility to do that. We like that if we are trying to save money we can easily cancel data temporarily and limit our phone usage to potentially emergency only. We would gain the option to not use our phone for a single call or text and effectively pay $0 on any given month. And during that entire month we would still have coverage and the capability to make an emergency call.

Heavy usage months would cost us $125 which is more than contract $105. But we plan on having enough savings throughout the year to offset these heavy months.

To help facilitate limited cell phone usage we are setting up google voice accounts for each of us. We will be able to make calls from a house phone using google voice (going through the internet) mitigating some of our cellular needs.  Note that Google Voice is currently free. We will not give out our cell numbers and instead give out our google voice number. We will configure Google Voice to forward to the house phone, our computers, and our cell phones. If we are unable to answer on the computer or the house phone then we can choose to answer with the cell phone.

Angela won’t have a data plan but will be able to connect her Droid over our wireless network. She will still be able to use all of her apps (like facebook) but just not while out on the road.

We will limit calls while driving and instead make those calls from home.

We ordered an Obi phone adapter so that we can connect a regular phone to our google voice account.

http://www.amazon.com/OBi110-Service-Bridge-Telephone-Adapter/dp/B0045RMEPI/ref=sr_1_1?ie=UTF8&qid=1327870783&sr=8-1

[Book Review] Crucial Conversations: Tools for talking when stakes are high

Title: Crucial Conversations: Tools for talking when stakes are high

Author: Kerry Patterson, Joseph Grenny, Ron McMillan, Al Switzler

Link: http://www.amazon.com/Crucial-Conversations-Patterson-McMillan-Switzler/dp/B004H7422O/ref=sr_1_2?ie=UTF8&qid=1325958089&sr=8-2

Overall I found this book to be helpful in putting a finger on such an impossible skill as managing a Crucial Conversation. Over the years I have found myself engaged in crucial conversations where the skills described in the book are necessary but undefined. I am confident that my marriage and relationship with my children will be improved now that we can use a consistent language to manage the Crucial Conversation much more efficiently. For other crucial conversations where I cannot expect stakeholders to be aware of this language (like co-workers) - I can now review a concise cheat sheet and help steer the conversation towards a healthy output.

The authors have a diagram they intend to be a tool but I find it to be unhelpful.

It is clearly too abstract – probably b/c they don’t want people to skip reading their 200+ page book. Well I can’t read the book before every Crucial Conversation, so I decided to create my own.

In creating this, I found that I simplified some of the skills. I effectively combined the Master My Stories (not shown above) with STATE my path and explore others’ path because I feel that this should be a collaborative process where everyone is playing by the same rules. I think the authors separated it so much because some things are outside your control while exploring others’ path, but I feel like I want to be thinking about those things for both.

I also did not include Move To Action as I feel that is a separate problem. It is surely related but there are great resources that focus on just that problem and in the context of a crucial conversation, if we can get through high stakes, varied opinions, and strong emotions to come up with a solution. We can take a break, maybe even go to another cheat sheet to find a way to Move to Action.

I simplified a concept called CRIB which is meant to Make it Safe. CRIB stands for Commit to seek mutual purpose, Recognize the purpose behind the strategy,  Invent a mutual purpose, and Brainstorm new strategies. I found this to be highly repetitive and therefore overly complex. I replaced it with find common ground. This simple statement provides a great reminder if the conversation has gone to silence of violence to make it safe again by finding common ground.

I ignored one of the clever stories. I found that victim and helpless are two similar to clutter my cheat sheet. I found that when I tried to find a difference – a victim had a villain at which point its more of a villain story.

Though I know I am an improved human being after reading this book, I was disappointed during reading at how long the book is. The book is under 250 pages but could easily be under 100 pages. Much of the simplification I did in my cheat sheet was being performed by my mind while I was reading. It is disappointing to finish reading a paragraph or page and then say – wow – that didn’t really add anything new.

I do recommend this book.