I have way too many companies

Controller:

Can you give me a list of all companies in GP?

Me:

Sure, do you need anything else while you I am at it?

Controller:

I also need to be able to tell if there is any activity or not in any given company.

Me:

Okay, let me see what I can do. 

I built the below script to generate the SQL I needed. 

Begin SQL script

use Dynamics
SELECT 'SELECT '+''''+RTRIM(CMPANYID)+'-'+RTRIM(INTERID) +''''+' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM '+rtrim(INTERID)+'.DBO.SY40101 union' sql1
FROM [dbo].SY01500
where cmpanyid <> - 1
UNION

SELECT 'SELECT '+''''+RTRIM(CMPANYID)+'-'+RTRIM(INTERID) +''''+' COMPANY, MIN(year1) ,MAX(year1) FROM '+rtrim(INTERID)+'.DBO.SY40101 ' sql1
FROM [dbo].SY01500
where cmpanyid = - 1

order by SQL1 desc

End SQL Script

My SQL looked like this

SELECT '80-LFFMT' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM LFFMT.DBO.SY40101 union
SELECT '79-LFMRE' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM LFMRE.DBO.SY40101 union
SELECT '78-PURCH' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM PURCH.DBO.SY40101 union
SELECT '77-OCR' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM OCR.DBO.SY40101 union
SELECT '76-LFMTG' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM LFMTG.DBO.SY40101 union
SELECT '75-DUMMY' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM DUMMY.DBO.SY40101 union
SELECT '74-LFFMR' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM LFFMR.DBO.SY40101 union
SELECT '73-WJMC' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM WJMC.DBO.SY40101 union
SELECT '72-LHDQ2' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM LHDQ2.DBO.SY40101 union
SELECT '71-LFIRE' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM LFIRE.DBO.SY40101 union
SELECT '70-LFMVE' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM LFMVE.DBO.SY40101 union
SELECT '7-PRS' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM PRS.DBO.SY40101 union
SELECT '69-LFHSC' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM LFHSC.DBO.SY40101 union
SELECT '68-LFFIN' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM LFFIN.DBO.SY40101 union
SELECT '67-LVENT' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM LVENT.DBO.SY40101 union
SELECT '66-HQ2' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM HQ2.DBO.SY40101 union
SELECT '65-VISLD' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM VISLD.DBO.SY40101 union
SELECT '63-GRYVA' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM GRYVA.DBO.SY40101 union
SELECT '62-SAGEG' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM SAGEG.DBO.SY40101 union
SELECT '61-STPRO' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM STPRO.DBO.SY40101 union
SELECT '58-SSDSJ' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM SSDSJ.DBO.SY40101 union
SELECT '57-KEYSP' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM KEYSP.DBO.SY40101 union
SELECT '5-MET' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM MET.DBO.SY40101 union
SELECT '45-goaks' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM goaks.DBO.SY40101 union
SELECT '43-RGSMD' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM RGSMD.DBO.SY40101 union
SELECT '42-RGS' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM RGS.DBO.SY40101 union
SELECT '40-FONV' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM FONV.DBO.SY40101 union
SELECT '4-MST' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM MST.DBO.SY40101 union
SELECT '3-LFIA' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM LFIA.DBO.SY40101 union
SELECT '27-PMPR' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM PMPR.DBO.SY40101 union
SELECT '20-LIC' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM LIC.DBO.SY40101 union
SELECT '19-LFC' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM LFC.DBO.SY40101 union
SELECT '17-HDQ' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM HDQ.DBO.SY40101 union
SELECT '16-LHI' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM LHI.DBO.SY40101 union
SELECT '15-ETL' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM ETL.DBO.SY40101 union
SELECT '11-LLC' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM LLC.DBO.SY40101 union
SELECT '1-LFI' COMPANY, MIN(year1) OldestFY,MAX(year1) RecentFY FROM LFI.DBO.SY40101 union
SELECT '-1-TWO' COMPANY, MIN(year1) ,MAX(year1) FROM TWO.DBO.SY40101

My Report data when pasted in excel looked like this

The excel is what I gave my controller and she was very happy. 

If you have another consolidated report idea in mind, please do leave a comment and I will be happy to compile a script for the same.

I thank you for reading my blog.