SQL View to Check Dynamics GP Utilities Upgrade Progress

It’s a while since I did an upgrade of Microsoft Dynamics GP, as they are usually assigned to other consultants, but I’ve taken on a couple recently. One of them has 20+ companies and another coming up soon has well above 100+, so I decided I needed a way of seeing how far the upgrade had progressed.

The below SQL script creates a view on the upgrade tables showing when the upgrade of a company started and when it ended; the highlighted section should be changed to the include any of the products which should not be checked (I included the Dynamics Online Services product as it has been retired).

-- drop view if it exists
IF OBJECT_ID(N'uv_AZRCRVCheckUpgradeProgress', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRVCheckUpgradeProgress
GO
-- create view
CREATE VIEW uv_AZRCRVCheckUpgradeProgress AS
WITH ['Upgrade Stop'] AS
(
	SELECT
		db_name
		,stop_time
		,ROW_NUMBER() OVER (PARTITION BY DB_NAME ORDER BY PRODID DESC) AS RowNumber
	FROM
		db_upgrade WITH (NOLOCK)
	WHERE
		PRODID <> 6499 -- Dynamics Online Services
)

SELECT
	['Upgrade Start'].db_name
	,['Upgrade Start'].db_verMajor
	,['Upgrade Start'].db_verMinor
	,['Upgrade Start'].start_time
	,['Upgrade Stop'].stop_time
FROM
	DB_Upgrade ['Upgrade Start'] WITH (NOLOCK)
LEFT JOIN
	['Upgrade Stop'] WITH (NOLOCK)
		ON
			['Upgrade Stop'].db_name = ['Upgrade Start'].db_name
		and
			RowNumber = 1
WHERE
	['Upgrade Start'].PRODID = 0
GO

GRANT SELECT ON uv_AZRCRVCheckUpgradeProgress TO DYNGRP
GO

To select the data from the view in the correct order, this query can be used:

SELECT
	*
FROM
	uv_AZRCRVCheckUpgradeProgress 
ORDER BY
	db_verMajor DESC
	,db_verMinor DESC
	,start_time