/*******************************************************************************************
How Many Licenses Am I Using
      
Rev 01 - Tony Trus 06/03/13   
Onepax Business Consulting
http://onepax.com

Limitations: The maximum licenses will need to be gathered from the License Administration Form because
it is encrypted.   Once those licenses are gatherered, you can edit this script and optionally add logic
as desired
*******************************************************************************************/

SET NOCOUNT ON
IF EXISTS (
		SELECT NAME
		FROM tempdb..sysobjects
		WHERE NAME = '##tmp'
		)
	DROP TABLE ##tmp

CREATE TABLE ##tmp (
	ModuleName VARCHAR(50)
	,[Licenses In Use] INT
	)
GO

DECLARE @modulenam VARCHAR(50)

DECLARE modulenam CURSOR
FOR
SELECT DISTINCT ModuleName
FROM modules
ORDER BY ModuleName

OPEN modulenam

FETCH NEXT
FROM modulenam
INTO @modulenam

WHILE @@FETCH_STATUS = 0
BEGIN
	DECLARE @sql NVARCHAR(500)
	SET @sql = 'declare @usercount INT; declare @modulenam varchar(50)=''' + @modulenam + '''; exec GetLicenseModuleUserCountSp ''' + @modulenam + ''',@usercount OUTPUT; insert into ##tmp select @modulenam, @usercount'
	PRINT @sql
	EXEC sp_executesql @sql
	FETCH NEXT
	FROM modulenam
	INTO @modulenam
END

CLOSE modulenam
DEALLOCATE modulenam
GO

--order by rows desc
SELECT *
FROM ##tmp

GO

IF EXISTS (
		SELECT NAME
		FROM tempdb..sysobjects
		WHERE NAME = '##tmp'
		)
	DROP TABLE ##tmp