SELECT		SUM(CASE WHEN OrderDate >= dateadd(day, - 30, getdate()) THEN 1 ELSE 0 END) AS OrderCount,
		exigo_data_sync.Customer.CustomerStatus,
		exigo_data_sync.Customer.State,
		dbo.Rank.RankCode
FROM		exigo_data_sync.Orders
INTO		#Temp
INNER JOIN	exigo_data_sync.Customer ON exigo_data_sync.Orders.CustomerID = exigo_data_sync.Customer.CustomerID
INNER JOIN	dbo.Rank ON exigo_data_sync.Customer.RankID = dbo.Rank.RankID
WHERE		exigo_data_sync.Customer.CustomerType = 'Distributor'
GROUP BY	exigo_data_sync.Customer.CustomerStatus,
		exigo_data_sync.Customer.State, 
		dbo.Rank.RankCode

SELECT		OrderCount,
		CustomerStatus,
		State,
		RankCode
FROM		(
			SELECT		OrderCount,
					CustomerStatus,
					State,
					RankCode
			FROM		#Temp

			UNION ALL

			SELECT		SUM(OrderCount),
					'Active',
					State,
					RankCode
			FROM		#Temp
			WHERE		CustomerStatus = 'Active'
			GROUP BY	State,
					RankCode
		)
ORDER BY	State

DROP TABLE	#Temp