Stephen Balkum

Agile Enthusiast, Leader, and Developer

Azure SDK DFInit fails for usernames with a space

no comment

I was moving some of my dev projects to a new computer today. Usual procedure: pull down source from repository, open in VS and do a sanity build. Well, my Azure based project kicked off the Azure SDK initialization chores and I was greeted by this failure:

It was important to scroll this dialog up to see “The option is not valid: balkum.” It took me a while of fruitless web searches before the issue dawned on me. When I built this computer, I entered “Stephen Balkum” for a user during the Windows installation. The Azure SDK must have queried the system for my username and called DFInit with this command line:

DFInit /user:stephen balkum

and the space in my username confused the command.

To fix the issue, I took a stab in the dark (there is no online help on DFInit), and ran it from a command line. The dialog says /user is optional and will pull my username from the parent process. Worked like a charm.

Upgraded to Windows 8.1, what am I installing?

no comment

I have been running Windows 8.1 Preview since it was released. It is a nice improvement over the original version. Of course, with the upgrade to the official release, I have to reinstall all of my apps since I was running preview. So, I will use this post to document the software I install. I’m sure I will be updating this list a few times over the next couple of weeks. And I’ll reference it when my new laptop arrives mid-November.

Office 365
Visual Studio 2013
Resharper 8
SQL Server 2012
SSMS
Dropbox
(Skydrive already installed)
Chocolatey

Via Chocolatey:
Putty
TortoiseSVN
MongoDB
Inkscape
Paint.Net
Console2
Notepad2
Teamviewer

Thanks, LinkedIn, but no thanks

no comment

LinkedIn has been really pushing their new premium service. I have not subscribed, mostly because it is not a trivial amount of money, but a lot of recruiters have because I am now getting pounded with requests from recruiters. Part of me is flattered. I work hard and I think my career says that.

However, my volume of email just took a measurable tick upward and that is not helpful, LinkedIn.

I also try hard to keep my connections list in LinkedIn true, i.e. each person is someone I know and at least had some meaningful discussions. So, with the last request from a recruiter that I politely declined, I unchecked all of the boxes only to discover I had just added this person as a new connection. Very sneaky, LinkedIn, and I deleted that connection.

I did find that I can request people not contact me about new jobs. I even attached a nice message. Time will tell if this helps and I will be marking future recruiting communications as inappropriate.

I am dissatisfied, LinkedIn.

So You Want to Learn SQL… Final Session

no comment

As mentioned in the previous session, this is the last issue of this series.  Even though I could continue with more rarely used SQL features, I feel the content presented meets the stated goal.  Here are the final homework answers.

Homework Answers

1) List the two-week rolling total quantity by territory for the current period and 3 prior periods by week. The result set should have three columns: Period Start Date, Territory Name, and Total Quantity.

SELECT w.startdate, t.Name, SUM(od.OrderQty) AS Quantity
	FROM Sales.SalesOrderHeader o
	JOIN Sales.SalesOrderDetail od ON od.SalesOrderID = o.SalesOrderID
	JOIN Sales.SalesTerritory t ON t.TerritoryID = o.TerritoryID
	JOIN (
		SELECT	DATEADD(dd, -1, DATEADD(ww, DATEDIFF(ww, 0, '8/1/2008'), 0)) as 'StartDate',
			DATEADD(dd, 13, DATEADD(ww, DATEDIFF(ww, 0, '8/1/2008'), 0)) as 'EndDate'
		UNION ALL
		SELECT	DATEADD(dd, -8, DATEADD(ww, DATEDIFF(ww, 0, '8/1/2008'), 0)),
			DATEADD(dd, 6, DATEADD(ww, DATEDIFF(ww, 0, '8/1/2008'), 0))
		UNION ALL
		SELECT	DATEADD(dd, -15, DATEADD(ww, DATEDIFF(ww, 0, '8/1/2008'), 0)),
			DATEADD(dd, -1, DATEADD(ww, DATEDIFF(ww, 0, '8/1/2008'), 0))
		UNION ALL
		SELECT	DATEADD(dd, -22, DATEADD(ww, DATEDIFF(ww, 0, '8/1/2008'), 0)),
			DATEADD(dd, -8, DATEADD(ww, DATEDIFF(ww, 0, '8/1/2008'), 0))
	) W ON W.StartDate <= o.OrderDate AND w.EndDate > o.OrderDate
	GROUP BY w.startdate, t.Name
	ORDER BY w.StartDate, t.Name

2) List the total sales for each day of the week by territory for July 2008. The columns in the result set should be: Territory, Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday. Do not display the territory if it had zero sales all week.

SELECT	t.Name AS Territory,
		ISNULL(Sunday.Sales, 0) AS Sunday,
		ISNULL(Monday.Sales, 0) AS Monday,
		ISNULL(Tuesday.Sales, 0) AS Tuesday,
		ISNULL(Wednesday.Sales, 0) AS Wednesday,
		ISNULL(Thursday.Sales, 0) AS Thursday,
		ISNULL(Friday.Sales, 0) AS Friday,
		ISNULL(Saturday.Sales, 0) AS Saturday
	FROM Sales.SalesTerritory t
	LEFT JOIN (
		SELECT  o.TerritoryID,
				SUM(o.TotalDue) AS Sales
			FROM Sales.SalesOrderHeader o
			WHERE o.OrderDate >= '7/1/2008' AND o.OrderDate < '8/1/2008'
				AND DATEPART(dw, o.OrderDate) = 1
			GROUP BY o.TerritoryID
	) Sunday ON Sunday.TerritoryID = t.TerritoryID
	LEFT JOIN (
		SELECT  o.TerritoryID,
				SUM(o.TotalDue) AS Sales
			FROM Sales.SalesOrderHeader o
			WHERE o.OrderDate >= '7/1/2008' AND o.OrderDate < '8/1/2008'
				AND DATEPART(dw, o.OrderDate) = 2
			GROUP BY o.TerritoryID
	) Monday ON Monday.TerritoryID = t.TerritoryID
	LEFT JOIN (
		SELECT  o.TerritoryID,
				SUM(o.TotalDue) AS Sales
			FROM Sales.SalesOrderHeader o
			WHERE o.OrderDate >= '7/1/2008' AND o.OrderDate < '8/1/2008'
				AND DATEPART(dw, o.OrderDate) = 3
			GROUP BY o.TerritoryID
	) Tuesday ON Tuesday.TerritoryID = t.TerritoryID
	LEFT JOIN (
		SELECT  o.TerritoryID,
				SUM(o.TotalDue) AS Sales
			FROM Sales.SalesOrderHeader o
			WHERE o.OrderDate >= '7/1/2008' AND o.OrderDate < '8/1/2008'
				AND DATEPART(dw, o.OrderDate) = 4
			GROUP BY o.TerritoryID
	) Wednesday ON Wednesday.TerritoryID = t.TerritoryID
	LEFT JOIN (
		SELECT  o.TerritoryID,
				SUM(o.TotalDue) AS Sales
			FROM Sales.SalesOrderHeader o
			WHERE o.OrderDate >= '7/1/2008' AND o.OrderDate < '8/1/2008'
				AND DATEPART(dw, o.OrderDate) = 5
			GROUP BY o.TerritoryID
	) Thursday ON Thursday.TerritoryID = t.TerritoryID
	LEFT JOIN (
		SELECT  o.TerritoryID,
				SUM(o.TotalDue) AS Sales
			FROM Sales.SalesOrderHeader o
			WHERE o.OrderDate >= '7/1/2008' AND o.OrderDate < '8/1/2008'
				AND DATEPART(dw, o.OrderDate) = 6
			GROUP BY o.TerritoryID
	) Friday ON Friday.TerritoryID = t.TerritoryID
	LEFT JOIN (
		SELECT  o.TerritoryID,
				SUM(o.TotalDue) AS Sales
			FROM Sales.SalesOrderHeader o
			WHERE o.OrderDate >= '7/1/2008' AND o.OrderDate < '8/1/2008'
				AND DATEPART(dw, o.OrderDate) = 7
			GROUP BY o.TerritoryID
	) Saturday ON Saturday.TerritoryID = t.TerritoryID
	WHERE Sunday.Sales IS NOT NULL
		OR Monday.Sales IS NOT NULL
		OR Tuesday.Sales IS NOT NULL
		OR Wednesday.Sales IS NOT NULL
		OR Thursday.Sales IS NOT NULL
		OR Friday.Sales IS NOT NULL
		OR Saturday.Sales IS NOT NULL
	ORDER BY t.Name

or

SELECT t.Name AS Territory,
	SUM(CASE DATEPART(dw, o.OrderDate) WHEN 1 THEN o.TotalDue ELSE 0 END) AS Sunday,
	SUM(CASE DATEPART(dw, o.OrderDate) WHEN 2 THEN o.TotalDue ELSE 0 END) AS Monday,
	SUM(CASE DATEPART(dw, o.OrderDate) WHEN 3 THEN o.TotalDue ELSE 0 END) AS Tuesday,
	SUM(CASE DATEPART(dw, o.OrderDate) WHEN 4 THEN o.TotalDue ELSE 0 END) AS Wednesday,
	SUM(CASE DATEPART(dw, o.OrderDate) WHEN 5 THEN o.TotalDue ELSE 0 END) AS Thursday,
	SUM(CASE DATEPART(dw, o.OrderDate) WHEN 6 THEN o.TotalDue ELSE 0 END) AS Friday,
	SUM(CASE DATEPART(dw, o.OrderDate) WHEN 7 THEN o.TotalDue ELSE 0 END) AS Saturday
	FROM Sales.SalesTerritory t
	JOIN Sales.SalesOrderHeader o ON o.TerritoryID = t.TerritoryID
	WHERE o.OrderDate >= '7/1/2008' AND o.OrderDate < '8/1/2008'
	GROUP BY t.Name
	ORDER BY t.Name

So You Want to Learn SQL… Session 4

no comment

Homework Answers

Generate a list of products that have a bill of material, stating each subcomponent and the quantity used. You should use the Production.BillOfMaterials table and create a human readable phrase. You might find the STR function useful.

SELECT 'A ' + p.Name + ' contains' + STR(bom.PerAssemblyQty) + ' ' + c.Name
	FROM Production.Product p
	JOIN Production.BillOfMaterials bom ON bom.ProductAssemblyID = p.ProductID
	JOIN Production.Product c ON c.ProductID = bom.ComponentID
	ORDER BY 1

Date Functions

GETDATE()

This first function is self-explanatory. It gets the time right now.

SELECT GETDATE()

DATEDIFF(datepart, startdate, enddate)

This function returns the difference between two dates. Returned value is an integer. Here are some useful dateparts are:
datepart year quarter month day week hour minute second
Abbreviations yy, yyyy qq, q mm, m dd, d wk, ww hh mi, n ss, s

SELECT	DATEDIFF(dd, '8/1/2013', '8/10/2013') AS 'days',
	DATEDIFF(dd, '8/10/2013', '8/1/2013') AS 'negative difference',
	DATEDIFF(hh, '8/1/2013 3:00', '8/2/2013 1:00') AS 'hours',
	DATEDIFF(m, 0, '8/1/2013') AS 'since 1/1/1900'

DATEADD(datepart, number, date)

This function adds the specified number of dateparts to the given date and returns the new date. The dateparts are the same as DATEDIFF.

SELECT	DATEADD(dd, 1, '8/1/2013') AS '1 day forward',
	DATEADD(dd, -1, '3/1/2012') AS 'Leap year known',
	DATEADD(yyyy, 1, '8/12/2013 13:30') AS 'keeps time',
	DATEADD(mm, 1363, 0) AS 'since 1/1/1900'

Combine Functions

Frequently, we want to bucket dates. For example, to count orders per day:

SELECT DATEADD(DD, DATEDIFF(DD, 0, o.OrderDate), 0) AS 'Day', COUNT(*) AS 'Count'
	FROM Sales.SalesOrderHeader o
	WHERE o.OrderDate > '7/1/2008'
	GROUP BY DATEADD(DD, DATEDIFF(DD, 0, o.OrderDate), 0)
	ORDER BY Day

Notice, you still have to GROUP BY any column not involved in an aggregate function, regardless of its complexity.

UNION ALL

To produce a single result set that is a combination of two queries, use UNION ALL. All of the columns of the two queries must have exactly the same data types. Here is a single list of all the address types and phone number types we have in our database.

SELECT a.AddressTypeID AS ID, a.Name, 'Address' AS Type
	FROM Person.AddressType a
UNION ALL
SELECT p.PhoneNumberTypeID, p.Name, 'Phone'
	FROM Person.PhoneNumberType p
ORDER BY Name

Subqueries

A subquery, a query within a query, can be used almost anywhere to access data in a more useful way. Useful can be a different structure for joining or it could be for performance purposes.
Let’s take a look at this year’s high value orders product 863. A high value order containing product 863 is >=$40,000. So, we start with our a query using what we already know how to do:

SELECT o.SalesOrderID, o.TotalDue, p.FirstName + ' ' + p.LastName AS CustomerName
	FROM Sales.SalesOrderHeader o
	JOIN Sales.Customer c on c.CustomerID = o.CustomerID
	JOIN Person.Person p ON p.BusinessEntityID = c.PersonID
	JOIN Sales.SalesOrderDetail od ON od.SalesOrderID = o.SalesOrderID
	WHERE o.TotalDue > 40000
		AND od.ProductID = 863
	ORDER BY o.OrderDate

The problem is filtering to product 863 requires the SalesOrderDetail table. If we join to SalesOrderDetail, our rows may expand for any order with multiple line items of that product. We don’t need to display any data from OrderItems, but the data we need to filter on is there.

WHERE EXISTS (subquery)

So, we will add a special WHERE clause that just looks for the existence of data in a query:

SELECT o.SalesOrderID, o.TotalDue, p.FirstName + ' ' + p.LastName AS CustomerName
	FROM Sales.SalesOrderHeader o
	JOIN Sales.Customer c on c.CustomerID = o.CustomerID
	JOIN Person.Person p ON p.BusinessEntityID = c.PersonID
	WHERE o.TotalDue > 40000
		AND EXISTS (SELECT *
				FROM Sales.SalesOrderDetail od
				WHERE od.SalesOrderID = o.SalesOrderID
					AND od.ProductID = 863)
	ORDER BY o.OrderDate

There are two keys takeaways with this query:
1) We didn’t have to do a join to OrderItems and get needless row duplication.
2) The EXISTS check is very fast since it doesn’t really return data, just the existence of rows. Once it finds a single row, it moves on and does not have to find all of the rows.
Now, we can expand our query to show two products: 863 and 854. However, high value orders for product 854 are >= $80,000. We can UNION two queries:

SELECT o.SalesOrderID, o.TotalDue, p.FirstName + ' ' + p.LastName AS CustomerName, 863 AS ProductID
	FROM Sales.SalesOrderHeader o
	JOIN Sales.Customer c on c.CustomerID = o.CustomerID
	JOIN Person.Person p ON p.BusinessEntityID = c.PersonID
	WHERE o.TotalDue > 40000
		AND EXISTS (SELECT *
				FROM Sales.SalesOrderDetail od
				WHERE od.SalesOrderID = o.SalesOrderID
					AND od.ProductID = 863)
UNION ALL
SELECT o.SalesOrderID, o.TotalDue, p.FirstName + ' ' + p.LastName AS CustomerName, 854
	FROM Sales.SalesOrderHeader o
	JOIN Sales.Customer c on c.CustomerID = o.CustomerID
	JOIN Person.Person p ON p.BusinessEntityID = c.PersonID
	WHERE o.TotalDue > 80000
		AND EXISTS (SELECT *
				FROM Sales.SalesOrderDetail od
				WHERE od.SalesOrderID = o.SalesOrderID
					AND od.ProductID = 854)
ORDER BY SalesOrderID

JOINing to a Subquery

With everything we have seen in this session, we can now create a psuedo table and join to it. Our request is for a two-week rolling average for the past four weeks. Since our sample database is all historical data, we will use August 1, 2008, rather than GETDATE().
We will start by creating our time ranges. Here is the current two-week date range:

SELECT	DATEADD(dd, -1, DATEADD(ww, DATEDIFF(ww, 0, '8/1/2008'), 0)) as 'StartDate',
	DATEADD(dd, 13, DATEADD(ww, DATEDIFF(ww, 0, '8/1/2008'), 0)) as 'EndDate'

Add in the previous 4 weeks:

SELECT	DATEADD(dd, -1, DATEADD(ww, DATEDIFF(ww, 0, '8/1/2008'), 0)) as 'StartDate',
	DATEADD(dd, 13, DATEADD(ww, DATEDIFF(ww, 0, '8/1/2008'), 0)) as 'EndDate'
UNION ALL
SELECT	DATEADD(dd, -8, DATEADD(ww, DATEDIFF(ww, 0, '8/1/2008'), 0)),
	DATEADD(dd, 6, DATEADD(ww, DATEDIFF(ww, 0, '8/1/2008'), 0))
UNION ALL
SELECT	DATEADD(dd, -15, DATEADD(ww, DATEDIFF(ww, 0, '8/1/2008'), 0)),
	DATEADD(dd, -1, DATEADD(ww, DATEDIFF(ww, 0, '8/1/2008'), 0))
UNION ALL
SELECT	DATEADD(dd, -22, DATEADD(ww, DATEDIFF(ww, 0, '8/1/2008'), 0)),
	DATEADD(dd, -8, DATEADD(ww, DATEDIFF(ww, 0, '8/1/2008'), 0))

Our base query for AOV and order count:

SELECT	AVG(o.TotalDue) AS AOV, COUNT(*) AS Count
	FROM Sales.SalesOrderHeader o

We definitely don’t want to run this query since it looks at all orders. However, we can join to our query of date ranges to remove unwanted data:

SELECT w.startdate, AVG(o.TotalDue) AS AOV, COUNT(*) AS Count
	FROM Sales.SalesOrderHeader o
	JOIN (
		SELECT	DATEADD(dd, -1, DATEADD(ww, DATEDIFF(ww, 0, '8/1/2008'), 0)) as 'StartDate',
			DATEADD(dd, 13, DATEADD(ww, DATEDIFF(ww, 0, '8/1/2008'), 0)) as 'EndDate'
		UNION ALL
		SELECT	DATEADD(dd, -8, DATEADD(ww, DATEDIFF(ww, 0, '8/1/2008'), 0)),
			DATEADD(dd, 6, DATEADD(ww, DATEDIFF(ww, 0, '8/1/2008'), 0))
		UNION ALL
		SELECT	DATEADD(dd, -15, DATEADD(ww, DATEDIFF(ww, 0, '8/1/2008'), 0)),
			DATEADD(dd, -1, DATEADD(ww, DATEDIFF(ww, 0, '8/1/2008'), 0))
		UNION ALL
		SELECT	DATEADD(dd, -22, DATEADD(ww, DATEDIFF(ww, 0, '8/1/2008'), 0)),
			DATEADD(dd, -8, DATEADD(ww, DATEDIFF(ww, 0, '8/1/2008'), 0))
) W ON W.StartDate <= o.OrderDate AND w.EndDate > o.OrderDate
GROUP BY w.startdate

Notice that a row in Orders will join to multiple rows in the subquery if it belongs to multiple date ranges. This allows the order to be counted in two different two-week rolling averages.

Homework

1) List the two-week rolling total quantity by territory for the current period and 3 prior periods by week. The result set should have three columns: Period Start Date, Territory Name, and Total Quantity.
2) List the total sales for each day of the week by territory for July 2008. The columns in the result set should be: Territory, Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday. Do not display the territory if it had zero sales all week.

Next Session will be just the homework answers as I have exhausted what I consider necessary for the goal of this series.