Aggregating Data Into a Single SQL Server Column

This week, I was working with a client using nopCommerce to try and fix a performance issue relating to syncing data between their legacy product management system and their nopCommerce system used for their front-facing website.  A plugin was built in nopCommerce that would utilize a SQL Server staging database to stage data from the legacy system and import into nopCommerce on a daily basis.

Specifically, this issue relates to trying to add a promotion to a product, in which promotions and products have a many-to-many relationship with each other, and each product has a one-to-many relationship with a series of product tabs that are used to display information on the product.  The entity relationship looks like this:

When the client attempted to add a promotion to 3000 products, it slowed the sync process from about 30 minutes to 8 hours – causing those products to not reflect the new promotion until mid-day and making for an unhappy client.

Digging into the code, the major slowness issue was relating to creating the product tab page – the system was searching to see if a tab page existed for the product, and then either created a new one or adding to the existing with the appropriate data, forcing iteration through a large dataaset and giving a poorly scaling solution.

Creating a stored procedure for this make the most sense – since it’s just copying data from a few tables into another, but with database programming, I cannot truly iterate through each mapping to create the Promotion product tab correctly, so I needed to use aggregation to add the content to the product tab for each product.

I found out about the STUFF() operator and came up with this:

    'Promotions' as title,
        (SELECT '<br />' + p2.Name
         FROM Promotions p2
         JOIN ProductPromoMappings ppm2 on ppm2.Promo_Id = p2.Id
             and p.StartDate <= GETDATE() AND GETDATE() < p.EndDate
         WHERE ppm2.Product_Id = ppm.Product_Id
         FOR XML PATH(''), TYPE)
             .value('.','varchar(max)'),1,6, '') AS content,

 FROM ProductPromoMappings ppm
 JOIN Promos p ON p.Id = ppm.Promo_Id
     and p.StartDate <= GETDATE() AND GETDATE() < p.EndDate

Here’s what is happening – we are going through the ProductPromoMappings and returning the content for the product tab pages for use.  For the content in the product tabs, we are going through each promotion in an inner query and getting the content for each promotion.  After that, then concatenating it into a single value to be used by the outer query.

Here’s a little more details on some of the commands that may be new to you:

  • WITH TIES – Addition to the ‘TOP’ keyword that allows for returning ‘ties’, or multiple rows in last place with the limited data set.
  • STUFF() – Inserts a string into another string.  Specifically, this allows us to insert the ‘<br />’ element into each subsequent string without inserting the line break in the first element.
  • FOR XML PATH(”) – Outputs the results of a query in XML – for us, this means taking the contents and making them into a single string for use.
  • .value() – Converts the XML node passed from FOR XML PATH into a varchar,  allowing us to escape all characters that would cause issue with XML conversion (such as ‘<‘ and ‘>’).
  • ROW_NUMBER() – Numbers the result rows – this is uses with the OVER and PARTITION keywords to filter each product tab page by the Product ID.

Putting this command into a stored procedure reduced the runtime from 8 hours to about 15 seconds, as the data is now just copying directly into the database.




Adding Google Analytics To Your Angular CLI App

As you’re working through your Angular CLI application, you may want to set up Google Analytics for tracking the traffic coming in to your website.  This guide will help on setting up the basics for being able to view the active users on you web application, along with being able to see more analytical data on how your web application is being used.


Set up a Google Analytics account and property for your web site.  You should get a code that looks like the following for your web application: UA-XXXXXXXXX-XX.

Adding the Tracking Code to Your Application

Once you have the tracking code from your Google Analytics account, you’ll want to add it to the application somewhere that it can be referenced in the application.  The best place to do this is in the environment.ts file meant for environment-specific variables.  Add the following to your environment.ts file:

googleAnalyticsTrackingId: 'UA-XXXXXXXXX-XX' // add the tracking ID from your GA account

Once this is done, you’ll need to add the actual tracking code to your application.  One way to do this is to write it into the code that bootstraps your Angular App in the main.ts file.  Add the following to your main.ts file:

import { environment } from './environments/environment';

if (environment.googleAnalyticsTrackingId !== '') {
 document.write('<script>' +
 '(function(i,s,o,g,r,a,m){i[\'GoogleAnalyticsObject\']=r;i[r]=i[r]||' +
 'function(){(i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();' +
 'a=s.createElement(o), m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;' +
 'm.parentNode.insertBefore(a,m)})(window,document,\'script\',' +
 '\'\',\'ga\');' +
 'ga(\'create\', \'' + environment.googleAnalyticsTrackingId + '\', \'auto\');</script>');

Modifying The App Component

Google Analytics works by receiving page events from the web application, such as page loads.  With a single page application, you’ll need to send an event for each time the user routes to a different part of the application.  The easiest way to do this is to edit the App Component, which will affect all routes.

Make the following additions to your app.component.ts file:


import { environment } from '../environments/environment';

declare let ga: Function;

constructor(private router: Router, private location: Location) {
    if (!this.hasGoogleAnalyticsTrackingId()) { return; }

    this.router.event.subscribe(event => {
        if (eventinstanceofNavigationEnd) {
            ga('set', 'page', event.urlAfterRedirects);
            ga('send', 'pageview');

private hasGoogleAnalyticsTrackingId() {
    return environment.googleAnalyticsTrackingId !== null &&
           environment.googleAnalyticsTrackingId !== '';

Verify Working Functionality

Once this is all set, you’ll want to make sure your changes are working – you can actually determine this using your local environment.  Open the Google Analytics dashboard and go to the view for the property you’ve created for your application, and access the ‘Real-time’ Reports.  You should see the following:

Start your Angular application locally and access the home page.  Once you do this, you should see the following change in the Google Analytics dashboard:

and just to see how the system acts on routing changes:

Congratulations!  You’ve set up Google Analytics for your Angular app.

Setting Up For Multiple Environments

Depending on how you’re deploying your Angular CLI app, you likely are using multiple environments (such as a Staging and a Production environment).  A common practice is to set up a separate property inside Google Analytics for each environment to allow for viewing statistics for each website.  Especially useful for testing to see if the changes are actually going through the environments.

The optimal way to set this up is using the environment specific files in Angular.  You’ll include the property-specific code for each environment, and build the application for each environment.

For this example, let’s imagine we have 3 different environments:

  • My local environment I use for development – we do not want the Google Analytics code running on this.
  • A staging environment used for testing changes before promoting changes to production.
  • A production environment that will be used by the end user.

Angular CLI comes with two files, so let’s create a third one called environment.staging.ts.  Once that’s done, add the following content to the new file and the file:

export const environment = {
    production:true, // allows for simulating production configuration
    googleAnalyticsTrackingId: 'UA-XXXXXXXXX-X'  // your tracking ID for the specific environment goes here
    // ..and any other environment variables you may have.

Notice that we’re leaving environment.ts as is – since we want the codebase to not use Google Analytics when working locally.

We will make one more change to .angular-cli.json to allow for Angular CLI to recognize the new environment and allow for the Staging environment build:

"environments": {
    // dev and prod statements are here, add this line below
    "staging": "environments/environment.staging.ts",

Once this is done, you’ll be able to use different builds that associate with different tracking IDs.



Tutorial: Angular 2 & Google Analytics with Event tracking

2018.03.08 Training

For my last day before my procedure (that will take me off for around a week), Zach granted me a day of heavy lifts.  I am pretty happy with how it all went down, I think these are both PRs without a belt.


  • 70 x 3
  • 90 x 3
  • 110 x 3
  • 130 x 3
  • 150 x 2
  • 170
  • 180

Deadlift (straps)

  • 70 x 3
  • 90 x 3
  • 110 x 3
  • 130
  • 150
  • 170
  • 190
  • 210

2018.03.05 Training

Decent day today, it’s been a while since front squats so I am sure I’ll put some weight on those in a few weeks. Being able to hit 140 for 3, I really should be able to clean 140…

Front Squat

  • 70 x 3
  • 90 x 3
  • 110 x 3
  • 120 x 3
  • 130 x 3
  • 140 x 3


  • 40 x 5
  • 50 x 5
  • 55 x 5
  • 60 x 5
  • 65 x 5

Bent Over Rows (3 sec pause)

  • 40 x 10
  • 45 x 10
  • 48 x 10
  • 50 x 10
  • 51 x 10

Changing URL for the nopCommerce Store Locator with IIS

I was working on what I thought would be a simple request for a client this week – change the default URL for the Store Locator plugin for the nopCommerce platform.  After a bit of digging and looking for an easy solution, I found that the plugin doesn’t support the capability to change the default URL, specifically the ‘/AllShops’ URL that will show a listing of all of the stores added to the application.

For those not familiar, nopCommerce is backed by a series of plugins from the nopTemplates group, which includes plugins such as sliders, menus, and the mentioned store locator.  This store locator allows for adding a series of store locations and having them appear on Google Maps, alongside a listing of all individual stores.  You can see a demo here.

Worry not, there is a simple way to make this work if you are hosting the site using IIS.  First, access IIS Manager, and then access the Web Site that nopCommerce is being hosted on.  Click on the ‘URL Rewrite’  option to show the current URL rerouting rules set up for the Web Site:

Once inside the URL Rewrite module, click the ‘Add Rule(s)…’ option on the right sidebar:

Click on the option to create a ‘Blank Rule’, and then add the following information:

  • Name:  nopCommerce Store Locator /AllShops Redirect
  • Pattern:  store-locator (or whatever you would like the new URL to be)
  • Action URL:  AllShops

After applying this change, test the change using the URL and ensure that you are directed to the Store Locator, and the URL does not redirect to the /AllShops URL.

What this rule does is treats the ‘/store-locator’ URL as an alias for the ‘/AllShops’ URL for the Store Locator, so it would allow both URLs to point to the same place.  In my case, I wanted to change the URL over, so in addition to setting up this rule, I would also need to set up a redirect to have the ‘/AllShops’ URL redirect to the ‘/store-locator’ URL.  This allows for simulating a true URL change.

One caveat with the is that is you use Visual Studio to Publish the application to IIS, it will overwrite all of the URL reroute rules in place, meaning you will need to recreate this rule to make sure your alias still works.

2018 American Open 1 Results

Overall, a decent weekend at the American Open 1, my weight was pretty good and I think for as consistent as I’ve been (not great the last few months), I’m happy with my results.

Snatches well a little iffy, with pull and power not being an issue, but just making sure I punch up on the bar when I catch.  I’ve been having this issue for a little while now, so that’s something to work on for some time.

Clean and jerks ultimately felt great – I picked up on a stronger lockout on the last CJ, and the big lesson I picked up was to really stick the lockout and be patient in standing up with the bar.

I don’t have anything planned for the next few months.  I’ll just plan on continuing my training and picking up a meet that comes up maybe halfway through the year?


  1. 101 (pressout)
  2. 101 (miss behind)
  3. 101

Clean and Jerk

  1. 128
  2. 131
  3. 134

Questions To Ask a Candidate in a Software Developer Interview

Consider my post last week about questions you may want to ask when you’re interviewing for a software developer position, you can also be on the other side of the table, interviewing candidates to work for the place you currently work at.

Depending on how interviews are structured at your company, you might have a hard time thinking of questions that can give you a good feeling of whether the person you are interviewing is a good fit for the company.  One of the overarching things you should be trying to discover in an interview is whether this is a person you would like to work with in the future – whether this person is someone that would be on your team, or whether this person would become a peer you would work with.

Here’s a few questions I’ve used over time that have helped me determine that:

What has been your experience in both jumping into projects in maintenance mode vs. working on a project from start to finish?

This is good to get a feel of what experience the candidate has in the full software development cycle.  You’ll be able to see the difference between someone with a bit of experience that has been through the major phases of releasing an application.  Developers that work solely with maintenance issues may struggle with being able to explain how to actually get a project off the group, especially considering that a good amount of the work up-front is not just coding, but determining a solution to the problem at hand.

In previous projects, what technical limitations did you work under that you would have liked to change to improve the quality of your work?

This question gives a good feel of issues that a developer would have experienced in the past that they may be passionate about to fix.  Things like having a poor build process or branching strategy can be good candidates, or things like having the developers not be involved in the design and planning of the features to be implemented into the project.

Are there any emerging technologies that are coming up that you’re interested in learning?

This can be a good question to get a feel of the things they are interested in.  Maybe something like machine learning, or a new framework that the developer is interested in trying out.  In particular, this is a good question to get a feel for what kind of stuff the developer truly wants to do, and whether it’s a good fit for the company you work at.  It may shine a light on the fact that the candidate would like to work on data science focused issues when your company focuses on web development, for instance.

What is the best trait a developer can have?

Alongside the questions above, this one gives a feel for the types of traits they value in the development team.  You might get someone to answer that they should be focused on having high code coverage for the projects they work on, or they may say that a developer should be able to communicate well with all business units.  In particular, it’s nice to hear a specific trait, as opposed to hearing that they are simply very good at software development as a whole.

2018.02.24 Training

Lifting felt good again today – need to be a little tighter off the floor for snatches, and for jerks I should be think thinking about dipping a little deeper and relaxing my grip when standing.


  • 70 x 3
  • 75 x 3
  • 80 x 2
  • 85 x 2
  • 90
  • 94
  • 97
  • 100
  • 100

Clean and Jerk

  • 90 x 2
  • 95 x 2
  • 100
  • 105
  • 110
  • 115
  • 120
  • 120
  • 120